Sql-server – Doing a point in time restore with CDC enabled; Possible

change-data-capturerestoresql serversql-server-2008

I discovered this week, the hard way, that the database restore options NORECOVERY and KEEP_CDC are mutually exclusive. So, this begs the question. How do you do a database restore, keeping CDC intact, with both full and log backups?

Doing research on MSDN etc. I cannot find any documentation on restoring a database with KEEP_CDC using any other option than a single full db restore with RECOVERY specified.

I was able to find one attempt that did the full and subsequent logs without the keep_cdc option waiting until the final log. Only then was the table brought online with the RECOVERY and KEEP_CDC option. The result was a corrupt CDC schema as demonstrated here.

If the intent is to KEEP_CDC on restore are you truley limited to a full backup only or is there a mechanism similar to the attempt above to keep it intact during multi-file restors on a server other than the original?

Best Answer

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';
Related Question