| Author |
Topic |
|
markham
Starting Member
5 Posts |
 Posted - 2006-01-11 : 12:07:17
|
| I have a question regarding the backup to use in case of consistency errors.I'm backing up my dbs daily and logs hourly with 'verify the integrity of the backup when completed' checked. Today I found out one of my tables has 1 page id error (dbcc checkdb message from yesterday), I'm wondering if I can use yesterday's full backup even if it's done after the dbcc, that way I don't have to apply a lot less log backups. In other words, does or does not the full backup inherit the corruption if it is done after consistency errors and the backup passes the integrity verification.Thanks,Markham |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-11 : 12:28:50
|
| I suggest you try restoring it and if the restore is OK then do a DBCC CHECKDB to ensure that the database is OK before you go any further.IME the only really good test that a backup has worked is to restore it to a database on a different server and do a DBCC CHECKEDB.Kristen |
 |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-11 : 14:21:52
|
| Agreed. Unless you know the exact time period in which the corruption occured (e.g. between a clean CHECKDB and one with errors) and whether the corrupt page was backed up after the bad CHECKDB, it's very difficult to tell whether the backup picked up the corruption too.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
markham
Starting Member
5 Posts |
Posted - 2006-01-12 : 11:09:59
|
| Thanks for your responses.I'm running CHECKDB daily and that's how I know that backup was done After the first CHECKDB error. I just don't know why the backup passed the intergrity verification. How does backup intergrity verification work? Can it catch problems in the same way as CHECKDB or it simply is very much less useful?Thanks,Markham |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-12 : 13:32:11
|
| "it simply is very much less useful?"That's my opinion. Restoring it proves that the media is recoverable, as well as the integrity of the database backup itself.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-12 : 13:34:52
|
| Backup integrity verification has nothing to do with the integrity of the database. If the backup verification passes, it doesn't even mean that you can use it for a restore. The only way to know if a backup is in fact restoreable is to perform a restore. That's why this needs to be done regularly. So the only way to know if the database is good is to run DBCC CHECKDB and the only way to know if the backup is good is to run RESTORE. We don't even bother with the backup integrity process.Tara Kizeraka tduggan |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-12 : 13:45:07
|
| Agreed. That process has to be part of a backup strategy.In SQL Server 2005 there is a a BACKUP option WITH CHECKSUM that will verify the page checksum/ton-page bits when reading them from disk and before writing them to the backup - that's a small step in the right direction.What we really need to do is allow CHECKDB of a database within a backup without having to restore the backup (and needing the free disk space that requires). It just so happens that I have a patent on a method of doing that which we plan to put into a future release of SQL Server.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-12 : 13:49:13
|
quote:
It just so happens that I have a patent on a method of doing that which we plan to put into a future release of SQL Server.
All the good ones are married!Tara Kizeraka tduggan |
|
|
|
markham
Starting Member
5 Posts |
Posted - 2006-01-12 : 14:14:08
|
| Thank you all.I have another question. CHECKDB succeeded without any errors the next day after failed the first time, complaining a page ID error of a cluster. The strange things are 1) CHECKTABLE never gave any errors on the table 2) rebuild failed from the same page ID error. The question is: Can the first CHECKDB error be a false alarm? Or the success one is false since the rebuild failure? The database is quite large (50GB), and I only have a small window to run the check everyday.Thanks,Markham |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-12 : 14:41:18
|
"All the good ones are married!"Brilliant! ... Are you Patented?  Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-12 : 14:45:48
|
quote:
Originally posted by markham
Thank you all.I have another question. CHECKDB succeeded without any errors the next day after failed the first time, complaining a page ID error of a cluster. The strange things are 1) CHECKTABLE never gave any errors on the table 2) rebuild failed from the same page ID error. The question is: Can the first CHECKDB error be a false alarm? Or the success one is false since the rebuild failure? The database is quite large (50GB), and I only have a small window to run the check everyday.Thanks,Markham
Can you post all the errors you got (or send me email) along with the commands you ran (incl the CHECKTABLE)?ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-01-15 : 00:54:49
|
| Do you reindex nightly? It's possible that the CHECKDB error was on an index and you blew away the index. I would be interested in seeing the error as Paul pointed out. Depending on the error, you might need to check the integrity of your hardware (no jokes intended). Hard to tell though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
Valid backup to use