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:
- exec sys.sp_cdc_add_job 'capture'
- GO
- exec sys.sp_cdc_add_job 'cleanup'
- 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:
- USE <DB NAME>
- EXEC sp_changedbowner 'sa'
- USE master
- ALTER DATABASE <DB NAME>
- SET SINGLE_USER;
- RESTORE DATABASE <DB NAME> FROM DISK = '<BAK FILE PATH>' with KEEP_CDC
- ALTER DATABASE <DB NAME>
- SET MULTI_USER
I am ending things here, hope this blog was helpful for all.