May 20, 2014 at 3:08 am
I'm using SQL Server 2012 Enterprise Edition, and i Enable CDC to started the service.
I've set up CDC on multiple tables, and I see them (under system table)
While the database backup (export file 'BAK'), and imports I do not see the tables of the CDC.
1. Condition restore / backup is full.
2. Carried out an attempt to copy the MDF & LDF files (replace them), to no avail.
I'd love to know how to back up data of these tables.
May 20, 2014 at 3:39 am
CDC is an advanced and complex subsystem within SQL Server. You need to do a lot of reading and study to be able to configure, monitor, and use it correctly. What parts of Books Online did you review? What DMVs have you used to examine the restored database from a CDC perspective? Do you get any errors? Are you going into the restored copy of the database as an SA level user? Recall that when you restore a database to a different server SID's for logins from Server A will not be the same (by default) for Server B and will not have access as they did on Sever A.
The CDC storage objects are tables within the database. To my knowledge there are no tables of any kind in SQL Server 2012 that are not included in every full backup.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2014 at 4:26 am
Chances are you didn't run the restore with the syntax KEEP_CDC. In this event, even though, as Kevin says, the backup includes the CDC tables and data, they're removed during the restore process. Add KEEP_CDC to the WITH clause of the RESTORE and you should see it again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2014 at 6:09 am
Grant Fritchey (5/20/2014)
Chances are you didn't run the restore with the syntax KEEP_CDC. In this event, even though, as Kevin says, the backup includes the CDC tables and data, they're removed during the restore process. Add KEEP_CDC to the WITH clause of the RESTORE and you should see it again.
Thanks Grant - totally spaced out on asking about that. I knew I shouldn't be answering forum posts after waking up at 0330 and not being able to go back to sleep! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2014 at 1:21 pm
TheSQLGuru (5/20/2014)
Grant Fritchey (5/20/2014)
Chances are you didn't run the restore with the syntax KEEP_CDC. In this event, even though, as Kevin says, the backup includes the CDC tables and data, they're removed during the restore process. Add KEEP_CDC to the WITH clause of the RESTORE and you should see it again.Thanks Grant - totally spaced out on asking about that. I knew I shouldn't be answering forum posts after waking up at 0330 and not being able to go back to sleep! :blink:
I was impressed that you were up before me (and/or never went to bed). Still, it's the little things. You catch my giant flubs often enough I can pick up a tiny one after you every so often.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy