How to restore a "contained" database? [closed]

Question

I recently tried to restore to my local development SQL Server a backup from a network instance. To my surprised I received the following error message:

Msg 12824, Level 16, State 1, Line 3 The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

What steps must I follow to successfully restore the database?

Answer 1

In order to restore a contained database into a different instance of sql server, in this case my local server, the "Enable Contained Databases" property must be set to True.

You can do this from management studio:

1. Right-Click on the server instance, select Properties
2. Select Advanced page, set under Containment the property value to True
3. Proceed to restore the database backup.
4. ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName TO ReplaceThisWithLeastPrivilegeUser;

Here are the script lines I actually used for enabling/disabling containment:

-- Enable "contained database authentication"
EXEC sp_configure 'contained', 1;
RECONFIGURE;

-- Disable "contained database authentication"
EXEC sp_configure 'contained', 0;
-- Force disabling of "contained database authentication"
RECONFIGURE WITH OVERRIDE;

For additional information please refer to:
http://www.sqlsoldier.com/wp/sqlserver/protectionfromrestoringabackupofacontaineddatabase

Answer 2

Have you tried doing what the error is describing?

USE master
GO
sp_configure 'show   advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show   advanced options', 0 
GO
RECONFIGURE
GO