Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restoring a SQL Server database that uses Change Data Capture


By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | More > Change Data Capture


Share your SQL Server knowledge and make some money too!


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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 30, 2013 - 12:06:26 PM - SHINOJ P Back To Top

Great Jugal..!

It's very useful article, because SSMS backup and restore wizards doesn't provide the switches like KEEP_CDC.

Thanks,

Shinoj


Friday, October 19, 2012 - 10:07:01 AM - VG Back To Top

Thank you very much!  This was extremely helpful in testing our SQL 08 to SQL 12 upgrade where we are rebuilding servers and restoring databases rather than in-place upgrade.  Very, very helpful!!


Monday, September 10, 2012 - 3:32:33 PM - Riti Back To Top

Thanks soo much for the clear resolution of the issue while moving the database from one server to another. Helped me a lot


Friday, June 24, 2011 - 5:38:02 PM - Cardy Back To Top

Apologies .. fingers not working tonight ...

Nice & neat example to illustrate this DB feature for those that havent ever used it.

Just wanted to add the importance of using the "keep_cdc" switch .. because this option is not part of the Restore DB Dialog of SSMS in the same way the with KEEP_Replication switch is.

Consequently you have to Restore a CDC enabled DB via T-sql (argh- just like the good old days)

I've seen a few other grumbles on Connect about this ommission from SSMS - but no signs of any plans to add it.


Friday, June 24, 2011 - 5:35:45 PM - Cardy Back To Top

Nice & neat example to illustrate this DB feature  for thise that havent ever used it.

Just wanted to add the importance of using the "keep_cdc" switch .. because this option is not in the Restore DB Dialog of SSMS in the same way the with KEEP_Replication exists.

Consequently you have to Restore a CDC enabled DB via T-sql (arggh just like the good old days)

I've seen a few other grumbles on Cnnect about why this feature isnt there  but no signs of any lans to add it.


Learn more about SQL Server tools