Sql-server – CDC system tables missing from backup

change-data-capturesql serversql-server-2008sql-server-2012

I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012.

There are CDC-related tables under the "System Tables" branch in Object Explorer:

cdc.ddl_history
cdc.lsn_time_mapping
cdc.captured_columns
cdc.index_columns
cdc.change_tables

After backing up the database and restoring it on a different server, the CDC-related tables are not there.

I tried to copy them across using SSIS, but they are not visible in the "Export data" wizard GUI. It appears the only way of actually copying them across would be to export them manually or by outputting SELECT * FROM... results to a file.

How can I ensure the Change Data Capture related tables are restored properly?

Best Answer

If you are restoring a CDC-enabled database onto a different instance, you must ensure the server principal that "owns" the database in question is present on the target instance prior to performing the restore. You must also ensure you use the KEEP_CDC option when restoring the database.

From the MSDN page on restore options:

  • KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

  • Restoring the database with KEEP_CDC will not create the change data capture jobs. To extract changes from the log after restoring the database, recreate the capture process job and the cleanup job for the restored database. For information, see sys.sp_cdc_add_job (Transact-SQL).

  • For information about using change data capture with database mirroring, see Change Data Capture and Other SQL Server Features.

I just restored a test CDC-enabled database onto my SQL Server 2012 instance that was created on my test SQL Server 2008R2 instance, without creating the login that owns the database on the 2012 instance. An error was shown during the restore:

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262

Could not update the metadata that indicates database CDCTest is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

The login must also be a member of the sysadmin server role prior to restoring the CDC-enabled database.

You will need to re-create the owner of the database in the target instance using the same SID if the owner is a SQL login (if the owner is a Windows login, the SID will naturally be the same if the Windows login is a domain-member).

You'll need to perform something like the following to restore your database:

/*
    obtain the identify of the login that "owns" the database by looking at the 
    UserName column in the output from the following command:
*/
RESTORE HEADERONLY FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'

/*
    This login is the owner of the database
*/
CREATE LOGIN CDCTestLogin 
WITH PASSWORD = 'LozierPituophisUnconsciousShelduck4'
    , SID = 0x2ECDACB721D7E84E8A28DCFE1C758799;

/*
    Ensure the login is a member of the 'sysadmin' server-level fixed role.
*/
EXEC sp_addsrvrolemember @loginame = 'CDCTestLogin', @rolename = 'sysadmin';
GO


/*
    Restore the database, with the KEEP_CDC option
*/
RESTORE DATABASE CDCTest FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'
WITH MOVE 'CDCTest' TO 'D:\SQLServer\MV2012\Data\CDCTest.mdf'
    , MOVE 'CDCTest_log' TO 'D:\SQLServer\MV2012\Logs\CDCTest_log.LDF'
    , REPLACE
    , KEEP_CDC;
Related Question