locked
CDC does not work on a restored Database RRS feed

  • Question

  •  

    Hi,

     

    I am working on the CDC feature of the SQL Server 2008 Feb CTP. I restore a database and try to explore the CDC fetaure. CDC get enabled for the Database and the table but no data gets captured in the CDC Captured table.

     

    But when I create new Database and new tbales within it and try to explore the CDC features, it works absolutely fine. Data gets captured without any issue.

     

    Note: SQL Server Agent is running before I try to capture the data.

     

    Regards,

    Ankur Kumar

    Friday, February 29, 2008 1:41 PM

Answers

  • I'm marking this thread as answered for now, if you still have an issue please post with some more info.

    Thursday, March 13, 2008 6:29 PM

All replies

  • Moving to replication forum.

     

    Can you tell us a little bit more about the restored database?  What is the compat level of the database? 

    Friday, February 29, 2008 4:30 PM
  • Please let us know if you still have any issues, otherwise i will close this thread.

    Thursday, March 6, 2008 6:32 PM
  • Hi,

     

    The issue is still not resolved. If I restore the backup file on an existing database, CDC does not work. Though CDC tables and other objects get created but data is not captured.

     

    On the other hand, if I copy/export DB and import it on another machine or DB, CDC works fine.

     

    I don't know may be on restoring a database, some permissions does not get reflected which are required by CDC feature to work. Though i checked the configuration set-up for CDC for a DB in Help. I performed all steps on restored DB but no success.

     

    Regards,

    Ankur Kumar

     

    Friday, March 7, 2008 9:04 AM
  • Ankur, I'd like to know more about the backup.  What is the compat level of the db after it is restored, is it a SQL 2005 db backup, is it Katmai db backup, etc.?  IF a Katmai backup, from what CTP? 

     

    Also can you validate that the CDC capture job is created and is running?  You can also check to see if there are any errors in sys.dm_cdc_log_scan_sessions.

    Friday, March 7, 2008 4:58 PM
  • you can check whether there is any error in sys.dm_cdc_errors; besides, you can stop agent service and manually run sp_cdc_scan from management studio.

    Saturday, March 8, 2008 1:02 AM
  • I'm marking this thread as answered for now, if you still have an issue please post with some more info.

    Thursday, March 13, 2008 6:29 PM
  •  

    Even when I tried restoring a database and trying to run CDC it was not working. Probably you have to check the cdc capture jobs, and see whether it goes to retries mode.

    I got this error on the job history.

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. [SQLSTATE 42000] (Error 15517)

     

    If this is the error you get then you must check whether what level of permission your id has on the db.

     

     

     

    Saturday, March 29, 2008 12:33 AM