mssqltips logo

Restoring a SQL Server database that uses Change Data Capture

By:   |   Updated: 2011-06-24   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | More > Change Data Capture

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 Updated: 2011-06-24


get scripts

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, February 20, 2019 - 6:10:03 AM - Samir Vikhare Back To Top

Can we restore CDC by attaching MDF-LDF files


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.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools