Checking Databases after Backup / Restore

When Moving a Database between Servers – e.g. migrating to a new Production Server, how can you be absolutely sure that the Backup that you restored is the correct one?

The nature of the DBA role dictates a certain amount of OCD. Most people work in a methodical manner – using a specific Folder for backup, checking the Name, Date and Time of the Backup file before copying it over to another Server. Using Tools such as Ola Hallengren’s Maintenance Solution help by creating Filenames with the Database name, plus Date / Time of Backup. Files are generally copied to a specific Folder for restore. This is good, but is it sufficient?

The importance of restoring the correct backup is paramount, but so is being able to prove that you have restored the correct backup. This proof may be required for audit purposes, but it’s more important for your own sanity.

How about asking your Primary User to check the Data? Yes, they can do this by looking for something that they have changed just before the Database was backed up. This may give them confidence that their data has been moved successfully. You can be sure that any other Changes made on the source system before this would have been applied, but what about any system data changes that might have happened afterwards?

I think that we need to be a bit more sure. Backup files can hold more than one Backup. Stuff happens.

SQL Server holds a history of Backups that are run, together with Restores that it has performed. This information includes Filenames, allowing a simple check to be made that a Backup on one Server correlates to a Restore on another.

The following Query can be run on the Source Server to view Backups that have been taken.

-- Backup Details (run on Server / Database used for Backup)
SELECT bs.server_name
     , bs.database_name
     , bmf.physical_device_name AS 'backup_Filename'
     , CASE bs.[type]
         WHEN 'D' THEN 'Database'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'V' THEN 'Verifyonly'
         WHEN 'R' THEN 'Revert'
       END AS backup_type
     , CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn
     , CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn
     , bs.backup_start_date
  FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
 WHERE 0 = 0
   AND bs.database_name = DB_NAME() -- Current Database only  (comment out for all Databases)
 ORDER BY bs.backup_start_date DESC, bs.backup_finish_date;
GO

 

The following Query can be run on the Destination Server to view Backups that have been restored.

-- Restore Details (run on Server / Database used for Restore)
SELECT bs.server_name
     , rsh.destination_database_name AS 'database_name'
     , bmf.physical_device_name AS 'backup_Filename'
     , CASE rsh.restore_type
         WHEN 'D' THEN 'Database'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'V' THEN 'Verifyonly'
         WHEN 'R' THEN 'Revert'
         ELSE rsh.restore_type
       END AS [restore_type]
     , bs.first_lsn
     , bs.last_lsn
     , rsh.restore_date AS [restore_start_date]
  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 0 = 0
   AND bs.database_name = DB_NAME() -- Current Database only (comment out for all Databases)
   AND rf.file_number = 1 -- Only count the Data File Restore
   AND restore_date > '20180301'
 ORDER BY rsh.restore_history_id DESC;
GO

 

Armed with the results from these two queries, you can confidently proceed, knowing that you have Restored the correct Backup.