The last ten days my team has been working on an issue where the Log Shipping broke due to DST change. During DST change some transaction log backup files got deleted and we had to re-sync the Secondary databases. So we took the FULL backup of the Primary databases and started copying them. Since the databases are huge in size and the connectivity is slow, by the time the FULL backup gets copied to the Secondary instance there would be several new transaction log backup generated on the Primary instance. Last weekend, we were left with only a couple of databases to be restored.
Like I said earlier, the connectivity between the two servers is slow and we were dependent on some other team to get the files copied to the Secondary server. Once they copy the files DBA would jump into action to restore them. During the copy process, some files would not get copied correctly and the devil will jump in when the DBA is trying to restore the database. In order to avoid the surprises during restore, we decided to verify the backup sets before restoring them.
The FULL backups were mostly striped across multiple drives. DBA chose to run RESTORE HEADERONLY on these backup files. The only reason for this choice was that HEADERONLY completes quickly when compared to other options of verifying the backup file validity. This is not a good choice to verify striped backups. Because in striped backups the important header information is only available in the First backup file. If the first file is valid then RESTORE HEADERONLY reports the Header information of the Backup set correctly. In the below example I take the backup of master database striped across multiple files.
BACKUP DATABASE master TO DISK = 'C:DBBACKUPSmaster1.bak', DISK = 'C:DBBACKUPSmaster2.bak', DISK = 'C:DBBACKUPSmaster3.bak', DISK = 'C:DBBACKUPSmaster4.bak', DISK = 'C:DBBACKUPSmaster5.bak'
The files get backed up to C:DBBACKUPS folder.
I will replace master2.bak with an empty file with the same name. Then run RESTORE HEADERONLY on the backup set.
RESTORE HEADERONLY FROM DISK = 'C:DBBACKUPSmaster1.bak', DISK = 'C:DBBACKUPSmaster2.bak', DISK = 'C:DBBACKUPSmaster3.bak', DISK = 'C:DBBACKUPSmaster4.bak', DISK = 'C:DBBACKUPSmaster5.bak'
Since the first file i.e. master1.bak file is intact, as expected the query would claim that the backup set is valid which is not the case.
If the first file was corrupt, then the BackupName in the above output would be marked Incomplete. The best approach to verify the validity of the backup files is to run RESTORE VERIFYONLY on the backup files. This verifies that the entire backup set is complete and the contents of the backup files are readable. However this does not guarantee that the data structure within the backup files are valid. The other downside to this command is that it may take the same or more time to complete than it to backup the database! All these options are useful to find if the backup media is valid but the data structure issues will come into picture while doing the actual Restore.
Does RESTORE VERIFYONLY command help in this example. It certainly does.
RESTORE VERIFYONLY FROM DISK = 'C:DBBACKUPSmaster1.bak', DISK = 'C:DBBACKUPSmaster2.bak', DISK = 'C:DBBACKUPSmaster3.bak', DISK = 'C:DBBACKUPSmaster4.bak', DISK = 'C:DBBACKUPSmaster5.bak'
The output correctly states that the master2.bak file is empty.
Msg 3254, Level 16, State 1, Line 1 The volume on device 'C:DBBACKUPSmaster2.bak' is empty. Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally.
Suppose there are multiple files which did not get copied properly. Say master2.bak and master4.bak files are corrupt. VERIFYONLY will terminate stating that the master2.bak file is corrupt. By default RESTORE VERIFYONLY uses the CONTINUE_AFTER_ERROR arguement where as RESTORE command defaults to STOP_ON_ERROR. The CONTINUE_AFTER_ERROR argument forces SQL Server to restore whatever it can. The pages that fail verification in this process are written to errorlog and to suspect_pages table in MSDB which can hold only 1000 rows. My fellow SME who was working on this issue this weekend told me about this very useful feature which I was unaware of. More information on how SQL Server responds to restore errors can be found in this article. But a corrupt backup file is something which renders even the CONTINUE_AFTER_ERROR argument invalid.
In our case, we started using RESTORE VERIFYONLY on the striped backup files and whichever files were reported to be corrupt were copied again. This process was repeated until all the VERIFYONLY command completed successfully. Hopefully by now all the databases on that instance are synched. I am yet to check my mails.
With this I conclude that it is worth the time to run VERIFYONLY on the backups at least once in a while to avoid surprises at the time disaster.