I had a bad experience before. I always took database backup but never tried to verify if that backup was good or not. I thought that if a backup file was successfully created, it means that that file is good. But I’m totally wrong till that day when disaster happen on one of our client database. Their server had a problem and need to migrate their system and database into another server.
I was asked, how many data they will losing if i restore their databases from my last backup file? Luckily I’m not answer that question immediately but tell them to prepare that new server and I’ll double check the backup files to ensure how much their data will be losing.
I’ve tried to restore last database backup files into a separate server but it failed. there is an error says that no backup set available. So i try to restore the other backup files before that, and found that last 3 days backup files was corrupt. This database was not critical enough, not too much data changed for three days, but still too much to lose.
Learn from my mistake, next time after I took a database backup, I need to verify if that backup file was good or not. There are 2 ways to verify database backup files :
You can Restore database backup files into another new database. This is the best way to know that database backup is good and able to restored. After you restore backup files to a new database, you may need to perform DBCC CHECKDB operation to check the logical and physical integrity of all object in that database.
Restore With VERIFYONLY Option
RESTORE VERIFYONLY FROM DISK = 'F:\BACKUP\AdventureWorks2012-Full-Backup.bak' GO
Restore database backup files with option VERIFYONLY. If you need to verify your backup file but doesn’t need to restore that file, you can restore backup with this option. This option will process the backup and perform several check such us :
- Find and read the backup files
- Walk through CHECKSUM if using CHECKSUM option when perform backup database task
- Check some header information
However, depending on disaster recovery strategy, relying only on RESTORE VERIFYONLY might not enough. You know that you need to protect the information for your organization. You already running a database backup, now you have to take the next step to ensure that those backup files are good and remember that the very best method to test is complete restore those files.