locked
[Forum FAQ] How do I restore the CDC enabled database backups (FULL + DIFF) while maintaining the CDC integrity RRS feed

  • General discussion

  • Question
    Background: When restoring Full + DIFF backups of a Change Data Capture (CDC) enabled database to a different SQL server, the CDC integrity is not being maintained. We did RESTORE the databases with the KEEP_CDC option.
    How do I successfully restore the CDC enabled database backups (FULL + DIFF) while maintaining the CDC integrity?

    Answer
    When restoring a CDC enabled database on a different machine that is running SQL Server, besides using use the KEEP_CDC option to retain all the CDC metadata, you also need to add Capture and Cleanup jobs. 

    In addition, as you want to restore FULL + DIFF backups of a CDC enabled database, you need to note that the KEEP_CDC and NoRecovery options are incompatible. Use the KEEP_CDC option only when you are completing the recovery. I made a test to display the whole process that how to restore the CDC enabled database backups (FULL + DIFF) on a different machine.

    1. Create a database named ’CDCTest’ in SQL Server 2012, then enable the CDC feature and take full+ differential backups of the database.
      -- Create database CDCTest
      CREATE DATABASE [CDCTest] ON  PRIMARY
      ( NAME = N'CDCTest ', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ CDCTest.mdf' , SIZE = 5120KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
       LOG ON
      ( NAME = N'CDCTest _log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ CDCTest _log.LDF' , SIZE = 3840KB ,
      MAXSIZE = 2048GB , FILEGROWTH = 10%)
      GO
      use CDCTest;
      go
      -- creating table
      create table Customer
      (
      custID int constraint PK_Employee primary key Identity(1,1)
      ,custName varchar(20)
      )
      --Enabling CDC on CDCTest database
      USE CDCTest
      GO
      EXEC sys.sp_cdc_enable_db
      --Enabling CDC on Customer table
      USE CDCTest
      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('Mike'),('Linda')
      -- Querying CDC table to get the changes
      select * from cdc.dbo_customer_CT
      --Taking full database backup
      backup database CDCTest to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\ CDCTest.bak'
      insert into Customer values('David'),('Jane')
      -- Querying CDC table to get the changes
      select * from cdc.dbo_customer_CT
      --Taking differential database backup
      BACKUP DATABASE CDCTest TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\ CDCTestdif.bak' WITH DIFFERENTIAL
      GO
    2. Restore Full backup of the ‘CDCTest’ database with using KEEP_CDC option in a different server that is running SQL Server 2014.
      Use master
      Go
      --restore full database backup
      restore database CDCTest from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\CDCTest.bak' with keep_cdc
    3. Restore Diff backup of the ‘CDCTest’ database.
      Restore Database CDCTest From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\CDCTest.bak'
          With Move 'CDCTest' To 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\CDCTest.mdf',
              Move 'CDCTest _log' To 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\CDCTest _log.LDF',
              Replace,
              NoRecovery;
      Go
      --restore differential database backup
      restore database CDCTest from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\CDCTestdif.bak' with keep_cdc
    4. Add the Capture and Cleanup jobs in the CDCTest database.
      --add the Capture and Cleanup jobs
      Use CDCTest
      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

    Reference
    Track Data Changes (SQL Server)
    Restoring a SQL Server database that uses Change Data Capture

    Applies to
    SQL Server 2014
    SQL Server 2012
    SQL Server 2008 R2
    SQL Server 2008


    Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.

    Wednesday, January 21, 2015 7:35 AM