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
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 (http://blog.SQLAuthority.com)

About these ads

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

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

  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,
    databasename,backuppath,backuptime,

    thanks in advance

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