question

jctronicsshiva avatar image
jctronicsshiva 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 ?
sql-server-2005databasebackup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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!!!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
@Grant Fritchey is wise. Check also his backup rant at http://www.red-gate.com/products/dba/sql-backup/version-7
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ha! I actually thought about posting a link to the rant but figured the article supporting it would be in better taste.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.