Search code examples
sql-serverpowershelldatabase-restore

Powershell SQL Server database restore (with CDC)

I am trying to create a Powershell script to restore database to my laptop from my desktop. I have a script which creates the backup files and have almost got the restore script working apart from a strange error I get with a CDC enabled database. What I end up with is an off-line single user database. I have to bring it back on-line and change it back to multi-user manually. Here are the relevant bits of my powershell code ...

$instance = "(local)"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $instance

$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$restore.Action = "Database"
$restore.Database = $dbname
$restore.NoRecovery = $false
$restore.ReplaceDatabase = $true
$restore.Devices.AddDevice($filename, "File")
$restore.SqlRestore($server)

I get an error message saying ...

*System.Data.SqlClient.SqlException: Could not update the metadata that indicates database xxxxx is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission'*

and a bit further down ...

*The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.*

further down ...

Converting database 'xxxxx' from version 655 to the current version 661.
Database 'xxxxx' running the upgrade step from version 655 to version 660.
Database 'xxxxx' running the upgrade step from version 660 to 661.

While I can get the database back to a useable state, I would ideally like to have it completely scripted. The idea of this is that I can run the backup script on my desktop and then run the restore script on my laptop, which then restores the databases on my laptop so I have a working copy of the same database for when I need to work remotely.

Any insights would be great, even better if someone has come across and solved the same problem.

Solution

  • When I had to set an explicit CDC setting in a restore script, I did something like:

        $script_lines = $restore.script( $server )
    $script_lines += ', keep_cdc'
    $script = ''
    foreach ($line in $script_lines) {
            $script += $line
    }

    $script
    invoke-sqlcmd -ServerInstance $server.name -Query $script -QueryTimeout 65535