How To Restore Change Data Capture – CDC Tables When Restoring a SQL Server Database

I have recently had the privilege of working with multiple clients who have been taking advantage of Change Data Capture “CDC”. Change Data Capture is a feature that utilizes SQL Server Agent to log inserts, updates, and deletes occurring in a table. It makes these data changes available to be consumed in a relational format. These changes are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables.

Change Data Capture is very often utilized for ETL projects or for data extracts into a data warehouse, data lake, etc.

Recently I came across a client that was needing to have their UAT environment refreshed with a copy of the production database. The client asked if their CDC tables would be restored in UAT. I assumed that yes, they would be restored since we would be restoring a full backup and overwriting the UAT database. By them asking the question, it made me curious if there was a reason they would be asking about it.

I decided to do a test in my lab. I created a new table, enabled CDC, turned on CDC for the new table and inserted some records. I confirmed that the inserts where showing in the CDC table. I did a full backup and restored that backup to a new database name. What I found was that the CDC table was not restored.

This lead me to doing some research and finding that in order to restore the CDC table, I must include WITH KEEP_CDC in the restore command. Example: RESTORE DATABASE DB_NAME FROM DISK = ‘C:\BACKUP\DB_NAME_FULL.BAK’ WITH KEEP_CDC

By including the WITH KEEP_CDC I was able to restore the client’s user database to include all their Change Data Capture tracked tables.

To illustrate this, I created a quick demo video. Check it out below and make sure to “Like and Subscribe”.