x

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'.
more ▼

asked Aug 03 '10 at 02:35 AM in Default

dave 4 gravatar image

dave 4
104 2 2 4

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
Aug 03 '10 at 02:40 AM dave 4
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Apr 25 '12 at 02:01 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.1k 72 77 107

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)
10|1200 characters needed characters left

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

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).

[2]: http://www.sqlservercentral.com/Forums/Forum266-1.aspx
more ▼

answered Aug 03 '10 at 03:16 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

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)
10|1200 characters needed characters left

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.
more ▼

answered Aug 05 '10 at 02:32 PM

TDrudge gravatar image

TDrudge
11

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)
10|1200 characters needed characters left

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.
more ▼

answered Apr 25 '12 at 09:39 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.1k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x561
x101
x47
x11

asked: Aug 03 '10 at 02:35 AM

Seen: 2489 times

Last Updated: Apr 27 '12 at 08:04 AM