How to Verify the backup

Question

How to Verify the backup

Abhishek Kotha

SSC Eights!

Points: 934
More actions
August 21, 2007 at 7:16 am

#118140

Hi everybody,
How to verify that the database backup which we took is good enough to retore it.
Thankyou,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 1

The best thing is to restore the backup file to your dev or test server or any server you can possibly with a new name if there is a database with the same name as the original. That way you would know for sure once it is successuly restored. check out the books on line for restore database statement you can restore the database with different name and move the data and log file to another location if you need to.

RESTORE DATABASE MyNewDatabase FROM DISK = 'ThisIsThePathToYourBackupFile'

WITH MOVE 'MyOldDB_Data' TO 'D:\DATA\MyNewDatabase_Data.MDF',

MOVE 'MyOldDB_Log' TO 'E:\LOG\MyNewDataabase_Log.LDF';

Good day...

Gopal-122691 Mr or Mrs. 500 Points: 517 More actions · Answer 2

To verify the backup, run this command

RESTORE VERIFYONLY

FROM disk = 'ThisIsThePathToYourBackupFile'

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 3

RESTORE VERIFYONLY

Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes.

This is from books on line. keep that in mind.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 4

Use Verifyonly, but periodically run a restore from the backup file. It's good practice for you.

Also periodically restore from tape or whatever archive media you're using. Ensure that it's really writing stuff there you can use.

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 5

... and how do you know the structure of data is one good?

In Theory, theory and practice are the same...In practice, they are not.

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 6

Look into BOL for the following DBCC commands.

DBCC CHECKDB

DBCC CHECKTABLE

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 7

I know these commands, but I referred to the data itself not the objects of sql server like tables, triggers, stored proc. etc.

In Theory, theory and practice are the same...In practice, they are not.

Karl Hewlett Mr or Mrs. 500 Points: 528 More actions · Answer 8

Hi

The only means I know to confirm a restore is to detail some data changes in the backed up DB before and after the restore-to-time. Then after restoration confirm that each before change is reflected in the data and no after change is. I have done this previously by adding rows to tables and confirming the row is present after the restore.

If the system is a Prod one where you cannot add data to Prod tables, create a table and work with that one.

You can also try to find a valid Prod transaction that a user completes, e.g. if you have someone who can confirm that at a given time they updated a record to a known value, look for that value in the restored DB.

IMHO this is the only truly meaningful validation of a backup. That is to say that validation of the backup file is not a complete test. I have seen files pass validation but the DB not restore due to issues related to other software, hardware, process, etc.

Regards

Karl