I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.
Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.
USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO
Very neat script and in my above example I have ran that for single database adventureworks and you can see following results. The same can be ran for multiple database as well if you just remove the WHERE condition.
Reference: Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
What does the SELECT TOP 100 mean?
Thanks
Helo, I want to make sql server 2008 Replication with Mirroring. so I have 4 servers i want two of them to be mirrored . and the other two i want the Principle to be replicated to them. So that after fail over i want the mirrored to take the replication. Please help me i’m new to sql server .
Nice Work !!!
Good one.
But what if we need to get the backup history with backupfile size in Kbs?
select A.database_name, A.backup_start_date, A.backup_finish_date,
datediff(ss, A.backup_start_date, A.backup_finish_date) as ‘Duration’,
(case A.[type] when ‘D’ then ‘Full’ when ‘I’ then ‘Differential’ when ‘L’ then ‘Log’
when ‘F’ then ‘File or Filegroup’ when ‘G’ then ‘File Differential’
when ‘P’ then ‘Partial’ when ‘Q’ then ‘Partial Differential’ else A.[type] end) as ‘Type’,
A.backup_size, B.physical_device_name
from msdb.dbo.backupset A
left join msdb.dbo.backupmediafamily B on B.media_set_id = A.media_set_id
order by A.backup_finish_date desc
This will give in KB
How can I get the result of the above script suggested by aasim abdullah on mail
I have configured DB mail and just get successful backup mail of DB.
How to get both success and fail status for daily backup jobs to generate daily report, as i know failed database backup has no entry in msdb.dbo.backupset ?
this is great! thanks! is there a way to see who backed it up?
hey i want to create one history table for hole database .
without creating any link with any table.means whenever i will fire update,delete,insert query that history table automatically insert.
How do we get the “compressed” database backup file size, not the regular backup file size?
@Leon, the backup_size column will give you the compressed size. You need to join to backupmediaset to determine if the backup is compressed.
Sorry, made a mistake. Look at [msdb].[dbo].[backupset].[compressed_backup_size]
if the use takes a file/filegroup backup ,copy of backup , stripped back is it works or not
sorry invalid Question ?
Select TOP 100 will not give u the complete backup history. The script is great, but remove top 100 from the query.
Also, if you have backed up your database more than 100 times in the same day, you will not have history after the 100th, no need for top 100.
may i a hav e your help writing down a SP that will generate a report regarding backup success/failure?
Jhonatan – What help you are looking for?
it would be grateful if you could provide script for backup history for last one year , by executing the above script i’m getting for last two weeks.
Thnx in advance
Very Useful Script. Thanks !!!
Hi Pinal,
I have noticed in my one database above scripts shows the only 3 latest entries of backup history. Any idea why it is showing only tree entries but actually job is taking the backup from from last two weeks.
Hi All,
I want the script which i can find latest full and tlog backup details because i have so many server and provide me the steps also to get data in excel sheet table required in queries ‘server name ,hostname,bacuptype,
databasename,backuppath,backuptime,
thanks in advance
Good one!! This is really helpful..!!
can any of you all help me in getting the latest successful backup in a db.
Thanks in advance
Hey, i need the script for backup details of all sql servers in the estate, with out using CMS. Can anyone help me out.
Hi Abdullah , Thank you for the script. Can we found who took the backup ???
@Sagar: backupset table has column called user_name. You can add that in the select clause.
Dave! your size is inaccurate – it should be s.backup_size / 1048576 because there are 1024 bytes to 1 KB and 1024 KB in 1 MB. – consider this… CONVERT (NUMERIC (20,2), s.backup_size / 1048576 ) ‘Backup Size (MB)’ to eliminate your rounding error.