x

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 ?

more ▼

asked Mar 20, 2013 at 11:31 AM in Default

avatar image

jctronicsshiva
470 14 14 19

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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 laying out all the verification checks you can do to ensure that a backup is good.

more ▼

answered Mar 20, 2013 at 11:59 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left

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!!!!!

more ▼

answered Mar 20, 2013 at 03:03 PM

avatar image

Tim
40.4k 39 85 166

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 20, 2013 at 11:41 AM

avatar image

tanglesoft
1.8k 4 6 11

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Mar 20, 2013 at 03:12 PM

avatar image

ThomasRushton ♦♦
41.3k 20 50 53

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

Mar 20, 2013 at 03:18 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2019
x292
x244

asked: Mar 20, 2013 at 11:31 AM

Seen: 1587 times

Last Updated: Mar 20, 2013 at 03:18 PM

Copyright 2016 Redgate Software. Privacy Policy