May 16, 2012 at 11:19 am
Hello,
Suppose you have a database that you need to restore on another server. This database has SQL Server CDC enabled, but you don't need the change data on the restored copy. If you run a restore command without the KEEP_CDC flag, this yields the result you seek.
However, what we've noticed is that, after the restore is almost completely done, we see a series of SELECT * from dbo_Table1_CT into dbo_Table1_CT commands in the DMVs for running queries. It is as if SQL Server is reindexing or performing some other operation on the capture instance tables, even though it will ultimately drop those tables.
Does anyone have any experience with this to help us understand what the restore process is doing with the _CT tables? This takes quite some time and in the end, the tables aren't even there.
One option we haven't explored yet is a piecemeal restore without restoring the filegroup that contains our CDC data, but we thought the simple restore statement without KEEP_CDC would do that for us, without taking what can be a long time to manipulate, and then drop, the _CT tables.
Thanks!
April 3, 2013 at 8:10 am
Hello,
Similar issue here. In my case, I use KEEP_CDC and, for "some" 6000 captured tables (but trying to restore an empty database with no rows), the restoring is quite painful, as the engine do the following:
1- It restores the database in a quite normal time.
2- It re-generates all the TVFs for each table, and for the tables supporting net changes (supports_net_changes=1 in cdc.change_tables), it recreates the primary key index for the captured instance!!
- As a direct consequence of (2), it generates HUGE amounts of log transaction (my db is in full recovery mode, and I don't wan't to change this).
I opened in Microsoft Connect this situation at https://connect.microsoft.com/SQLServer/feedback/details/777585/keep-cdc-restore-suggestion#tabs , and hope folks at Microsoft try to check this. Truly, , I don't understand why the server has a need to regenerate any of the above info.
Alvaro
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply