SQL Server 2012: Finding Sequence of backup taken October 22nd, 2012

Vinod Kumar

Recently I had a customer come over for a session on designing their HA/DR strategy and one of the steps for that was to understand the backup strategy too. I took sometime to talk at length what FULL Backup, Differential Backup and Transaction Log Backups. After understanding the core concepts one of the developer asked me, “Is there a way I can know in what sequence the current backups are taken? This will help me re-visit the current process done by my networking team”. Interestingly, a lot of people fail to understand how Page level restore needs to be done.

This was an interesting question and needed a little digging into the MSDB database which has most of the information. The below query will give you a list of backups and feel free to check the same in your environments. This also works in any of the SQL Server versions.

SELECT s.database_name,
       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
ORDER  BY database_name,

A typical output looks like below:


All these information are great stuff and the best part starts when we use the same for RESTORE. The UI for example will show interesting information which I was pleasantly surprised.


There are two things that stand out in the above dialog -

  1. The header shows we need to take the tail of the log backup for the restore. This is an awesome step which a lot of DBA forget in their restore process.
  2. The Backup Step shows what you will be restoring and in what sequence. This is also a pre-requisite for any restore stage.

On initializing the Filegroup restore dialog I can see ALL the Backups taken for the restore step and this is also handy if you are planning to restore any corrupt files.


Hope you find these improvements useful and the scripts handy. In future posts, let me take a shot in showing what all can be done with these backups.

Share this article

Tags: , , , , , , ,

This entry was posted on Monday, October 22nd, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply