September 7, 2009 at 1:37 pm
Lets say you run dbcc checkdb and find some corruption in the database. You decide to restore the last successfull backup to resolve the issue. How can you be sure that the backup does not also contain the problem?
September 7, 2009 at 2:29 pm
Why not run these checks before your backup operation?
As for existing backups, just restore them to another location, check the DB and then restore again if needed onto real DB itself
September 7, 2009 at 2:58 pm
Cool. thanks for the answers. im just wondering as if such an issue occured, and you had to restore, chances are you dont have time to restore a 300gb database somewhere else to check for consistencies. i was thinking there might be some checks you can perform on restore but i cant find anything. looks like doing a check before each backup is the only option. thanks.
September 7, 2009 at 3:12 pm
hi
there are some backup settings that you can use to be sure that your backup is ok 😉
1) checksum
BACKUP DATABASE TO DISK = WITH CHECKSUM
BOL about checksum:
CHECKSUM
Enables backup checksums, so that BACKUP can do the following:
Prior to writing a page to the backup media, BACKUP verifies the page (page checksum or torn page), if this information is present on the page.
Regardless of whether page checksums are present, BACKUP generates a separate backup checksum for the backup streams. Restore operations can optionally use the backup checksum to validate that the backup is not corrupted. The backup checksum is stored on the backup media, not on the database pages. The backup checksum can optionally be used at restore time,
Using backup checksums may affect workload and backup throughput.
This is the default behavior for a compressed backup.
2) verify backup
RESTORE VERIFYONLY FROM DISK = WITH FILE =
you can find backup set id in this way:
select position as BackupSetId from msdb..backupset where database_name= and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name= )
BOL about verifyonly:
SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.
The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup. Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system.
and one more thing Paul Randal figured out a great thing: checkdb againt backup ... but ... he has but but Katmai don't ;/
http://www.patents.com/System-a-consistency-check-a-database-backup/US7277905/en-US/
September 8, 2009 at 12:58 am
winston Smith (9/7/2009)
Cool. thanks for the answers. im just wondering as if such an issue occured, and you had to restore, chances are you dont have time to restore a 300gb database somewhere else to check for consistencies. i was thinking there might be some checks you can perform on restore but i cant find anything. looks like doing a check before each backup is the only option. thanks.
You should be running CheckDB often enough that when you discover corruption, you can go back through the logs, see exactly when it started failing and use that to select a backup for restore.
If you aren't running checkDB often (or at all), then you'll have to restore the backups and test them out
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
September 8, 2009 at 3:07 am
winston Smith (9/7/2009)
Lets say you run dbcc checkdb and find some corruption in the database. You decide to restore the last successfull backup to resolve the issue. How can you be sure that the backup does not also contain the problem?
This seems a very similar question to the other one you have posted in this topic...?
Other than that, Marcin and Gail have already covered the answers I would give.
edit: Maybe I will just add that some people are paranoid enough to take backups of their backups - or to use the MIRROR TO and COPY_ONLY options of the backup command. You can never have too many backups (or enough disk space :))
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 11:18 am
You could always use the copy_only option, at least you know that it doesnt interfere with your normal backup strategy.
But taking backups of your backup is extreme, unless you do a monthly DR exercise and restore backups to a scratch server (test environment) and run checks on the database that you have restored.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply