Creating CDC system table on SQL Server 2008

Question

I am facing problem with CDC tables in my database. I think few of these tables are damaged and are not capturing any audit information. I tried to disable CDC on these tables, but that did not work (I didn't even get an error). So I manually dropped a table.

Now I cannot see the table in system tables - but its still not properly deleted. So I cannot re-enable it.

I have got a fix here: http://support.microsoft.com/kb/966306

which is to manually create the CDC table of the same name in system tables - and retry disabling it.

Now the question is how do I manually create a system table.

Thanks

Answer 1

As the very article you linked says:

You can script those objects from a new dummy database in which you have successfully enabled CDC

These tables are not 'system' as in 'they belong to the sys namespace and protected from direct user operations' but are 'system' as in 'they are absolutely ordinary tables used by stored procedures shipped with CDC and MS just wishes nobody would mess with them'...

Answer 2

1. assume your database name is : DB1.
2. assume Your last backup of DB1 which cdc tables are exists is : DBbk.
3. restore DBbk.You can script those objects from this database in which you have successfully enabled CDC.
4. authorize cdc user in DB1.
5. mark created cdc tables as system tables in DB1.
6. fill DB1 cdc tables from DBbk.
7. do this proccess for all cdc tables which are exists in DBbk and are not exists in DB1.
8. create a needed index on cdc.change_table.
9. in security section , change cdc user , db role to 'db_owner'
10. in security section , correct schema owner to its owner not to cdc
11. now you can disable cdc and enable it again.

.

/* scripts step 1..6 */


use DB1
GO

/****** Object:  Table [cdc].[captured_columns]    Script Date: 02/11/2013 18:12:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [cdc].[captured_columns](
    [object_id] [int] NOT NULL,
    [column_name] [sysname] NOT NULL,
    [column_id] [int] NULL,
    [column_type] [sysname] NOT NULL,
    [column_ordinal] [int] NOT NULL,
    [is_computed] [bit] NULL,
 CONSTRAINT [captured_columns_clustered_idx] PRIMARY KEY CLUSTERED 
(
    [object_id] ASC,
    [column_ordinal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

----------------------------
alter authorization on schema::[cdc] to [cdc]

----------------------------
exec sp_MS_marksystemobject '[cdc].[captured_columns]';

---------------------------
insert into [cdc].[captured_columns]
SELECT * FROM [DBbk].[cdc].[captured_columns];

/* scripts step 8 */
/****** Object:  Index [source_object_id_idx]    Script Date: 02/11/2013 18:44:00 ******/
CREATE NONCLUSTERED INDEX [source_object_id_idx] ON [cdc].[change_tables] 
(
    [source_object_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO