How to preserve SQL Server CDC table data during restores and refreshes?

Question

We have below two requirements . Our reporting system reads from CDC tables for aggregations, sorting etc and the CDC data is critical for business.

i. How do we make sure we preserve the data in CDC tables when we restore from backups ? In case of a disaster we may have to restore from backups . Default behavior is we lose all data in CDC tables.

ii. We do daily refresh on non-prod from Prod backup. How do we copy the CDC data from Prod to non-prods ? We need this for UAT / testing of reporting systems

Thank You

Answer 1

..........

Answer 2

Hi @Tony Thomas,

i. How do we make sure we preserve the data in CDC tables when we restore from backups ? In case of a disaster we may have to restore from backups . Default behavior is we lose all data in CDC tables.

To preserve the data in CDC tables during a restore from backups, you can use the "KEEP_CDC" option of the RESTORE DATABASE command. This option ensures that the CDC capture instances are preserved after the restore operation, so that you can continue to capture changes to the database after the restore. Here is an example of how to use the KEEP_CDC option:

RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\YourBackup.bak' WITH KEEP_CDC;

ii. We do daily refresh on non-prod from Prod backup. How do we copy the CDC data from Prod to non-prods ? We need this for UAT / testing of reporting systems

Transactional replication can be used to replicate CDC data changes from the source database to one or more target databases. This can be useful for keeping non-prod environments up-to-date with the latest changes made in the Prod environment.

Here are the general steps you can follow:

Create a publication on the source database and enable CDC for the publication.

Create a subscription on the target database and configure it to use transactional replication.

Configure the subscription to replicate CDC data changes by setting the "replicate_ddl" and "allow_cdc" options to true.

Start the subscription and allow it to synchronize with the source database.

After completing these steps, CDC data changes in the source database will be replicated to the target database(s) on a regular basis.

It's important to note that transactional replication requires additional configuration and maintenance, and it may add some overhead to your system. So, you will need to weigh the benefits of having the latest CDC data in your non-prod environments against the costs of maintaining the replication setup.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".