Problem
When restoring a database that uses Change Data Capture (CDC), restoring a backup works differently depending on where the database is restored. In this tip we take a look at different scenarios when restoring a database when CDC is enabled.
Solution
When restoring a CDC enabled database you may have these scenarios:
- Restore the CDC enabled database on the same SQL instance by overwriting existing database
- Restore the CDC enabled database with the different name on same SQL instance
- Restore the CDC enabled database on a different SQL instance
Sample CDC Database Setup
The below script creates a database and enables CDC.
— Create database sqldbpool
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N’SQLDBPool’, FILENAME = N’L:\SQLDBPool.mdf’ , SIZE = 5120KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’SQLDBPool_log’, FILENAME = N’F:\SQLDBPool_log.LDF’ , SIZE = 3840KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
use sqldbpool;
go
— creating table
create table Customer
(
custID int constraint PK_Employee primary key Identity(1,1)
,custName varchar(20)
)
–Enabling CDC on SQLDBPool database
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_db
–Enabling CDC on Customer table
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name = N’Customer’,
@role_name = NULL
GO
–Inserting values in customer table
insert into Customer values(‘jugal’),(‘shah’)
— Querying CDC table to get the changes
select * from cdc.dbo_customer_CT
–Taking full database backup which I am going to restore for all the above scenarios
backup database sqldbpool to disk = ‘l:\sqldbpool.bak’
Restore to same instance with same database name
In the scenario of restoring a database on the same instance by overwriting the existing database, CDC remains enabled and all related metadata is persisted. CDC will start working since the Capture and Cleanup jobs are already on the box.
–Restoring on the same SQL Instance with the Same database name
restore database sqldbpool from disk = ‘l:\sqldbpool.bak’ with replace
Restore to same instance with different database name or Restore to a different instance
In these scenarios CDC will be disabled and all the related metadata is deleted from the database. To not loose this information you must use the Keep_CDC option with the database restore statement as shown below.
–Restoring on the same instance with the different database name
Restore Database sqldbpool_1
from disk = ‘l:\sqldbpool.bak’
with move ‘SQLDBPool’ to ‘L:\SQLDBPool1.mdf’,
move ‘SQLDBPool_log’ to ‘F:\SQLDBPool_log1.LDF’,keep_cdc
–Restoring on a different instance with same database name
restore database sqldbpool from disk = ‘l:\sqldbpool.bak’ with keep_cdc
In addition, you need to add the Capture and Cleanup jobs using the following commands in the appropriate database.
Use sqldbpool_1
exec sys.sp_cdc_add_job ‘capture’
GO
exec sys.sp_cdc_add_job ‘cleanup’
GO
Next Steps
- Try all the above solutions by creating different scenarios for a CDC enabled database
- Read these other CDC tips
Biography
Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
