asked
master backup validation by restore
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:** 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 **results from** "dbcc checkdb ('test') with no_infomsgs": > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=12,depid=0,depsubid=0) was found in the system table sys.syssingleobjrefs (class=12). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=1,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=3,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=4,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=5,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=259,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=260,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=261,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=262,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=100) was found in the system table sys.syssingleobjrefs (class=76). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=101) was found in the system table sys.syssingleobjrefs (class=76). > Msg 8992, Level 16, State 1, Line 1 > Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=102) was found in the system table sys.syssingleobjrefs (class=76). > CHECKDB found 0 allocation errors and 16 consistency errors not associated with any single object. > Msg 8906, Level 16, State 1, Line 1 > Page (1:10) in database ID 6 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'. > CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 99)' (object ID 99). > CHECKDB found 1 allocation errors and 16 consistency errors in database 'test'.
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
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 `DBCC CHECKCATALOG` is run for SQL 2005 and above that would catch this problem. It certainly doesn't look good, as far as I can see - [see MSDN][1] 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][2] would get some help too (is under SQL 2005, so mention your version when/if you post). [1]:
http://msdn.microsoft.com/en-us/library/aa337359.aspx [2]:
http://www.sqlservercentral.com/Forums/Forum266-1.aspx
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.
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?
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.
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!
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.
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 :-)
@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.
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.
OK, I got the answer from the person who knows. Paul Randall (he wrote the current version of CHECKDB) confirms (via Twitter): - Master database cannot be restored as a user database. - If you do, a CHECKDB on the restored version will ALWAYS fail. - This is expected behaviour. 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:
http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/master-database-corrupt-142934 ![alt text][1] [1]:
http://ask.sqlservercentral.com/storage/temp/181-FJ_PR_MasterRestore_CheckDB.png
1 Person is following this question.
