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 (https://blog.sqlauthority.com)

SQL Backup and Restore, SQL Scripts
Previous Post
SQL SERVER – Recycle Error Log – Create New Log file without Server Restart
Next Post
SQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010

Related Posts

50 Comments. Leave new

  • What does the SELECT TOP 100 mean?

    Thanks

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

    Reply
  • Bhuvanesh Sekar
    July 11, 2011 6:49 pm

    Nice Work !!!

    Reply
  • Praveesh Chandrapal
    July 11, 2012 5:56 am

    Good one.

    But what if we need to get the backup history with backupfile size in Kbs?

    Reply
    • Aditya - Senior SQL DBA
      September 20, 2012 1:17 pm

      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

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

    Reply
  • 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 ?

    Reply
  • this is great! thanks! is there a way to see who backed it up?

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

    Reply
  • How do we get the “compressed” database backup file size, not the regular backup file size?

    Reply
  • Marc Jellinek
    June 16, 2013 10:23 pm

    @Leon, the backup_size column will give you the compressed size. You need to join to backupmediaset to determine if the backup is compressed.

    Reply
  • saran kumar reddy
    August 27, 2013 1:48 pm

    if the use takes a file/filegroup backup ,copy of backup , stripped back is it works or not

    Reply
  • Select TOP 100 will not give u the complete backup history. The script is great, but remove top 100 from the query.

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

      Reply
      • Jhonatan Ben Ami
        March 14, 2015 2:20 pm

        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

  • Varinder Sandhu
    February 13, 2014 10:53 am

    Very Useful Script. Thanks !!!

    Reply
  • Varinder Sandhu
    February 13, 2014 11:05 am

    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.

    Reply
  • ameeruddin
    May 5, 2014 2:00 pm

    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

    Reply
  • Gobinda Sharma
    July 25, 2014 12:34 pm

    Good one!! This is really helpful..!!

    Reply
  • can any of you all help me in getting the latest successful backup in a db.

    Thanks in advance

    Reply
  • Hey, i need the script for backup details of all sql servers in the estate, with out using CMS. Can anyone help me out.

    Reply
  • Hi Abdullah , Thank you for the script. Can we found who took the backup ???

    Reply
  • b.backup_size / 1048576
    March 10, 2015 12:38 pm

    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.

    Reply

Leave a Reply

Menu
Exit mobile version