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
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.
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.