Share via

BackupRestore & Corruption – What is worth to know

  • Article

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!

https://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/05/oh-no-my-backup-is-corrupt-too-help.aspx

 

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 -

Comments

  • Anonymous
    January 01, 2003
    Whole heartedly agree with you.  HyperBac (developed by founding developers of LiteSpeed) is a unique 3d party service based solution does backup compression which uses native BACKUP commands and can thus be verified using RESTORE VERIFYONLY commands in TSQL or via SQLMS or SQLEM with the HyperBac service running.