November 18, 2016 at 6:34 pm
We had an interesting finding today when performing a disaster recovery restore test on the master database. I was able to reproduce this on two separate instances running SQL 2012 SP3 (11.0.6523.0). The master database passed DBCC CHECKDB without problems. However, if we back that database up then immediately restore it to the same server (with a different name) the restored database is consistently corrupt on page 1:10
Has anyone seen this before, and can we explain it?
-- 1) Perform CHECKDB on master database, this concludes with no findings
DBCC CHECKDB ('master') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS
-- NO Errors reported
-- 2) Backup master database
BACKUP DATABASE [master] TO DISK = N'M:\MSSQL\Backups\master_drt.bak' WITH NOFORMAT, INIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'master' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'master' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'M:\MSSQL\Backups\master_drt.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
-- 3) Restore master database as master_drt
USE [master]
RESTORE DATABASE [master_drt] FROM DISK = N'M:\MSSQL\Backups\master_drt.bak' WITH FILE = 1, MOVE N'master' TO N'M:\MSSQL\Data\master_drt.mdf', MOVE N'mastlog' TO N'L:\MSSQL\Logs\mast_drtlog.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
-- 4) Perform CHECKDB on restored master_drt database
DBCC CHECKDB ('master_drt') WITH NO_INFOMSGS, ALL_ERRORMSGS
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=26,depid=263,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=264,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=266,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=271,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=274,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=277,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=65538,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=65538,depsubid=103) 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=65538,depsubid=104) 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=65538,depsubid=105) 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=65538,depsubid=107) 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=65539,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=65539,depsubid=103) 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=65539,depsubid=107) was found in the system table sys.syssingleobjrefs (class=76).
CHECKDB found 0 allocation errors and 27 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 27 consistency errors in database 'master_drt'.
November 19, 2016 at 1:26 am
Sql_In_Seattle (11/18/2016)
However, if we back that database up then immediately restore it to the same server (with a different name) the restored database is consistently corrupt on page 1:10
Yup, because master has some special structures at the start that no user database has. CheckDB knows this, and ignores these when checking master. However when master is restored as a user database, it's considered invalid
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
November 21, 2016 at 4:56 pm
Gail:
Thanks for taking the time to explain this. I was unaware of this until now.
Andre
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply