locked
Can CDC enabled database DIFF backups be RESTORED while maintaining the CDC integrity? RRS feed

  • Question

  • We tried restoring FULL + subsequent DIFF backups of a CDC enabled database to a different SQL server and found that the CDC integrity is not being maintained. We did RESTORE the databases with the KEEP_CDC option.

    Can someone please guide us on how to successfully restore the CDC enabled database backups(FULL + DIFF) while maintaining the CDC integrity?

    Note: SQL Server Version: SQL Server 2012 SP1 CU2

    Monday, October 6, 2014 7:47 PM

Answers

  • Hi YoungBreeze,

    Based on your description, I make a test on my computer.

    Firstly, I create a database named ‘sqldbpool’ , then enable the Change Data Capture (CDC) feature and take full+ differential backups of the database. Below are the scripts I used.

    -- Create database sqldbpool
    CREATE DATABASE [sqldbpool] ON  PRIMARY 
    ( NAME = N'SQLDBPool', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool.mdf' , SIZE = 5120KB , 
    MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'SQLDBPool_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\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 
    backup database sqldbpool to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak'
    
    insert into Customer values('111'),('222')
    -- Querying CDC table to get the changes
    select * from cdc.dbo_customer_CT
    --Taking differential database backup
    BACKUP DATABASE sqldbpool TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpooldif.bak' WITH DIFFERENTIAL
    GO

    Secondly, I restore the ‘sqldbpool’ database  in a different SQL Server instance with the following scripts. After the restoration, everything works as expected and the database maintains the CDC integrity.

    Use master
    Go
    --restore full database backup
    restore database sqldbpool from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak' with keep_cdc 
    
    Restore Database sqldbpool From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak'
        With Move 'SQLDBPool' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool.mdf',
            Move 'SQLDBPool_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool_log.LDF',
            Replace,
            NoRecovery;
    Go
    
    --restore differential database backup
    restore database sqldbpool from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpooldif.bak' with keep_cdc
    
    --add the Capture and Cleanup jobs
    Use sqldbpool
    exec sys.sp_cdc_add_job 'capture'
    GO
    exec sys.sp_cdc_add_job 'cleanup'
    GO
    
    insert into Customer values('TEST'),('TEST1')
    -- Querying CDC table to get the changes
    select * from cdc.dbo_customer_CT

    When we restore a CDC enabled database, we need to note that the CDC feature is available only on the Enterprise, Developer, and Evaluation editions of SQL Server. And we need to add the Capture and Cleanup agent jobs after restoring the database.

    For more details about restoring a CDC enabled database in SQL Server, you can review the following similar articles.
    Restoring a SQL Server database that uses Change Data Capture: http://www.mssqltips.com/sqlservertip/2421/restoring-a-sql-server-database-that-uses-change-data-capture/
    CDC Interoperability with Mirroring and Recovery: http://www.sqlsoldier.com/wp/sqlserver/cdcinteroperabilitywithmirroringandrecovery


    Thanks,
    Lydia Zhang


    Tuesday, October 7, 2014 7:53 AM

All replies

  • Hi YoungBreeze,

    Based on your description, I make a test on my computer.

    Firstly, I create a database named ‘sqldbpool’ , then enable the Change Data Capture (CDC) feature and take full+ differential backups of the database. Below are the scripts I used.

    -- Create database sqldbpool
    CREATE DATABASE [sqldbpool] ON  PRIMARY 
    ( NAME = N'SQLDBPool', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool.mdf' , SIZE = 5120KB , 
    MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'SQLDBPool_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\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 
    backup database sqldbpool to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak'
    
    insert into Customer values('111'),('222')
    -- Querying CDC table to get the changes
    select * from cdc.dbo_customer_CT
    --Taking differential database backup
    BACKUP DATABASE sqldbpool TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpooldif.bak' WITH DIFFERENTIAL
    GO

    Secondly, I restore the ‘sqldbpool’ database  in a different SQL Server instance with the following scripts. After the restoration, everything works as expected and the database maintains the CDC integrity.

    Use master
    Go
    --restore full database backup
    restore database sqldbpool from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak' with keep_cdc 
    
    Restore Database sqldbpool From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpool.bak'
        With Move 'SQLDBPool' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool.mdf',
            Move 'SQLDBPool_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLDBPool_log.LDF',
            Replace,
            NoRecovery;
    Go
    
    --restore differential database backup
    restore database sqldbpool from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sqldbpooldif.bak' with keep_cdc
    
    --add the Capture and Cleanup jobs
    Use sqldbpool
    exec sys.sp_cdc_add_job 'capture'
    GO
    exec sys.sp_cdc_add_job 'cleanup'
    GO
    
    insert into Customer values('TEST'),('TEST1')
    -- Querying CDC table to get the changes
    select * from cdc.dbo_customer_CT

    When we restore a CDC enabled database, we need to note that the CDC feature is available only on the Enterprise, Developer, and Evaluation editions of SQL Server. And we need to add the Capture and Cleanup agent jobs after restoring the database.

    For more details about restoring a CDC enabled database in SQL Server, you can review the following similar articles.
    Restoring a SQL Server database that uses Change Data Capture: http://www.mssqltips.com/sqlservertip/2421/restoring-a-sql-server-database-that-uses-change-data-capture/
    CDC Interoperability with Mirroring and Recovery: http://www.sqlsoldier.com/wp/sqlserver/cdcinteroperabilitywithmirroringandrecovery


    Thanks,
    Lydia Zhang


    Tuesday, October 7, 2014 7:53 AM
  • Thanks Lydia. Will do as per the steps suggested and let you know if it works.
    Tuesday, October 7, 2014 10:19 PM