Restoring SQL Server Database with Change Data Capture

Posted: 01 Kasım 2016 in SQL, SQL SERVER

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.

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Connecting to %s