In Iron Man-3 Tony asks JARVIS to initiate the "House Party Protocol". As you know JARVIS had a hard time tracking back which suit should be provisioned to meet the demands of a situation. Even though a computer has a lot of intelligence it might need to be re-programmed for an unforeseen scenario. We DBAs are also in a fix sometimes and are not able to look what is beyond the obvious and give an answer to a situation. So we can’t stop learning and have to keep pushing ourselves into the internals of the SQL engine for the answers.
Today I would like to discuss one scenario where I had no documented answers. The answer was only found because of my hunger to know more. The question was “How do I know which backup was used to restore the database given there are two backups and they are from two different servers?” Well to understand the answer to this question I would like to break this article it in two phases.
Phase 1: How can we identify the backup file which was used to restore a database if the backup was from the same server?
Answer: Here we will need to exploit the history tables in MSDB. We will need to join the “msdb.dbo.restorehistory” and “msdb.dbo.backupmediafamily” tables. This will give us the answer to our query.
In the above image it is clearly shown that the test database was restored last on 29 Jan, 2014 16:37:25 hours. Also the backup file related to this restore is “C:\Development\backups\ test29012014.bak”. The query is attached to the article as get_restore_history.sql
Now we know the backup that was used to restore the database. The scenario takes a complete different turn when the historytables have been cleared because of maintenance jobs or the backup files are not from the same server. This brings us to the Phase 2 of my article
Phase2. How can we identify the backup file that was used to restore a database if the backup was from the different server or the history tables have been cleared?
Answer: The history tables only help if the backupset data is from the same server where you want to restore. The reason being is the backupset information that we are joining with the restore history here is only relevant when the backup of the database is taken on the same server. Otherwise the backupset will not have the information about the backupset.
To approach the solution we will use an undocumented command: DBCC DBINFO (“DBNAME”). This will give us a lot of information related to the database including the dbi_dbbackupLSN and the dbi_checkptLSN. LSN is the Log Sequence number which helps SQL Server to maintain the chain of the restoration process. You can see this command run below.
In the image above we see how the command DBCC DBINFO () lists the dbi_dbbackupLSN and the dbi_checkptLSN for the database test. The dbi_dbbackupLSN is the last LSN of the backup file and dbi_checkptLSN is the checkpoint LSN for the backup file that was used to restore the database. For more information on LSN you can visit MSDN/TechNet. Now that we have the information of the LSN of the Backupset from which we require all we need to do is to identify the LSN’s of the backup set that we have. We can get the LSN information of the backup file using the restore headeronly command.
The backup header will have a lot of information related to the backup file. It will have the server name, database version, first LSN, last LSN, checkpoint LSN and lot of information which are useful information in a lot of scenarios. Here we will try to match the LastLSN with the dbiBackupLSN and the CheckPointLSN to dbi_checkptLSN to identify the backup which was used to restore the database.
As mentioned earlier we will try to match the LastLSN of the Backup file to the dbiBackupLSN and the CheckPointLSN to dbi_checkptLSN of the database. This helps us to identify the backup file that was used to restore the database in the specific case the “test29012014.bak” backup file was used to restore the database test.
