Forum Discussion

sqltleo's avatar
sqltleo
Copper Contributor
Jan 22, 2024

Preserve CDC info while restoring (-KeepCDC)

I would like to preserve the CDC information while refreshing the development using production backup. We use a sophisticated powershell script to perform the restores. I am a little unclear on the usahe of -KeepCDC parameter with powershell module.


write-host $file.FullName : $dbName;
Restore-DbaDatabase -SqlInstance $ServerInstanceName `
-DatabaseName $dbName `
-Path $file.FullName `
-DestinationDataDirectory F:\MSSQL\DATA `
-DestinationLogDirectory G:\MSSQL\Logs `
-WithReplace `
-EnableException


Below is the discription of -KeepCDC . However, I am not sure what value to pass in the powershell. Can I just append above script with "-KeepCDC" ? Example below:

...
..
.
-WithReplace `
-EnableException
-KeepCDC


----------------------------------------------------------------------------------------------------
-KeepCDC
This field prevents "change data capture" settings from being reomved. When a database or log backup is
restored on another server and database is recovered.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False
-----------------------------------------------------------------------------------------------------------------

 

Regards,

leo

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    However, I am not sure what value to pass in the powershell.

    Default value: False


    The parameter is a boolean value: False/True

    • sqltleo's avatar
      sqltleo
      Copper Contributor
      Thank You olafhelper for the information. I have updated PowerShell code to below:
      Restore-DbaDatabase -SqlInstance $ServerInstanceName `
      -DatabaseName $dbName `
      -Path $file.FullName `
      -DestinationDataDirectory F:\MSSQL\DATA `
      -DestinationLogDirectory G:\MSSQL\Logs `
      -WithReplace `
      -EnableException
      -KeepCDC True <----This is the change added

      Regards,
      Leo
      • Riddhi640's avatar
        Riddhi640
        Copper Contributor

        sqltleo Keep CDC is available for Microsoft.SqlServer.Management.Smo.Restore ?

        I need to restore a database with CDC from one env to another so I want to know if I can restore with all CDC tables. 

Resources