When you need to restore a database to a different SQL Server instance, or perhaps to the same instance with a different database name, it’s a pretty standard affair. Just backup the database, then restore it in the location you want. Easy as that, you’ve got the same database restored with all the data it had before.
That’s not quite the case if your database has Change Data Capture (CDC) enabled. If you do a standard restore of a database with CDC enabled to a different SQL Server instance or same instance but different database name, you’ll end up losing all your CDC captured data and CDC will be disabled. Not ideal really, especially since the CDC tables wouldn’t typically be a table you would check to see if all the data you needed has been restored.
There is a nice little argument to add to your restore commands for CDC enabled databases – KEEP_CDC. Here I’ll demonstrate restoring a database with and without the KEEP_CDC argument.
Setup a CDC database
Firstly, lets setup a database with CDC enabled. I create the database called CDCDemo, then enable cdc for the database executing the stored procedure sp_cdc_enable_db. Then check that it has indeed been enabled.
-- Create a database
CREATE DATABASE CDCDemo
ON PRIMARY
( NAME = N'CDCDemo', FILENAME = N'F:\MSSQL\Data\CDCDemo.mdf' , SIZE = 5MB)
LOG ON
( NAME = N'CDCDemo_log', FILENAME = N'F:\MSSQL\Log\CDCDemo_log.ldf' , SIZE = 5MB);
GO
USE CDCDemo;
GO
-- Enable cdc for the database
EXECUTE sp_cdc_enable_db;
GO
-- Check that CDC is enabled on the database
SELECT [name], is_cdc_enabled
FROM sys.databases
WHERE [name] = N'CDCDemo';
Now that the database has CDC enabled, I create a basic table with an ID and a nvarchar column, then enable CDC for that table.
-- Create table to demonstrate with
CREATE TABLE dbo.person
(
person_id INT NOT NULL PRIMARY KEY,
person_name NVARCHAR(100)
)
-- Enable a table for for cdc
-- Show the table in Object Explorer and the jobs
EXECUTE sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'person',
@role_name = NULL
As part of enabling that table for CDC, it creates a table to track those changes, in this case called cdc.dbo_person_CT, and two jobs, called cdc.CDCDemo_capture and cdc.CDCDemo_cleanup. The capture job runs constantly and frequently checks for new changes to the CDC enabled tables in that database. The cleanup job simply cleans up the old records in the change tables, like cdc.dbo_person_CT.
Now we’re all ready for capturing some data! Lets try it out.
-- Insert some rows into the table
INSERT INTO dbo.person
VALUES
(1, N'Jacinta'),
(2, N'Boris'),
(3, N'Donald'),
(4, N'Scott');
-- Check the values in the table
SELECT * FROM dbo.person;
Next lets check if those records that were inserted have been captured
See those 4 records that were inserted have been captured in the CT table. Cool. Now lets make some more changes to the data.
-- Run a delete and an update on the table
DELETE FROM dbo.person WHERE person_id = 4
UPDATE dbo.person SET person_name = 'Joe' WHERE person_id = 3
-- Check the values in the table
SELECT * FROM dbo.person
Lets check the CT table again
Now we can see the delete of ID 4 and the update of ID 3. The column to look for is __$operation. Operation number 1 is for delete, 2 for insert, 3 for old values of an update, 4 for the new values of an update.
Setting up CDC is as easy as that. But the reason I’m writing this is to show how to restore a backup of a database with CDC enabled…
Restore the database backup
First I’ll take a full backup of the database
BACKUP DATABASE [CDCDemo]
TO DISK = N'F:\MSSQL\Backup\CDCDemo_FULL.bak'
WITH NOFORMAT, INIT, SKIP, CHECKSUM
GO
Nothing special to do there. It’s when writing the restore command which is important. The KEEP_CDC is the key argument to include. I’ll first show restoring without it to a database called CDCDemo_New_NoCDC:
-- Restore the full backup with no KEEP_CDC
USE [master]
RESTORE DATABASE [CDCDemo_New_NoCDC]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_FULL.bak'
WITH
MOVE N'CDCDemo' TO N'F:\MSSQL\Data\CDCDemo_New_NoCDC.mdf',
MOVE N'CDCDemo_log' TO N'F:\MSSQL\Log\CDCDemo_New_NoCDC_log.ldf',
NOUNLOAD, REPLACE
GO
If I then run the same check as I did before to see if CDC is enabled, it shows that the new restored database has CDC disabled!
And the cdc.dbo_person_CT table no longer exists
So if you did this on one of your production databases with CDC enabled, you would lose all your CDC data! So now I’ll show restoring with the KEEP_CDC argument by restoring to another database called CDCDemo_New_CDC.
-- Restore the full backup with KEEP_CDC
USE [master]
RESTORE DATABASE [CDCDemo_New_CDC]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_FULL.bak'
WITH
MOVE N'CDCDemo' TO N'F:\MSSQL\Data\CDCDemo_New_CDC.mdf',
MOVE N'CDCDemo_log' TO N'F:\MSSQL\Log\CDCDemo_New_CDC_log.ldf',
NOUNLOAD, REPLACE, KEEP_CDC
GO
The same checks show this time that CDC is enabled and the CT tables with the data are still there. That’s better!
But it’s not completely done. If you go check the jobs, you’ll notice that the CDC capture and cleanup jobs have not been automatically created for the restored database. So they need to be created again. There is a nice little command that can be executed for each job:
USE CDCDemo_New_CDC;
GO
EXECUTE sys.sp_cdc_add_job 'capture';
GO
EXECUTE sys.sp_cdc_add_job 'cleanup';
GO
What about Diff and Log Backup Restores?
Is there anything different when restoring differential or log backups? Yes! When restoring diff and log backups, you would restore the backups with norecovery, until finally you want to recover the database, you would run the final restore command with recovery. The KEEP_CDC argument can only be run on the restore command with recovery. It isn’t allowed with norecovery. If you feel rebellious and try it out with norecovery anyway, SQL Server will spit an error at you. Let’s go through it.
Going back to the original CDCDemo, insert another row.
-- Insert another row into the table
INSERT INTO dbo.person
VALUES
(5, N'Vladimir');
-- Check the values in the table
SELECT * FROM dbo.person;
And… yep, it’s been captured.
So lets take a differential backup.
BACKUP DATABASE [CDCDemo]
TO DISK = N'F:\MSSQL\Backup\CDCDemo_DIFF.bak'
WITH NOFORMAT, INIT, SKIP, CHECKSUM, DIFFERENTIAL
GO
Then restore the original full backup, with norecovery and keep_cdc.
It says: Option 'norecovery' conflicts with options(s) 'keep_cdc'. Remove the conflicting option and reissue the statement.
To be fair, it’s a very helpful message. It says norecovery and keep_cdc cannot be used at the same time. So lets do as it says and omit the keep_cdc argument from the full restore, and then restore the differential with recovery and keep_cdc.
-- Restore the full and diff backups with KEEP_CDC
USE [master]
RESTORE DATABASE [CDCDemo_New_CDC_2]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_FULL.bak'
WITH
MOVE N'CDCDemo' TO N'F:\MSSQL\Data\CDCDemo_New_CDC_2.mdf',
MOVE N'CDCDemo_log' TO N'F:\MSSQL\Log\CDCDemo_New_CDC_2_log.ldf',
NOUNLOAD, REPLACE, NORECOVERY;
GO
RESTORE DATABASE [CDCDemo_New_CDC_2]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_DIFF.bak'
WITH NOUNLOAD, RECOVERY, KEEP_CDC;
GO
That worked and it kept CDC enabled!
In situations when you’re restoring a whole chain of log backups, or recovering a secondary log shipping database, often a technique used is to restore all the backups and then recover the database. That technique still works with CDC too. I’ll take a couple of log backups of the original database, then restore the full chain of full, diff and two log backups, all with norecovery. Then I’ll recover the database.
BACKUP LOG [CDCDemo]
TO DISK = N'F:\MSSQL\Backup\CDCDemo_LOG_01.trn'
WITH NOFORMAT, INIT, SKIP, CHECKSUM
GO
BACKUP LOG [CDCDemo]
TO DISK = N'F:\MSSQL\Backup\CDCDemo_LOG_02.trn'
WITH NOFORMAT, INIT, SKIP, CHECKSUM
GO
-- Restore the full and diff and log backups with KEEP_CDC
USE [master]
RESTORE DATABASE [CDCDemo_New_CDC_3]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_FULL.bak'
WITH
MOVE N'CDCDemo' TO N'F:\MSSQL\Data\CDCDemo_New_CDC.mdf',
MOVE N'CDCDemo_log' TO N'F:\MSSQL\Log\CDCDemo_New_CDC_log.ldf',
NOUNLOAD, REPLACE, NORECOVERY;
GO
RESTORE DATABASE [CDCDemo_New_CDC_3]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_DIFF.bak'
WITH NOUNLOAD, NORECOVERY;
GO
RESTORE LOG [CDCDemo_New_CDC_3]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_LOG_01.trn'
WITH NOUNLOAD, NORECOVERY;
GO
RESTORE LOG [CDCDemo_New_CDC_3]
FROM DISK = N'F:\MSSQL\Backup\CDCDemo_LOG_02.trn'
WITH NOUNLOAD, NORECOVERY;
GO
So far so good. Lets recover it.
-- Recover the database
RESTORE DATABASE [CDCDemo_New_CDC_3]
WITH RECOVERY, KEEP_CDC;
GO
Success! Of course, with these last examples, the CDC jobs would still need to be created.
So, the moral of that story is to not forget to include the KEEP_CDC argument when restoring your CDC enabled databases.
Chad Franklin 