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
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
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.)