Maintenance Plan - Backup with checksum?

Question

Maintenance Plan - Backup with checksum?

beppe_30

Say Hey Kid

Points: 676
More actions
June 21, 2010 at 11:57 pm

#223014

We are in the process of upgrading our SQL 2K to SQL 2K8.
We would like to use Maintenance Plan to backup our databases but we also want to use the new option "with checksum".
This new option is not selectable during the construction of the maintencance plan.
Do you know how we can solve this problem?
Thank you very much.

Viewing 11 posts - 1 through 10 (of 10 total)

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

David Baffaleuf SSCrazy Points: 2159 More actions · Answer 1

Hi beppe,

Indeed the maintenance plan offers a 'Verify Backup Integrity' check box in the backup section. The command runs a restore verifyonly but the backup hasn't been generated with checksums. The restore command just checks whether the backup set is valid or not.

May I suggest you to code the same logic in a SQL Agent job ? This is where you may have control on the arguments.

HTH

David B.

mahalingam_moorthy SSC Enthusiast Points: 146 More actions · Answer 2

hi sir/madam,

you can change the database set option:

Like this:

alter database "databasename" set page_verify

USE [master]

GO

ALTER DATABASE "database Name" SET PAGE_VERIFY CHECKSUM WITH NO_WAIT

GO

first change the database option

then add the database on the maintenance plan wizard which u need.

regards,

M.Mahalingam

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Page verify = checksum and backup checksums are separate things. Setting a database's page verify to checksum will not cause the backup to be taken with the Checksum option by default.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

David BAFFALEUF (6/22/2010)
May I suggest you to code the same logic in a SQL Agent job ? This is where you may have control on the arguments.
David B.

I agree with David's suggestion. Because there is no UI for some of the commands/options.

beppe_30 Say Hey Kid Points: 676 More actions · Answer 5

OK, thanks for all your reply but can someone give an esample on how to code this option with SQL agent job?

Cheers.

David Baffaleuf SSCrazy Points: 2159 More actions · Answer 6

Hi Beppe,

You'll find plenty of examples on the web. The idea is to run BACKUP DATABASE with the CHECKSUM option. The CONTINUE_AFTER_ERROR option will force SQL Server to carry on the execution even if it finds a checksum error. The default behaviour causes SQL Server to interrupt the backup sequence.

Next to the BACKUP command, you will need to use the RESTORE VERIFYONLY ... WITH CHECKSUM command to validate the checksums.

See Paul Randal's blog post on using these options here.

MSDN referene on BACKUP: http://msdn.microsoft.com/en-us/library/ms186865.aspx

MSDN reference on RESTORE VERIFYONLY: http://msdn.microsoft.com/en-us/library/ms188902.aspx

David B.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

David BAFFALEUF (6/30/2010)
The CONTINUE_AFTER_ERROR option will force SQL Server to carry on the execution even if it finds a checksum error.

I wouldn't recommend that as a default option. It's good to have when necessary, but personally I'd rather back failed if it encountered corruption than it happily backing up a corrupt database and not warning me.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Baffaleuf SSCrazy Points: 2159 More actions · Answer 8

Hi Gail,

I totally agree. I don't consider CONTINUE_AFTER_ERROR as a *must* option either. I just wanted to mention that in some circumstances you may need to have the backup finish its course whatever happens.

David B.

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 9

beppe_30 (6/21/2010)
We are in the process of upgrading our SQL 2K to SQL 2K8.
We would like to use Maintenance Plan to backup our databases but we also want to use the new option "with checksum".
This new option is not selectable during the construction of the maintencance plan.
Do you know how we can solve this problem?
Thank you very much.

There are many reasons why I don't like maintenance plans and one of them is that there is NO support to use BACKUP CHECKSUM. I wrote that this a while ago at

http://sankarreddy.com/2010/02/another-reason-why-i-dont-like-maintenance-plans/[/url]

Normally I don't recommend people to use MP and encourage them to move to T-SQL scripts. There are quite a few good scripts in T-SQL that are shared online for general use and you can pick any of them and I would recommend using scripts from Ola hallengren at http://ola.hallengren.com/[/url] though.

Just a thought that using BACKUP CHECKSUM, there is a slight cpu cost attached to it, so hopefully you may want to schedule accordingly.

I did some tests a while ago and here are my findings. YMMV, test it out few times.

http://sankarreddy.com/2010/03/performance-impact-of-using-backup-checksum-in-sql-server-20052008/

[/url]

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

calico-604598 Right there with Babe Points: 730 More actions · Answer 10

Hi,

I know this article is old.

If you use Maintenance Plan, then backup use checksum if trace flag 3023 is set

http://support.microsoft.com/kb/2656988/en-us

Danny