MsSQL Server: Make sure a SQL Server backup is usable

It is always recommended before restoring a database, please check whether database is corrupt or not as it will save your time for sure.

In Microsoft MsSQL server, this is the gain provided from RESTORE VERIFYONLY:

The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable. The does not do an actual restore, but reads through the file to ensure that SQL Server can read it while you are actually restoring the database. 

1) Case 1: Backup Is Useable

restore verifyonly from Disk='D:\SQL_DBBackup\TEST.BAK'

Output: “The backup set is valid.”

2) Case 2 : Backup is Corrupt

restore verifyonly from Disk='D:\SQL_DBBackup\TEST.BAK'

Output :

A) Msg 3242, Level 16, State 2, Line 1
The file on device ‘D:\SQL_DBBackup\TEST.BAK’ is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally. 

B) Msg 3043, Level 16, State 1, Line 1
BACKUP ‘database_name’ detected an error on page (file_id:page_number) in file file on device ‘D:\SQL_DBBackup\TEST.BAK’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Be the first to comment

Leave a Reply

Your email address will not be published.