question

One simple solution could be to take the backup, which already should be the case as you are doing some bulk DML operations, and then restore it as another Database. After the bulk operation, enable CDC and follow what were you doing before. Since the table names would be the same, it would not be that much difficult to load the data back. For your script demand, below is a preliminary script, which you should test thoroughly before using. At the moment this only will print the INSERTION commands. My assumption is that you have taken the backup and restored it as another DB with option "`KEEP_CDC`" ON. Moreover, it is assumed that there are no columns dropped from CDC while re-enabling (An addition of a column will be catered hopefully). You need to run this script on the restored DB and need to act according to the comments made in the script. Furthermore, this does not handle the tables **cdc.ddl_history** and **cdc.index_columns**, which I believe you can handle manually. If you want to stick to the staging table approach on the same DB, then you can tweak it easily. One suggestion could be to CREATE the staging tables by appending a Prefix/Post fix to the original tracking tables (not tracked ones). Hopefully, it will help you. **SCRIPT** SET NOCOUNT ON ; CREATE TABLE #cdc_tables -- TO CAPTURE THE OUTPUT OF sp_cdc_help_change_data_capture ( source_schema SYSNAME ,source_table SYSNAME ,capture_instance SYSNAME ,object_id INT ,source_object_id INT ,start_lsn BINARY(10) ,end_lsn BINARY(10) ,supports_net_changes BIT ,has_drop_pending BIT ,role_name SYSNAME NULL ,index_name SYSNAME NULL ,filegroup_name SYSNAME NULL ,create_date DATETIME ,index_column_list NVARCHAR(MAX) NULL ,captured_column_list NVARCHAR(MAX) NULL ) DECLARE @DSQL NVARCHAR(MAX) , @capture_instance SYSNAME , @captured_column_list NVARCHAR(MAX) INSERT [#cdc_tables] -- INSERTION EXEC sys.sp_cdc_help_change_data_capture DECLARE CDCCursor CURSOR FAST_FORWARD FOR SELECT [capture_instance], captured_column_list FROM [#cdc_tables] AS CT OPEN CDCCursor FETCH NEXT FROM CDCCursor INTO @capture_instance, @captured_column_list WHILE @@FETCH_STATUS = 0 BEGIN /*== A SIMPLE APPROACH WITH NO COLUMN NAMES COULD BE ====*/ --'INSERT INTO YOURPRODUCTIONDBNAME.cdc.'+ QUOTENAME([capture_instance] + '_CT') + ' SELECT * FROM cdc.'+ QUOTENAME([capture_instance] + '_CT') -- CHANGE NAME HERE IF USING STAGING TABLES /*== USE THIS IF YOU FEEL COMFORTABLE WITH IT. I DID NOT TEST IT*/ /*== COLUMN NAMES ARE SPECIFIED IN THIS BELOW APPROACH. ONE THING GOOD WITH IT IS IF YOU HAVE ADDED MORE COLUMNS FOR CHANGE TRACKING THIS HOPEFULLY WILL NOT FAIL. BUT IF SOME COLUMNS ARE REMOVED, THEN IT WILL NOT CATER ==*/ SET @DSQL = 'INSERT INTO '+ 'YOURPRODUCTIONDBNAME' + -- PUT YOUR PRODUCTION DB NAME HERE '.cdc.' + QUOTENAME(@capture_instance + '_CT') + ' ([__$start_lsn], [__$end_lsn], [__$seqval], [__$operation],[__$update_mask], ' -- THESE ARE THE COLUMNS UNIVERSAL IN ALL TRACKING TABLES + @captured_column_list + ')' + ' SELECT [__$start_lsn], [__$end_lsn], [__$seqval], [__$operation],[__$update_mask], ' -- AGAIN UNIVERSAL COLUMNS IN ALL TRACKING TABLES + @captured_column_list + ' FROM ' + QUOTENAME(DB_NAME()) + '.cdc.' + QUOTENAME(@capture_instance + '_CT') -- CHANGE NAME HERE IF USING STAGING TABLES PRINT @DSQL --EXEC (@DSQL) -- UNCOMMENT IF YOU WANT TO EXECUTE FETCH NEXT FROM CDCCursor INTO @capture_instance, @captured_column_list END DROP TABLE [#cdc_tables] CLOSE CDCCursor DEALLOCATE CDCCursor

CDC is not designed to do what you are wanting. You can transfer the CDC data into permanent user tables for future reference and offer that up to the web application. This way you can disable CDC for the data load and re-enable it afterwards and lose no data. You will then have to devise a system to periodically transfer the CDC data into the permanent tables and clean these tables up (if necessary).