Checking Backup Files

Question

Checking Backup Files

raotor

Hall of Fame

Points: 3346
More actions
December 12, 2012 at 6:56 am

#268088

Hello,
Having read an article about restoring from your backups and the things you can do to minimize potential problems therein, I saw the following command:
RESTORE HEADERONLY ....
This was suggested as a way of testing the header(s) in your backup file.
I also came across the following:
RESTORE VERIFYONLY ...
Which I think, from memory, stated that it was the closest thing to actually recovering without doing so.
Does anyone have views as to which of the above is best?
Regards
Steve

Viewing 6 posts - 1 through 5 (of 5 total)

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

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

Hi Steve,

Restore Verifyonly will tell you whether backup file is valid or not ,so no doubt its best option to check whether your backup files are restorable or not and always recommended to verify your backup files after backup operation

-----------------------------------------------------------------------------
संकेत कोकणे

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

There's only 1 valid test.

FULL restore, including diffs and logs.

THEN run checkDB.

Now you know you're good.

Once that's done you can't move the files because they could get corrupted in the transfer. Of course it could still happen sittin on the disk. So yes, if you can, retest old backups every once in a while.

raotor Hall of Fame Points: 3346 More actions · Answer 3

Ninja's_RGR'us (12/12/2012)
There's only 1 valid test.
FULL restore, including diffs and logs.
THEN run checkDB.
Now you know you're good.
Once that's done you can't move the files because they could get corrupted in the transfer. Of course it could still happen sittin on the disk. So yes, if you can, retest old backups every once in a while.

Granted.

But, if you can't actually restore, then what's the next best thing?

e4d4 SSCertifiable Points: 5777 More actions · Answer 4

raotor (12/12/2012)
Ninja's_RGR'us (12/12/2012)
There's only 1 valid test.
FULL restore, including diffs and logs.
THEN run checkDB.
Now you know you're good.
Once that's done you can't move the files because they could get corrupted in the transfer. Of course it could still happen sittin on the disk. So yes, if you can, retest old backups every once in a while.
Granted.
But, if you can't actually restore, then what's the next best thing?

Then use checksum:

BACKUP DATABASE test

TO DISK = 'd:\test_checksum.bak'

WITH CHECKSUM, INIT;

RESTORE VERIFYONLY

From disk = 'd:\test_checksum.bak'

WITH CHECKSUM

raotor Hall of Fame Points: 3346 More actions · Answer 5

*** it happens, I already use CHECKSUM. I also run a DBCC CHECKDB on the database before being backed up ensuring that should that fail that the backup is not done and an alert is raised.

Hopefully, all these measures will increase the chances of a successful restore should I need it.