asked
How do i verify a DB backup
Suppose i have taken a DB backup, How do i verify if the backup is alright. I know about 'RESTORE VERIFYONLY FROM DISK', but i suppose this only checks if the backup is corrupt. What i want to check is , if there are 1000 entries lets say, how can i be sure that all that 1000 entries are copied correctly? is there any mechanism to find that from the backup file ?
Backups are a page by page copy of the database. It's not an import/export process. It's copying the storage pages of the database. So if you have 1000 entries (I assume you mean rows) in the pages of the database storage, that's how many get copied. But, you're right to worry about backups. They can go wrong. I've got a [short article on Simple-Talk][1] laying out all the verification checks you can do to ensure that a backup is good. [1]:
https://www.simple-talk.com/sql/backup-and-recovery/backup-verification-tips-for-database-backup-testing/
You can add the 'Checksum' option to verify each page before it's written and as you mention the verify after backup to ensure the backup file is valid. However you cannot verify any business data in the backup file without restoring it and executing the desired check.
The simplest and most reliable way to verify a backup is whole and complete is to restore it. You should all have a restore validation process in place to regularly restore and verify your backups. What works today might break sometime in the near future and you find out you have been backing up air instead of your data. Trust but verify!!!!!
@Grant Fritchey is wise. Check also his backup rant at
http://www.red-gate.com/products/dba/sql-backup/version-7
Ha! I actually thought about posting a link to the rant but figured the article supporting it would be in better taste.
16 People are following this question.
