KEEP_CDC restore suggestion | Microsoft Connect

KEEP_CDC restore suggestion by AlvaroFernandez

Active

Type:	Suggestion
ID:	777585
Opened:	1/26/2013 12:35:48 PM
Access Restriction:	Public

Workaround(s)

view

Hello,

I have a database with 3000 tables, all of them captured for CDC for compliance reasons. Now I'm restoring it, but I'm worried by the time the restore (with KEEP_CDC) is taking, and checked also the huge transaction log size the restore is generating.

While the restore is in progress, I'm making a trace and I cannot understand why the engine internally seems to just recreate the CDC schema (ie, creating all the CDC's procedures and functions for each table, and creating indexes/primary keys for all the tables).

Sorry for my ignorance, but why is re-creating the CDC environment this way? all the physical structures (tables and indexes) are in the backup, right? if so, why the need to recreate them on restore?

am I wrong?

regards,

Alvaro
Sivsa Soluciones

Details (expand)

Spanish

SQL Engine

Just restore the pages of tables and indexes from the backup.

Improved Administration

0 attachments

Posted by AlvaroFernandez on 5/2/2013 at 7:18 AM

Hello,

Yesterday I've read that this bug was already solved in SQL2008 R2 SP1 Cumulative Update 4 (FIX: Slow performance when you restore a database in SQL Server 2008 R2, in SQL Server 2008 or in SQL Server 2012 if CDC is enabled , http://support.microsoft.com/kb/2567366).

But the fix can only be activated via trace flag 3101 from this CU level - and it works perfectly. It does not trigger the sp_cdc_vupgrade operation.

So, please, close this as fixed, and many thanks for having solved this issue.

Regards,

Alvaro

Posted by AlvaroFernandez on 4/3/2013 at 7:00 AM

Hello,

Any update over this issue?

Regards,

Posted by Microsoft on 1/30/2013 at 11:35 AM

Hi AlvaroFernandez,

thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant

Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server