KEEP_CDC restore suggestion - by AlvaroFernandez

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 777585 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 1/26/2013 12:35:48 PM
Access Restriction Public



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?


Sivsa Soluciones
Sign in to post a comment.
Posted by AlvaroFernandez on 5/2/2013 at 7:18 AM

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 ,

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.


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

Any update over this issue?

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