BackupRestore & Corruption – What is worth to know
Just being discussing recently the Backup\Restore best practices (related to corruption) and wanted to share some information.
SQL Server Backup\Restore in SQL (when actually done directly from SQL, not from a 3rd party application - I come to that in a second) offers many options which are:
- Checking the backup consistency while performing the backup
- Checking the backup consistency after the restore (therefore by running DBCC CHECHDB on the restored Database)
- Checking the backup consistency with the restore command without actually performing a restore
Everything is very well explained in this article from (surprise surprise...) Paul Randal:
Oh no – my backup is corrupt too! Help!
Now, with a 3rd party application things are a bit different as you need to stick with the options that it gives to you.
I've just checked Litespeed options (as an example) and all that I can find is a "Verify Backups" option in the restore wizard.
Now this means that
- you won't be able to check backups when actually creating a backup
- you don't have anything similiar to WITH VERIFYONLY, so you need actually to restore your database to check consistency of your data
This leaves you with the two options:
- Restore with the "Verify Backups" option provided from the Wizard
- Restore and then run DBCC CHECKBD
I am not sure which one would be more efficient. I would guess all that the "Verify Backups" option does is actually to automatically run DBCC CHECKDB but happy to be contraddicted.
Any case, rule of thumb is to ALWAYS DBCCC CHECK your databases (daily) and ALWAYS CHECK YOUR BACKUPS (daily would be best but I already know the opinions on this... say weekly). How- is up to you!
- Beatrice Nicolini -