Is your SQL server database backup OK? Check it now

A data can be any thing like employee attendance record, student marks detail, passenger list, customer list, booking history, and so on. Normally data backup is taken by database administrator but if you are working for any organization then it’s your responsibility to maintain the backup of your data which is a very crucial task for protecting data. Sometimes making a backup of your database is not enough for protecting data because backup can get corrupt too. So checking the integrity of your database backup is also essential. The same concept is applied in SQL server. SQL server backup helps in recovery of corrupt or deleted database.

How to Check your Backup?

If you have made backup of your SQL server database and did not checked its integrity yet then I will recommend you to check it now by below syntax:

RESTORE VERIFYONLY FROM DISK = ''

To illustrate it, I have made a backup of my database. I have made another copy of my backup & corrupted it intentionally.

Name of my perfect backup is perfectdatabasebackup.bak.

Name of corrupt database backup is corruptdatabasebackup.bak

First I will run RESTORE VERIFYONLY syntax on the perfect database backup and the syntax is given below:

RESTORE VERIFYONLY FROM DISK = 'C:\Database Backup\perfectdatabasebackup.bak'

Output: The backup set on file 1 is valid.

As the output is indicating that my backup is OK

Now I will run RESTORE VERIFYONLY syntax on the corrupt database backup and the syntax is given below:

RESTORE VERIFYONLY FROM DISK = 'C:\Database Backup\corruptdatabasebackup.bak'

Output:

Msg 3242, Level 16, State 2, Line 1

The file on device 'C:\Database Backups\corruptdatabasebackup.bak' is not a valid Microsoft Tape Format backup set.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.

As the output is indicating that backup is not valid.

If you are trying to restore your database from backup and unable to do that then use RESTORE VERIFYONLY command on the database backup to verify it.

Conclusion: It is recommended to run RESTORE VERIFY command on SQL database backup after every successful backup.