2

I'm using SQL Server Change Data Capture in a production database. To my surprise, I found that when restoring that database to a lower environment the CDC data is not kept—all the tables, functions, and so on are gone because SQL Server removes the CDC information during the recovery phase of a restore to any other database besides the one that originated the backup.

I learned that there is a RESTORE option, KEEP_CDC, that can be used in conjunction with the RECOVERY option (as the last step of a restore chain) to prevent the CDC information from being removed.

Transact-SQL REFERENCE: RESTORE Arguments

KEEP_CDC

Supported by: RESTORE

KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

Our organization uses NetBackup as its primary way to backup and restore databases. Getting ahold of a backup file outside of that is not something that will be easy to do and likely could not ever be routine. We have a web site that lets developers restore production backups to other environments without having to touch the files themselves.

Question: So what do I really need—to find out if there is a way in NetBackup 7.7 to specify additional restore options so that WITH_CDC can be passed to the RESTORE command? Or does NetBackup not use a true RESTORE statement? It seems like it would have to somehow use the built-in SQL Server restore functionality, otherwise it couldn't trigger the recovery phase of a restore properly. Or maybe I'm just out of my depth, here.

I can't find much information on NetBackup's abilities, and have been assured by a Senior Database Engineer that NetBackup 7.7 can't do this. However, I'm wondering how deeply he searched and whether someone perhaps more intimately familiar with the product might know of a way to hack such a thing in. Apparently version 8.0 is coming out soon-ish, but again I can't seem to find online a solid documentation source for how this might be accomplished. (It seems like "additional command-line arguments" could be possible, even if the option is not explicitly supported through a named switch or other setting.)

  • P.S. This answer adds additional, useful information for those interested in the handling of CDC during restores. – ErikE Nov 29 '16 at 6:52
1

I just found this question answered by Veritas themselves. Though this answer is for 7.5 instead of 7.7, I doubt they added this in the meantime.

www.veritas.com: Does Netbackup support the MS-SQL feature "WITH KEEP_CDC"?

As of NetBackup 7.5 there is not support for the "WITH KEEP_CDC" functionality for Backup or Restores.

Very disappointing.

0

https://www.veritas.com/support/en_US.html

you'll have to find the SQL Agent Guide for that version of Netbackup and see what it supports. Technically you need 7.5 to backup SQL 2012 but I think that version supports only the bare features. I think they only introduced some of the advanced features in version 8, but I can't say with 100% certainty. You will need to find the doc for your version and check to see what it supports

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.