SQL Server – Using NetBackup 7.7 WITH KEEP_CDC

Change Data Capture, or CDC, does not require the KEEP_CDC keyword when restoring a database to the original server. When restoring a database to a server other than the original server, you can use the KEEP_CDC keyword to specify that the change data capture data is retained in the database. This is by design, and is clearly stated in BOL here under the section entitled "Restoring or Attaching a Database Enabled for Change Data Capture"

This code shows CDC in action both before and after a RESTORE DATABASE ... WITH NORECOVERY

CREATE DATABASE CDC_RESTORE_TEST 
ON (NAME='CDC_RESTORE_TEST', FILENAME = 'C:\SQLServer\Data\CDC_RESTORE_TEST.mdf', SIZE = 10MB, FILEGROWTH = 1MB)
LOG ON (NAME = 'CDC_RESTORE_TEST_LOG', FILENAME = 'C:\SQLServer\Logs\CDC_RESTORE_TEST.ldf', SIZE = 1MB, FILEGROWTH = 1MB);
GO
ALTER DATABASE CDC_RESTORE_TEST
SET RECOVERY FULL;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_enable_db;
GO
CREATE TABLE Test
( 
    ID INT NOT NULL CONSTRAINT PK_Test PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , DATA NVARCHAR(255) NOT NULL
);
EXEC sys.sp_cdc_enable_table 'dbo', 'Test', 'dbo_Test', 1, NULL;
GO
ALTER TABLE Test ADD Data1 NVARCHAR(255) NOT NULL;
WAITFOR DELAY '00:00:15'; /* wait for event to be captured */
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

USE Master;
GO
BACKUP DATABASE CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST.BAK';
BACKUP LOG CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.BAK';
GO
ALTER DATABASE CDC_RESTORE_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE CDC_RESTORE_TEST;
GO
RESTORE DATABASE CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY;
RESTORE LOG CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

Restoring the same backup onto the same server as a new database name appears to retain CDC:

USE Master;
GO
RESTORE DATABASE CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak' 
WITH NORECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
RESTORE LOG CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

Firstly,

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.