Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup With Checksum Expand / Collapse
Author
Message
Posted Friday, December 06, 2013 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
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?
Post #1520750
Posted Friday, December 06, 2013 2:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 41,548, Visits: 34,470
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 2008, MVP
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

Post #1520754
Posted Friday, December 06, 2013 4:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
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?
Post #1520777
Posted Saturday, December 07, 2013 1:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 41,548, Visits: 34,470
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 2008, MVP
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

Post #1520797
Posted Tuesday, December 10, 2013 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
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?

Post #1521543
Posted Tuesday, December 10, 2013 8:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 41,548, Visits: 34,470
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 2008, MVP
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

Post #1521546
Posted Wednesday, December 11, 2013 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
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.
Post #1522021
Posted Wednesday, December 11, 2013 12:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
I messed up there by concatenating "Same thing happens..." with the message. Sorry.
Post #1522023
Posted Wednesday, December 11, 2013 1:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 41,548, Visits: 34,470
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 2008, MVP
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

Post #1522031
Posted Wednesday, December 11, 2013 2:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 41, Visits: 227
Yep, tried backup WITH CHECKSUM. Restore still errored out just the same.
Post #1522074
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse