Restoring Database with CDC Enabled

In this post, I am going to talk about an issue I found while restoring a backup of CDC enabled database to a different SQL instance. You may receive a warning message that includes some information about the cause of the warning, which can help you to resolve the issue.

The warning message is as follows:

Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command “.sys.sp_replhelp N’DisablePerDbHistoryCache”. The error returned was 3930

On the screenshot below, the error says that CDTEST database restore failed and it is not enabled for CDC.

Resolution

CDC is a feature enterprise edition available in 2008 or higher versions. It helps to audit/change tracking database changes like insert, update, and delete operations in a table. Changes are available in the relational format. It captures the changes in tables, mirrors the column structure in source tables, and applies those changes to target tables. Table-valued functions are provided to allow a systematic access to the data modified by consumers. Captured changes from source tables including insert, update and delete operations are applied to tracked source tables, while the entries describing those changes are added to the SQL Server transaction log. The log serves as input to the change data capture process. Functions are provided to enumerate the changes returning the information in the form of a filtered result set. The filtered result set is typically used by an application process to update a representation of the source in some external environment.

CDC configuration:

• Enables CDC at the database level using the sp_cdc_enable_db system procedure
• Creates a CDC schema and CDC database user.
• Verifies if the database is enabled for CDC by looking at the is_cdc_enabled column for the database entry in the sys.databases catalog view
• You must be granted with a sysadmin fixed server role in order to verify the status of CDC

You should have a valid object name that must be unique across instances. Changes are captured in a capture schema of the enabled database.

The change data capture table contains the first five columns as metadata columns. It captures the information about recorded changes. The rest of the columns hold the captured column data that is generated from the source table. Any DML operation appears as a row in a change data table. For example, data columns of the row that result from an insert operation contain the column values after the insert operation is completed. A delete operation contains the column values before the delete operation is completed. An update operation contains column values before the update operation is performed in the first-row entry and similarly second-row entry to identify the column values after the update.

The changes made to a capture instance for a source database depend on a capture interval. The change data available for a capture instance is called Capture validity interval for a database. It is the interval between the first capture instance and the present time. The change data capture cleanup process is responsible for the retention-based cleanup policy and it removes the changes in the table after the retention expiry date.

The cdc.lsn_time_mapping table is responsible for keeping committed changes and new entries during the capture process. This table contains the Log Sequence Number (LSN) commit and a transaction commit time. The maximum LSN value that is found in the mapping table represents a high water mark while the commit time represents the base from which a retention-based cleanup computes a new low water mark. All the change data is retrieved from the transaction log, thus, there is some latency between the change committed to a source table and the change within its associated change table.

Below, you can find a script to create the CDC enabled in a database.

CREATE DATABASE [CDCTEST]

 ON PRIMARY

(NAME = N'CDCTEST', FILENAME = N'D:\Databases\CDCTEST.mdf')

 LOG ON

(NAME = N'CDCTEST_log', FILENAME = N'E:\Logfiles\CDCTEST_log.ldf')

GO


USE CDCTEST

GO

CREATE TABLE dbo.Employee

  (EmployeeID INT CONSTRAINT PK_Employee PRIMARY KEY IDENTITY(1,1),

   FirstName VARCHAR(50),

   LastName VARCHAR(50))

GO

-- Enable Database for CDC template

EXEC sys.sp_cdc_enable_db

GO

-- Enable CDC for specified table “Employee” in current database


EXEC sys.sp_cdc_enable_table

  @source_schema = 'dbo',

  @source_name = 'Employee',

  @supports_net_changes = 1,

  @role_name = NULL,

  @index_name = 'PK_Employee'

GO                              

INSERT INTO dbo.Employee

  (FirstName, LastName)

VALUES

  ('Ganapathi', 'varma')

GO

Backup of the database with the enabled CDC in an existing instance and restore of the database to a different instance.

For backup and restore a database, I am using a great IDE, dbForge Studio for SQL Server, a tool for SQL Server management, administration, and development. The Backup Wizard feature helps you to automate the backup process and simplifies the backup procedure.

Let us connect to a source SQL Server.

To start an SQL Server backup, right-click a required database in Database Explorer, point to Tasks and select Back Up… on the shortcut menu. The Backup window opens.

On the screenshot below, in the General Tab, you can view or modify settings for a database backup operation. Specify a path to back up the database to a disk.

Then, select the Overwrite all existing back up set and Verify backup when finished options. Click Next.

On the screenshot below, select SQL Server backup options and click Back Up. You can compress the backup option.

As you can see, the backup is successful.

You can also use a T-SQL script below to back up a database.

Backup database CDCTEST to disk=’C:\CDCTEST.bak’


Restore database CDCTEST from disk=’C:\CDCTEST.bak’ with keep_cdc

The screenshot shows that the “Keep_CDC” option with the database restore statement must be used to restore the CDC enabled database backup successfully.

In addition, you need to add the Capture and Cleanup jobs using the following commands in the appropriate database.

exec sys.sp_cdc_add_job 'capture'
	GO
	exec sys.sp_cdc_add_job 'cleanup'
	GO

We have two SQL Server Agent jobs associated with a change data capture enabled database. A capture job is used to populate the database change table and a cleanup job is responsible for a change table cleanup. The capture and cleanup jobs are created when a replication is enabled for the first time and initiated change data capture for a table.

The sp_MScdc_capture_job stored procedure runs to initiate the capture job. This stored procedure starts by extracting the configured values for maxtrans, maxscans, continuous, and pollinginterval for the capture job from msdb.dbo.cdc_jobs. These configured values are then passed as parameters to the sp_cdc_scan stored procedure. This is used to invoke sp_replcmds to perform the log scan.

The retention policy is used to maintain cleanup and change table size. The cleanup job is responsible for handling and maintaining a strategy to clean up for all database change tables.

The sp_MScdc_cleanup_job stored procedure runs to initiate the cleanup job. This stored procedure starts by extracting the configuration values like threshold values and retention from msdb.dbo.cdc_jobs. A low watermark for all capture instances is initially updated in a single transaction when cleanup is performed. It then tries to remove entries from the change tables and the cdc.lsn_time_mapping table.

Also Read:

Implementing Incremental Load using Change Data Capture in SQL Server