September 21, 2012 at 12:49 pm
Hey Grant, just saw your new video. Even though I'll vote for you when the Emmys come up, I still have a question. I do all the stuff you recommend, up through the actual restore - BUT! How do I know that the restore process actually restored ALL of my database? ALL my records, ALL my tables, views, procedures, indexes...
Doesn't complete confidence require me to verify the restore, after it's done? Use something like DBCompare (no, I don't secretly work for RedGate) to check the restored version against the current version? Maybe even have something to check the restored DB + up-to-current transaction logs against the current status of the live database?
Yeah, SQL Server should let you know if had problems doing the restore, but in a perfect world, none of this would be necessary. All the steps in creating backups are based on the presumption that something, somewhere will go wrong. If you stop at just performing the restore, you still don't know if the restored copy is actually any good.
September 21, 2012 at 12:54 pm
If the database restores you can be pretty sure that it is exactly as it was at the time of backup. Want to be completely sure, run CheckDB on the restored DB, or take the backup and do the restore WITH CHECKSUM
A restore that succeeds without error won't just randomly discard portions of the DB, and if the backup and restore are both WITH CHECKSUM, then any modification of the backup file will be caught by the restore and will cause the restore to fail
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 21, 2012 at 1:00 pm
I realize I'm getting pretty far out there with all this, but...
If the backup with checksum can look okay, but still be corrupt, couldn't restoring that failed backup also look okay, but be useless?
September 21, 2012 at 1:08 pm
No, because backing up a corrupt database WITH CHECKSUM will fail (it checks the page checksums as it backs up). Hence, if a backup taken WITH CHECKSUM restores successfully WITH CHECKSUM, you can be pretty sure the restored DB is fine.
Besides, you're doing regular CheckDB of the source DB, so you'd know if it was corrupt, right.
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 21, 2012 at 1:23 pm
Well, then why does Grant (and others) make a point of saying you should test-restore the backup? If the checksum is enough to guarantee that the backup was done correctly, that should be enough the end the matter, but he claims that even with a correct checksum, you can't be sure that the process completed correctly.
September 21, 2012 at 1:27 pm
I never said the checksum was enough to ensure that it would restore correctly. I said if it restores correctly and the backup and restore were done with checksum, then the restored DB is going to be OK.
The backup checks the page checksums and since you're doing a regular checkDB you know that the source DB is not corrupt. Then a successful restore with checksum tells you that nothing mangled the backup file after it was written 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 21, 2012 at 1:36 pm
Okay, that makes sense. But then I don't understand what can be wrong with the backup. If I run checkDB, I know my database is good. If I back up that known good database with checksum, and that runs correctly, I (theoretically) know my backup is good. What can be wrong in such a case, that might cause the restore to fail, and so my backup to be no good after all?
September 21, 2012 at 1:49 pm
A hardware or IO failure. Disk sectors can go bad on you.
September 21, 2012 at 2:06 pm
Lynn Pettis (9/21/2012)
A hardware or IO failure. Disk sectors can go bad on you.
Well, yes, but that can happen even after you've created and test-restored a backup. I plan for that by making several copies of each backup, on and off site. I'm wondering what can go wrong with the actual backup process, that can create a seemingly good, but unuseable backup.
September 21, 2012 at 2:15 pm
Not talking about the backup process itself.
The IO subsystem could incorrectly write the file. It could mangle the file after backing it up, it could corrupt the file during a copy. Same things that cause database corruption.
If I back up that known good database with checksum, and that runs correctly, I (theoretically) know my backup is good.
You know that the backup completed successfully. Not the same as knowing that the backup file is good.
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 21, 2012 at 3:18 pm
GilaMonster (9/21/2012)
Not talking about the backup process itself.You know that the backup completed successfully. Not the same as knowing that the backup file is good.
I guess I see. Of course, once it's back in SQL Server, it's just as subject to corruption as ever, same as the stored and verified backup can be on a sector that goes bad AFTER all the tests and verifications have been performed and passed. I guess there's no such thing as completely fail-safe; you just have to drive the probablility down to something you can live with.
I'm starting to feel like that anonymous king: "I know I'm paranoid. But am I paranoid enough?"
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply