Backup With Checksum

Question

Backup With Checksum

hayden_jones

SSCrazy

Points: 2099
More actions
December 6, 2013 at 2:29 pm

#285751

In searching for information on checksum, I see statements like
"When backing up a DB Checksums are checked for pages that have a Checksum".
Well, I want all the backed up pages to have a checksum. After I change the
torn-page default to checksum, that's not enough?
I see advice here and there on the internet to rebuild all the indexes after setting the DB
checksum option. Is that necessary? Is there another way to get a checksum
on every page? Is there a TSQL command that can count the pages
that have (or don't have) a checksum?
Or am I all set when I set the checksum option on the DB and don't have to worry about
all those questions?

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Turning the checksum option on does nothing at that point. Only when the page is next written out will the checksum be added. Rebuilding all indexes is the best way to get checksums on most pages.

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

hayden_jones SSCrazy Points: 2099 More actions · Answer 2

On a big database running a maint plan to rebuild all the indexes can take a very long time. The, too, some indexes cannot be rebuilt. Seems like there should be a better way to do this. It would be a lot quicker to shut down the application, back up the database and restore from the backup. Would that (since all pages would be written to disk) add a checksum to all pages? or would that just put the page image back the way it was?

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

A restore recreates the database exactly as it was when it was backed up. The only thing that puts a checksum on a page is when that page is read into the buffer pool, modified and written back to disk.

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

hayden_jones SSCrazy Points: 2099 More actions · Answer 4

Thanks,

Then to the last question in the original post: Is there a TSQL command that can count the pages

that have (or don't have) a checksum?

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

No. You could probably write something complex that iterates through all the pages in the DB, runs DBCC page and parses the results, but it'll be complex. Might be something on Paul Randal's blog to do that.

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

hayden_jones SSCrazy Points: 2099 More actions · Answer 6

This be wierd.

I

altered a database (previously restored to SQL2008R2 instance) to do page verification with CHECKSUM.

verified by looking in sys.databases.

rebuilt all indexes using a maint plan.

Took a Backup

did RESTORE VERIFYONLY FROM DISK = 'PATH\FILE' WITH CHECKSUM

and got

Msg 3187, Level 16, State 1, Line 1

RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.Same thing happens if backup is taken WITH CHECKSUM.

Same thing happens if I take backup WITH CHECKSUM.

Same thing for database created in SQL 2008 R2 with CHECKSUM as a default.

hayden_jones SSCrazy Points: 2099 More actions · Answer 7

I messed up there by concatenating "Same thing happens..." with the message. Sorry.

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

If a backup wasn't taken WITH CHECKSUM, then you can't use that option on the restore because there's no checksum to check. This is regardless of the database's page_verify setting.

No edition or version of SQL has CHECKSUM as a default option on backups. If you want backups taken WITH CHECKSUM, you have to specify it.

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

hayden_jones SSCrazy Points: 2099 More actions · Answer 9

Yep, tried backup WITH CHECKSUM. Restore still errored out just the same.

hayden_jones SSCrazy Points: 2099 More actions · Answer 10

hayden_jones

SSCrazy

Points: 2099

December 11, 2013 at 2:58 pm

#1672773

Like I said. This be wierd

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

Make sure you're restoring the same backup you took, not another within the same backup file if you're appending.

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

hayden_jones SSCrazy Points: 2099 More actions · Answer 12

I guess that was what I was doing. I thought I was deleting the backup before

I took another. But it's working fine.

Thanks.