Hello Geeks,

The time is flying by as I rejoice in the aftermath of the SQLServerGeeks Annual Summit 2015. It has been a wonderful event and once in a life time opportunity to be part of the Asia’s first SQL conference. More eventful was my personal life with the arrival of my princess Mahira on 8th of October into our lives to make it even more memorable.

It is really hard to take out time now to write blogs and get back on track with my passion. This is the first attempt to get back on track. To start with, I have been challenged to restore database from backups many a times. This is a daunting task if there is a long backup chain and there is always someone taking few adhoc backups in between to break your backup chain.

Today we will see a script I have written to save my some time. This will help you check if your backup chain is as expected. There are always few people/accounts which have access more than what is needed. There are few scenarios where the adhoc backup are taken by these accounts. This script can help you in identifying those cases. This will also help you in identifying the right sequence of restore.


The result set will give you the aggregate size of all backups, individual type of backups and each database restore sequence. In the below result set, you can observe that I have taken a full backup off schedule which has reset your restore sequence. So instead of the full backup taken at 2015-10-19 14:34, we have to start with the full backup taken manually at 2015-10-19 14:44.

restore sequence

Note 1: This script only works if there is enough history to the oldest backup in the backup chain to be restored.

Note 2: There are additional columns which can be uncommented in the last select statement for additional information.

Hope this script helps you and any feedback is greatly appreciated.

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook