Change Data Capture, or CDC, does not require the KEEP_CDC keyword when restoring a database to the original server. When restoring a database to a server other than the original server, you can use the KEEP_CDC keyword to specify that the change data capture data is retained in the database. This is by design, and is clearly stated in BOL here under the section entitled "Restoring or Attaching a Database Enabled for Change Data Capture"
This code shows CDC in action both before and after a RESTORE DATABASE ... WITH NORECOVERY
CREATE DATABASE CDC_RESTORE_TEST
ON (NAME='CDC_RESTORE_TEST', FILENAME = 'C:\SQLServer\Data\CDC_RESTORE_TEST.mdf', SIZE = 10MB, FILEGROWTH = 1MB)
LOG ON (NAME = 'CDC_RESTORE_TEST_LOG', FILENAME = 'C:\SQLServer\Logs\CDC_RESTORE_TEST.ldf', SIZE = 1MB, FILEGROWTH = 1MB);
GO
ALTER DATABASE CDC_RESTORE_TEST
SET RECOVERY FULL;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_enable_db;
GO
CREATE TABLE Test
(
ID INT NOT NULL CONSTRAINT PK_Test PRIMARY KEY CLUSTERED IDENTITY(1,1)
, DATA NVARCHAR(255) NOT NULL
);
EXEC sys.sp_cdc_enable_table 'dbo', 'Test', 'dbo_Test', 1, NULL;
GO
ALTER TABLE Test ADD Data1 NVARCHAR(255) NOT NULL;
WAITFOR DELAY '00:00:15'; /* wait for event to be captured */
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';
USE Master;
GO
BACKUP DATABASE CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST.BAK';
BACKUP LOG CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.BAK';
GO
ALTER DATABASE CDC_RESTORE_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE CDC_RESTORE_TEST;
GO
RESTORE DATABASE CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY;
RESTORE LOG CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';
Restoring the same backup onto the same server as a new database name appears to retain CDC:
USE Master;
GO
RESTORE DATABASE CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY
, MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
, MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
RESTORE LOG CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY
, MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
, MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';
Once a restore starts, the target database is overwritten and cannot be recovered back to it's original state (sans restore). Consider restoring to a different database name and, if successful, drop the other database and rename the restored one to the desired name.
Best Answer
Here are a few avenues I would investigate. Don't do all of these (some of them are different techniques to accomplish the same purpose), but are worth considering:
1. Examine the SQL Error Log directly
Browse directly to the folder containing the SQL error logs and load the most recent
ERRORLOGinto notepad to get more detail on why the SQL instance will not start. Perhaps you will find the problem isn't with the master database at all.2. Try to start the instance in single-user mode
Here is a full list of startup options for SQL server, including
-m(single user mode) and-f(minimal configuration mode). Other options allow you to specify the path for the master database, if that is the problem.If you are able to get the instance started, follow the steps in the MSDN article you linked for restoring the master database, or this detailed walkthrough by Thomas LaRock.
If another application always grabs the single user connection before you can, first disable the SQL Agent so it isn't starting. Second, see the ideas on this question for using the
-m"Application Name"parameter to specify the application name.3. Restore
masterto another instance and copy its filesI've only found one other mention of this undocumented technique, but I used it successfully this past weekend, so it might be worth a try.
If you can't start the instance in single-user mode, but you have another SQL instance running the exact same release and build, try restoring the last known good master database backup from your dead server to the other instance:
master_please_god_let_this_work),WITH MOVEso you don't overwritemasteron your good serverWITH NORECOVERY. Not sure this is necessary, but made me feel better that I knew the other server wasn't going to alter anything in the restored masterALTER DATABASE [master_please_god_let_this_work] SET OFFLINEmaster.mdfandmastlog.ldffiles as necessary to replace the bad master files with your restored versionsmaster.4. Rebuild the system databases
If you don't have another instance running the same version, or if you're not comfortable with using the undocumented procedure listed in #3, or if you don't have backups of
master(why do you not have backups??), you can rebuild the SQL system databases from the original install disk:When this is complete, you can follow the steps linked previously to restore
masterfrom your last good backup. You will also need to restore a recent backup ofmsdbto keep all your jobs, job schedule, and job history.5. Restore all USER databases to a new (or existing) SQL instance
If you've got another existing instance already running (proper SQL version, enough disk space), I would probably start database restores from the most recent backups while I'm working on the other troubleshooting steps above, just in case I need them.
If your new (or reinstalled) instance has access to the same disk, it is much faster to simply attach them as new databases:
6. Re-do any changes to
masterOnce you successfully restore
master(via any of the above techniques), you need to investigate any changes that might have been lost, if they were made after the backup you just restored:There is no magic way to find these, you'll have to go back to your own company's documentation trail for these kinds of changes, if you have one.