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?
results from "dbcc checkdb ('test') with no_infomsgs":
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:
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).
answered Aug 03, 2010 at 03:16 AM
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.
answered Aug 05, 2010 at 02:32 PM
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.
answered Apr 25, 2012 at 09:39 AM