SQL Server-BackUp History

Many times we need backup history, when which type of backup is taken?

Is back up was compressed? So I think let me make a Script which gives list of backup history.

Let me share One interesting fact which I observed when I was working onĀ  this Script.

Let me describe scenario.


  • We have two server 1.Test server and 2.Local server
  • I need to restore database on Local serer from Test server.
  • But Database does not exist on Local server.
  • So I will create new database.Then Restore Full back up which is taken from Test server.
  • now i will run following script for newly created and restored database.
  • Note it will show one record for which database is restored.
  • Reason is very simple cause it is backup right now for newly created Database as well as it is for Test server.as we have restored database to Local server from backup.so it will show one record for backup history on Local server without taking backup.
SELECT  ms.software_name [software] ,
		s.user_name ,
        s.database_name ,
        CASE ms.is_compressed
          WHEN 1 THEN 'Compressed'
          ELSE 'DeCompressed'
        END AS [BackupMethod] ,
         s.recovery_model ,
        CASE s.type
          WHEN 'D' THEN 'FULL'
          WHEN 'L' THEN 'Transaction Log'
          WHEN 'I' THEN 'Differential'
        END AS BackupType ,
        m.physical_device_name ,
        CASE ms.is_compressed
          WHEN 1
          THEN CAST(s.compressed_backup_size / 1048576 AS NUMERIC(18, 2))
          ELSE CAST(s.backup_size / 1048576 AS NUMERIC(18, 2))
        END AS [Size in MB] ,

        s.backup_start_date ,
        CAST(DATEDIFF(s, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) TimeTaken ,
        s.first_lsn ,
        s.last_lsn ,
        s.server_name
FROM    msdb.dbo.backupset s
        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
        INNER JOIN msdb.dbo.backupmediaset ms ON ms.media_set_id = s.media_set_id
WHERE   s.database_name = DB_NAME()