Sql-server – Use Netbackup 7.7 `WITH KEEP_CDC`

backupchange-data-capturenetbackuprestoresql serversql-server-2012

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

Best Answer

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