In MS SQL Server, Change data capture(CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
For more information how to create CDC you can go through below link:
Restoring a database that uses Change Data Capture (CDC) works differently depending on where the database is going to be restored. Within a moment we will take a look at different scenarios to restore a database when CDC is enabled.
We can have the following scenarios :
- Restore the CDC enabled database on same SQL Server instance by overwriting existing database
- Restore the CDC enabled database with a different name on same SQL Server instance
- Restore the CDC enabled database on a different SQL instance.
RESTORE TO SAME INSTANCE WITH SAME DATABASE NAME
In this scenario CDC remains enabled and all related
metadata will be persisted. CDC will start working as the Capture and
Cleanup jobs are already exists.
You can run the below script to restore database:
--Restoring on the same SQL Instance with the Same database name
restore
database SqlCDC from disk = 'E:\SqlCDC .bak' with replace
RESTORE TO SAME SQL SERVER INSTANCE WITH DIFFERENT DATABASE NAME
OR
RESTORE TO A DIFFERENT INSTANCE WITH SAME DATABASE
NAME/DIFFERENT NAME
In these particular scenario you have to add one option in script during restore. i.e. Keep_CDC
You can run the below script to restore database:
restore database SqlCDC from disk = 'E:\SqlCDC .bak' with keep_cdc
After this you will also have to add the Capture and Cleanup jobs using the following system store procedures in the appropriate database.
Use SqlCDC
GO
exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO
Thanks for reading this blog!!!!
No comments:
Post a Comment