SQL SERVER – How to Know Backup History of Current Database?

Some blog post and scripts are like ever green references. One such blog has been Get Database Backup History for a Single Database. A number of you have pinged me and for amendments to that script with various ideas. Since that post was written more than 4+ years now, I thought it would be worthwhile to change it to current trends of SQL Server 2014 additions. What are the changes made to the previous script? The script will answer few questions like:

  • What were the different backups taken on the current DB? Remove the condition mentioned below and it can be generic enough for all DBs on your server.
  • Which user was involved in taking the backup?
  • What is the type of backup we are talking about?
  • Recovery Model and Database compatibility level of the DB at the time the backup was taken. I remember using this for a user, who used to change the DB compatibility in their script before a backup. It was basically a copy-paste problem from an internet script. This data helped there.
  • Size of backup – Both compressed and uncompressed.
  • If the Backup was password protected.
  • Finally, when the backups were taken.

So let us look at the script next. Feel free to modify the same as you wish.

-- Recent backup history for the current DB
SELECT s.database_name 'Database',
s.recovery_model 'Recovery Model',
s.compatibility_level,
s.USER_NAME 'Backup by Username',
CASE s.TYPE
WHEN
'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END 'Backup Type',
CONVERT(VARCHAR(20), s.backup_finish_date, 13) 'Backup Completed',
CAST(mf.physical_device_name AS VARCHAR(100)) 'Physical device name',
DATEDIFF(minute, s.backup_start_date, s.backup_finish_date) 'Duration Min',
CAST(ROUND(s.backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Size in MB',
CAST(ROUND(s.compressed_backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Compressed Size in MB',
CASE WHEN LEFT(mf.physical_device_name, 1) = '{' THEN 'SQL VSS Writer'
WHEN LEFT(mf.physical_device_name, 3) LIKE '[A-Za-z]:\%' THEN 'SQL Backup'
WHEN LEFT(mf.physical_device_name, 2) LIKE '\\' THEN 'SQL Backup'
ELSE mf.physical_device_name
END 'Backup tool',
s.is_copy_only,
s.is_password_protected,
s.is_force_offline /* for WITH NORECOVERY option */
FROM   msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE  s.database_name = DB_NAME() AND  -- remove this condition if you want all DBs
s.backup_finish_date > DATEADD(MONTH, -3, GETDATE()) -- Get data for past 3 months
ORDER BY s.backup_finish_date DESC;

Solarwinds

A sample output would look like:

SQL SERVER - How to Know Backup History of Current Database? Backup_History

There can be more additional fields added to this script like: If encryption is enabled, Collation information, LSN information, if backup has checksum etc. Do let me know which additional information do you use in your environment to know your backups better.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 1 of 3)
Next Post
Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 2 of 3)

Related Posts

6 Comments. Leave new

  • Awesome script! Thanks Dave.

    Reply
  • Allen Cunningham
    June 28, 2015 5:19 am

    — This script will let you know when a db was refreshed, and what was the source
    — Unfortunately our msdb.dbo.restorehistory only shows 2 weeks of data.
    — It can be run in Master or any db,

    DECLARE @dbname sysname, @days int
    SET @dbname = ‘FNSB’ — db name
    SET @days = -30 — previous number of days, we only hold 14 days now
    SELECT
    rsh.destination_database_name AS [Database],
    rsh.user_name AS [Restored By],
    CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’
    WHEN rsh.restore_type = ‘F’ THEN ‘File’
    WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’
    WHEN rsh.restore_type = ‘I’ THEN ‘Differential’
    WHEN rsh.restore_type = ‘L’ THEN ‘Log’
    WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’
    WHEN rsh.restore_type = ‘R’ THEN ‘Revert’
    ELSE rsh.restore_type
    END AS [Restore Type],
    rsh.restore_date AS [Restore Started],
    bmf.physical_device_name AS [Restored From],
    rf.destination_phys_name AS [Restored To]
    FROM msdb.dbo.restorehistory rsh
    INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
    INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
    INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
    WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days
    AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all
    ORDER BY rsh.restore_history_id DESC
    GO

    Reply
  • neelamegam thangaraj
    June 29, 2017 1:26 pm

    Can i get the script to see the history for multiple database’s with instance name.

    Reply
  • My client gave me an old backup and asked me to which database it belongs and it’s complete details.
    How can we get that?

    Reply

Leave a Reply

Menu