Powershell SQL Server database restore (with CDC)

Question

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.

Thanks, I'm not familiar with CU. what does it stand for and how can I get the bug fix? I'm assuming the U stands for Update. — Antony Scott
– Antony Scott, Commented Aug 13, 2012 at 23:17
Search for cumulative update. I can't seem to find the relevant one right now. If you can, just install the newest one. — usr
– usr, Commented Aug 13, 2012 at 23:25
I see a KeepReplication property on the restore class you could try setting it to false msdn.microsoft.com/en-us/library/… — Chad Miller
– Chad Miller, Commented Aug 14, 2012 at 1:17

Ben Thul · Accepted Answer · 2012-08-14 18:06:50Z

3

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

answered Aug 14, 2012 at 18:06

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antony Scott Over a year ago

It worked after a few minor modifications. I had to execute the script using the master database. I got that like so ... $masterDatabase = $server.Databases | Where-Object { $_.Name -eq "master" } ... I then executed the script like so ... $masterDatabase.ExecuteNonQuery($script)

Collectives™ on Stack Overflow

Powershell SQL Server database restore (with CDC)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related