We restore a database daily that we download from an FTP site. I would like to build a history table that tracks when the database is restored on our SQL Server. This is easy. My issue is sometime the download is incomplete and therefore the restore does not work and throws an error. I would like to add logic to my history process to insert failed if the restore fails. Not sure how to handle this logic and am looking for assistance.

This is the error we get most frequently when the failure occurs.

Error: 3266, Severity: 16, State: 1.

The backup data at the end of "F:\Backups\DB.bak" is incorrectly formatted. Backup
sets on the media might be damaged and unusable. To determine the backup sets on the 
media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run 
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using 
BACKUP WITH FORMAT, which destroys all the backup sets.

What about using the tables already in the MSDB database, Restorehistory, BackupSet and BackupMediaFamily?

These databases will already have all of the backup and restore history. I would check though that in your particular case what the behaviour looks like in the Restorehistory table. You should be able to check in these tables that after your restore process whether it was successful or not and provoke the appropriate action you want.



If try/catch doesn't work for you, then you could add another step to the restore portion to test and see if the database restore worked or not.

Or you could run a VERIFYONLY restore first and test it that way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.