SQL SERVER – Get Database Backup History for a Single Database

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
-- Get Backup History for required database
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,
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,
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

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.

backuphistory SQL SERVER   Get Database Backup History for a Single Database

Reference: Pinal Dave (http://blog.SQLAuthority.com)

41 thoughts on “SQL SERVER – Get Database Backup History for a Single Database

  1. Pingback: SQL SERVER – Change Database Access to Single User Mode Using SSMS Journey to SQL Authority with Pinal Dave

  2. 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 .


    • 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


  3. 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.


  4. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  6. 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 ?


  7. 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.


  8. 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.


  9. 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,

    thanks in advance


  10. Pingback: SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to the correct earlier state | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – How to Know Backup History of Current Database? | Journey to SQL Authority with Pinal Dave

  12. 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.


      • He wants you to create the stored proc wrapper for him rather then attempting to create it himself. One pattern I’m noticing in the comments is most people want you to do their work for them with mods to the script.

        Give a man a fish, he eats for a day. Teach a man how to fish, he eats for a lifetime.

        These people need to attempt to understand the script and modify it themselves.


  13. Hi Pinal,

    I need to get the time taken for all the servers in sql using CMS. Can you please help me with a script? I would really appreciate it.

    Thank You,


  14. Pingback: SQL SERVER – Identify Time Between Backups Calculation | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s