Monday 6 August 2012

Restoring SQL Server Database with Change Data Capture

Just upgraded my SQL Server to version 2012.  I needed to because a client had upgraded and some features were making it difficult to restore their database to my computer.  Still, I got an annoying error when trying to restore the database via the wizard:

"System.Data.SqlClient.SqlError: Could not update the metadata that indicates database DBNAME 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.'. Use the action and error to determine the cause of the failure and resubmit the request. (Microsoft.SqlServer.SmoExtended)"

I love cryptic error messages.

Anyway, after a bit of research I was able to restore the database by using the following line:

restoredatabase DBNAME FROM DISK = 'c:\data\BackupFile.bak' with keep_cdc 

The important bit was the 'with keep_cdc' option.