Resolve Issue to Restore CDC Enabled Database

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