CDC is a feature available in and above SQL Server 2008 enterprise edition and helps to audit/change tracking of database changes like inserts, updates, and deletes on a table. In this blog, we will look into restoring a CDC enabled database.

We will look into the steps to be followed for restoring a CDC enabled database along with audit data. We need to use KEEP_CDC to restore Database along with audit data as shown below:

RESTORE DATABASE <DB NAME> FROM DISK = '<BAK FILE PATH>' with KEEP_CDC
After restoring, ensure to run capture and clean jobs using below commands:
  1. exec sys.sp_cdc_add_job 'capture'
  2. GO
  3. exec sys.sp_cdc_add_job 'cleanup'
  4. GO
Some times, you may get the below error while restoring DB:
To resolve it, ensure sa user is dbowner and database is not in use using below commands:
  1. USE <DB NAME>
  2. EXEC sp_changedbowner 'sa'
  3. USE master
  4. ALTER DATABASE <DB NAME>
  5. SET SINGLE_USER;
  6. RESTORE DATABASE <DB NAME> FROM DISK = '<BAK FILE PATH>' with KEEP_CDC
  7. ALTER DATABASE <DB NAME>
  8. SET MULTI_USER
I am ending things here, hope this blog was helpful for all.