February 23, 2015 at 11:48 am
I've created an automated DR test to test my backups. It restores databases to a test/sandbox instance of SQL. After each DB is restored, I run CHECKDB, then drop the DB. The [master] database is giving me problems, though. The restore completes successfully, but CHECKDB fails. Here's an example:
RESTORE DATABASE DR_master
FROM DISK = '\\UNC path\master.bak'
WITH
MOVE 'master' TO 'D:\SQL Data\DR_master.mdf',
MOVE 'mastlog' TO 'D:\SQL Data\DR_mastlog.ldf',
REPLACE, RECOVERY
DBCC CHECKDB('DR_master')
I've tried this on SQL 2008 R2 w/ SP3 sandbox, and also on SQL 2012 w/ SP2. (Error messages vary by version.)
Let's get the obvious out of the way: [master] is a system database. You knew that, right? I've restored [master] before and there are rules that have to be followed to do it correctly. But this is a different situation (in my mind, anyway). I'm restoring [master] under a different DB name--as if it was a user DB.
Is there another/better way to verify my backups of [master] are "good"? Or am I just wasting my time?
February 24, 2015 at 2:00 pm
Did you try restoring using single-user mode?
Dave Mason (2/23/2015)
I've created an automated DR test to test my backups. It restores databases to a test/sandbox instance of SQL. After each DB is restored, I run CHECKDB, then drop the DB. The [master] database is giving me problems, though. The restore completes successfully, but CHECKDB fails. Here's an example:
RESTORE DATABASE DR_masterFROM DISK = '\\UNC path\master.bak'
WITH
MOVE 'master' TO 'D:\SQL Data\DR_master.mdf',
MOVE 'mastlog' TO 'D:\SQL Data\DR_mastlog.ldf',
REPLACE, RECOVERY
DBCC CHECKDB('DR_master')
I've tried this on SQL 2008 R2 w/ SP3 sandbox, and also on SQL 2012 w/ SP2. (Error messages vary by version.)
Let's get the obvious out of the way: [master] is a system database. You knew that, right? I've restored [master] before and there are rules that have to be followed to do it correctly. But this is a different situation (in my mind, anyway). I'm restoring [master] under a different DB name--as if it was a user DB.
Is there another/better way to verify my backups of [master] are "good"? Or am I just wasting my time?
February 24, 2015 at 2:46 pm
CooLDBA (2/24/2015)
Did you try restoring using single-user mode?
I can restore the db, set it to SINGLE_USER mode, and then run DBCC CHECKDB. But there are still allocation/consistency errors in the db. I don't understand the point of putting the db in SINGLE_USER mode. Am I missing something?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply