13

Currently we use standard maintenance plans for backups on the SQL Server 2005/2008/2008R2/2012 servers in our environment, and the "Verify backup integrity" box has always been checked.

Some of the backups run very long, so I've recommended turning off that option, but management needs me to document the impact and risks of this change.

I understand the use and history of this option, it just seems unnecessary to me to double the time for the backup job when (in my opinion), any error that is likely to occur would occur during the backup step, not during the verify.

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something? (We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Is there any official MS recommendations for when it is safe to turn this off?

Do you run "verify" on every backup in your environment? Do you spot-check them?

EDIT: To clarify, when you check "verify backup integrity" in the maintenance plan, SQL will do a full RESTORE VERIFYONLY on each database immediately after each backup. This is just as data/IO intensive as the original backup, and (basically) doubles the overall time of the backup job. This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).

Improve this question
1
  • Thanks, everyone. Adding one more link for my own reference, about using a trace flag to enable checksum on backups, even when using SQL Maintenance plans: nebraskasql.blogspot.com/2014/03/…
    – BradC
    Commented Oct 6, 2015 at 16:09

3 Answers 3

Reset to default
5

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something?

No, you are right :-)

RESTORE VERIFYONLY only will not ensure that you will be able to recover your database in the event of corruption. By nature, it will not perform any integrity checks.

A better way will be to periodically take your backups and do a valid restore on a different server and perform DBCC CHECKDB on it.

This is one reason, why I am not a big fan of maintenance plans since the GUI does not expose a lot of options like backup .. with CHECKSUM that can be achieved by T-SQL.

From Paul Randal's Myth blog

24p) using RESTORE … WITH VERIFYONLY validates the entire backup

No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY and using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.

Do you run "verify" on every backup in your environment? Do you spot-check them?

I dont run the VERIFYONLY. Instead I take backup with CHECKSUM and then have them restored + CHECKDB'ed on a different server. You can follow Statistical Sampling for Verifying Database Backups approach if you want to be creative.

This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).

You can enable Trace Flag 3023 so that the CHECKSUM option is automatically enabled for the BACKUP command. As always, test the behavior of any trace flags in your environment !

The bottom line is - ditch maintenance plans and use a more sensible backup solution (hint: Ola's backup solution) that will allow you to customize it based on your needs.

(We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Backup up locally to the disk and then have a PowerShell transfer job that will copy the backup locally from the server to a network share (backup server). That will be more faster than to directly copy to a network share.

Also, enable instant file initialization, that will help with auto-growths on data files as well as will help cutdown the restore time (incase if you have to restore your databases). Its always good to have options handy.

A good read will be : Backups: Planning a Recovery Strategy

Improve this answer
3
  • Thanks for your detailed answer. I think I may recommend enabling checksum on backup, which should catch a slightly higher percentage of errors during the backup step, and should hopefully offset the (very slight) higher risk of skipping the BACKUP VERIFYONLY. I understand what you're recommending with regard to regular restores onto a different server, but due to the size of our environment, that doesn't seem plausible, except perhaps on a sampling basis.
    – BradC
    Commented Oct 5, 2015 at 17:39
  • @BradC Glad that my answer is useful to you. The gist of my answer is to use TSQL as opposed to GUI (maint plans) so that you can leverage the flexibility and adapt it with open hands. Just as an FYI.. maintenance plans have been enhanced in SQL Server 2016 CTP 2.4 which MS calls as SQL Server Smart Maintenance Plans - integrates best practices, and can identify optimal strategies on the fly. Still no GUI can beat TSQL :-)
    – Kin Shah
    Commented Oct 5, 2015 at 18:55
  • Thanks @Kin. I'm familiar with the all-custom script approach from other environments, obviously that can have its own issues with errors and script maintenance. We are evaluating some 3rd-party compression agents, so will likely need custom scripts eventually.
    – BradC
    Commented Oct 5, 2015 at 20:13
2

The bottom line is that unless you do a database restore somewhere, you can't be completely confident that a given backup file is good.

The ideal test to verify your backups is to setup an environment where the database backups, and the database log backups, are restored all the time as part of your day-to-day-process. This is one of the advantages of using log-shipping...

If you still want to stick with verifyonly, you could setup an environment specifically just to do this. This would offload the work from your (presumably) production server, and reduce the job time.

Finally, have you considered moving away from maintenance plans? Scripts like Ola's scripts are except for backups and maintenance: https://ola.hallengren.com/

Improve this answer
1
  • We may move away from maintenance plans in the future, since we are evaluating some 3rd-party backup agents (that are made to work with our specific backup appliance). I'm assuming these will require custom scripts, similar to what Ola has developed.
    – BradC
    Commented Oct 5, 2015 at 16:20
1

Technically the restore verfyonly is like performing a restore, however there is no better check that actually restoring the database to check its a valid backup. We've had an instance where verifyonly passed however the database wouldn't restore due to corruption, my point is don't count on this as a verification the backup is ok. We've now developed a system which restores all our databases on a separate instance to check their validity, clearly that's not always possible so try sampling certain database a week. The long and short don't trust verifyonly 100%.

Improve this answer
2
  • True, but I'm not sure if that really answers my question, since I'm recommending turning off the RESTORE VERIFYONLY in our environment. Unless your point is: "since only an actual full restore will prove the backup is viable, turning off this option doesn't appreciably increase the risk".
    – BradC
    Commented Oct 5, 2015 at 16:15
  • Correct, the only real check imo is the actually perform a restore for real
    – MrG
    Commented Aug 3, 2016 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.