|
We validate all our database backups by shipping them to another server, restoring them and running a dbcc checkdb. Some tweaks are necessary to move the files around, but not a great problem, and it has brought some interesting problems to light before they had further consequences. Anyhoo, since upgrading to ss08r2, we haven't been able to verify backups of the master databases using this method. They restore ok, they recover ok, but they fail dbcc checkdb as shown below. Each backup is restored to a test database rather than stomp on the ms shipped databases. I know it doesn't make a lot of sense (outside this context) to restore a master database other than as a master database (and notably on the same sql server release), but pre-R2 it worked and on R2 it doesn't. Source and target servers return identical values for @@version: "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)", except the target has "(Hypervisor)" appended, presumably because it's a Hyper-V host. (I know... not a good idea). Any ideas? Is there another way to fully check that the backup will restore without restoring as THE master database? restore script: results from "dbcc checkdb ('test') with no_infomsgs":
(comments are locked)
|
|
OK, I got the answer from the person who knows. Paul Randall (he wrote the current version of CHECKDB) confirms (via Twitter):
The way to check Master for errors run CHECKDB daily on the production database and exclude it from any automated Restore/CHECKDB process. Paul also very kindly offered to write a blog post on this as it is not currently documented anywhere. I will update this question with a link when the blog goes live. [edit - to include link] Paul has done as he offered - his article is at: Thanks for sharing this :)
Apr 25 '12 at 02:06 PM
Cyborg
no problem.
Apr 26 '12 at 06:49 AM
Fatherjack ♦♦
(comments are locked)
|
|
Does DBCC CHECKDB, or indeed DBCC CHECKCATALOG, return errors when run on the live database? If not then this seems to possibly be an issue that is particular to Master. I can reproduce exactly what you describe but a check on the live database runs with no indication of corruption or inconsistency. I guess the acid test would be to get a test server and actually restore Master and then DBCC CHECKDB and confirm if there are errors.
(comments are locked)
|
|
Coincidentally I have a very similar setup for validating backups and am having the same problem (i.e. copying to a remote server and restoring all backups from production servers as "TestDB" and then running a check of the TestDB. I only have this failing on master databases from servers with a different patch level then the server I am doing the validation on - all other databases check with no problems. In my case, if I don't find a solution to this shortly, I will just add an exclusion to the verification process to skip the specific master databases that are causing issues. If anybody has other recommendations, that would be great. You say different patch level. Do you mean that the archive (target) server is 'newer' i.e has a higher patch level than the source systems? I could understand it if your sources were on a higher patch level than the target, but not the other way.
Aug 06 '10 at 12:29 AM
WilliamD
Nice to hear someone else is doing this :-) I'd recommend it to any dba - just because you're taking backups, doesn't mean you'll be able to restore them when disaster strikes. Quite often I've found that copies taken off-server have been corrupted, or there is some condition on the source server that produces corrupt backups in the first place, or someone (against policy!) has taken a backup without setting the 'copy only' flag, so without that backup file the chain is broken. Our process also checks that the backups arrive when they should - proof that the backup was taken and that the process of replicating the file is working. All good protection against Sod's Law :-)
Aug 06 '10 at 01:24 AM
dave 4
William - is the instance you're restoring to ss08r2?
Aug 06 '10 at 01:27 AM
dave 4
@dave, I am not on R2 (yet), but we backup our servers and restore them to a sandbox system on a daily basis to test the restore, a DBCC is also performed on both the source and target to make sure things are ok. I have all systems on the same patch level, so that may be something for you to check.
Aug 06 '10 at 01:51 AM
WilliamD
(comments are locked)
|
|
Did you upgrade from SQL 2000 to SQL 2008R2? If so, then the checkdb you did on SQL 2000 didn't perform as many checks as SQL2005 and above do, in particular a It certainly doesn't look good, as far as I can see - see MSDN Do you have a backup of master that was successful from earlier (a few days, weeks, months ago)? That may be your only chance of fixing things. Paul Randal would be the person to ask here - he wrote DBCC. I don't think he is on here regularly, but he is on SSC's forums. Maybe posting there under the corruption forum would get some help too (is under SQL 2005, so mention your version when/if you post). Thanks for the feedback and the research William :-) The source master database isn't corrupt (well, according to dbcc checkdb anyway!) - only the restored copy gives problems. No, I believe the source database was upgraded from 2005 to 2008 (before my time), then from 2008 to r2 yesterday. The target was a clean install of SS08R2 in May after removal of the Nov '09 CTP. Dave.
Aug 03 '10 at 03:31 AM
dave 4
Strange, I just did what you mentioned (albeit on SQL 2008), and successfully restored 'master' as 'test' without a problem. I would still not be happy about a backup that doesn't restore. Can you try creating a test database with a table with a few records, back that up and try restoring to the target machine? Could there be some I/O strangeness happening on your VM? Are there any messages in Error Logs (System or SQL Server) to indicate that something bad is happening?
Aug 03 '10 at 03:49 AM
WilliamD
Yeah, we didn't have any problems 08->08, and no, I'm not happy either! All our database backups on all our servers/instances are restored automatically on the server the backups are archived to. The master database backups are the only ones we have problems with, and some of them are quite big :-) That includes 2008 backups restoring on a 2008r2 instance and (as here) r2->r2. Dave.
Aug 03 '10 at 06:44 AM
dave 4
@dave, have I understood correctly that the problems are occuring on more than one master database that you are trying to restore? If so, that sounds really strange. Will an of those 'broken' backups restore on another R2 installation (test system or something)?
Aug 03 '10 at 01:38 PM
WilliamD
Nope, we have more than one archive server, each of which tests the backups it receives from the servers it monitors, and we have the same problem on all archive servers for all monitored servers, but only for the master databases. I It looks like a systemic problem with restoring master databases onto R2... hopefully it works if the master database is restored as the master database!
Aug 04 '10 at 05:31 AM
dave 4
(comments are locked)
|



The script was corrupted on pasting. It should be:
restore database test from disk = 'E:\RemoteBackups\dogato\database\CanadaXX\master\master_backup_2010_08_03_011007_2187274.bak' with norecovery, move 'master' to 'E:\ABCDAp~1\autorestore\f1', move 'mastlog' to 'E:\ABCDAp~1\autorestore\f2', replace
restore database test with recovery