This procedure can be used to recover CDC solutions used in SQL Server when a SQL Server backup/restore is used to recover a data base used in an Attunity CDC solution. The procedure is used to complete the restore process and rebuild the tables in order to comply with the table structure of the source data base on the backup. Note, this example recoveres one SQL Server table. The attached zip/Word documents shows the data base restore options used.

1.) Deactivate workspaces via Attunity Studio
2.) Stop the SQLCDC log miner service
3.) Restore database from the back file(see attached Word doc.)
4.) Manually rebuild the indexes under the table (test_ref in this case)
5.) Notice the system tables only show the dbproperties. We need to rerun our provided SQL to get the replication and
checkpoint set. These are the scripts found on the Deployment summary of each CDC solution. Each set needs to be run for each CDC solution.

Example SQL:
a.) --++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
-- Replication script generation details:
-- Server name :SQLSERVER
-- Database :yourdb
-- CDC Instance :yourdb
-- Type : Anonymous PULL
-- Generated on :Mon Mar 15 11:30:43 EDT 2010
--++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ ++'
print ' '
print ' Step #0 - Establishing general replication setup'
print ' '
print '+-------------------------------------------------+'
print ''
use Master
GO
print' *** Enabling the replication database ***'
exec sp_replicationdboption
@dbname = N'yourdb',
@optname = N'publish',
@value = N'true'
GO
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++'
print ' '
print ' Step #1 - Expunging all existing publication definitions.'
print ' '
print ' (Errors due to non-existent objects may occur. '
print ' Discard them...) '
print '+-----------------------------------------------------------+'
print ''
use [yourdb]
GO
print ' *** Dropping the transactional subscription ***'
print ' *** Dropping the transactional articles ***'
exec sp_dropsubscription
@publication = N'yourdb',
@article = N'test_ref',
@subscriber = N'all', @destination_db = N'all'
exec sp_droparticle
@publication = N'yourdb',
@article = N'test_ref',
@force_invalidate_snapshot = 1
GO
print ' *** Dropping the transactional publication ***'
exec sp_droppublication @publication = N'yourdb'
GO
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++'
print ' '
print ' Step #2 - Adding publication '
print ' '
print '+----------------------------------------------------------+'
print ''
exec sp_addpublication
@publication = N'yourdb',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Anonymous Transactional publication of yourdb database from Publisher SQLSERVER.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 1,
@allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21,
@ftp_login = N'anonymous', @allow_dts = N'false',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false'
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++'
print ' '
print ' Step #3 - Granting access '
print ' '
print '+----------------------------------------------------------+'
print ''
exec sp_grant_publication_access
@publication = N'yourdb',
@login = N'sa'
-- exec sp_grant_publication_access @publication = N'{Solution}', @login = N'{LoginName#1}'
-- exec sp_grant_publication_access @publication = N'{Solution}', @login = N'{LoginName#2}'
-- exec sp_grant_publication_access @publication = N'{Solution}', @login = N'{LoginName#3}'
-- ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++++++++
--
-- For achieving compliance with MS-SQL replication standards, one can also fill the LoginName#?
-- place holders by the data set retrieved due to the following query:
--
-- select loginname from master..syslogins
-- where (is_srvrolemember('sysadmin', loginname) = 1 ) order by 1
-- ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++++++++
GO
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++'
print ' '
print ' Step #4 - Adding the transactional articles '
print ' (Parameters are assigned with defaults) '
print ' '
print '+----------------------------------------------------------+'
print ''
exec sp_addarticle
@publication = N'yourdb',
@article = N'test_ref',
@source_owner = N'dbo',
@source_object = N'test_ref',
@destination_table = N'test_ref',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_test_ref',
@del_cmd = N'CALL sp_MSdel_test_ref',
@upd_cmd = N'MCALL sp_MSupd_test_ref',
@filter = null,
@sync_object = null
GO
print ''
print '+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++'
print ' '
print ' Setting replication definitions for CDC completed. '
print ' ================================================ '
print ' '
print ' Defaults are assumed for all implicit value settings.'
print ' '
print ' Check for meaningful errors possibly issued. '
print ' '
print ' Further control and information are available via the '
print ' Enterprise Manager pane'
print ' '
print '+--------------------------------------------------------------------------+'
print ''
b.) EXEC sp_dboption 'yourdb', 'trunc. log on chkpt.', 'FALSE'
c.) SELECT [detainerKey]
,[detainerTimestamp]
FROM [yourdb].[dbo].[CDCdetainer]
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CDCdetainer' AND type = 'U')
DROP TABLE [CDCdetainer]
GO
CREATE TABLE [CDCdetainer] (
[detainerKey] [char] (1) NOT NULL,
[detainerTimestamp] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[detainerKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into CDCdetainer values ('A',GETDATE());
insert into CDCdetainer values ('B',GETDATE());
GO

6.) Restart the SQL Server instance.
7.) Delete the SQLCDC Log miner file and the transient log files.
8.) Delete the staging tables
9.) Start the SQLCDC Log miner service
10.) Activate the CDC solution.