还原包含数据库 Restoring a Contained Database：
One of the first concerns I had when I learned about contained database was how am I as a DBA protected against giving someone access to a database without my knowledge. Well, there are several layers of protection starting with the fact that it is up to the administrator to enable containment before it even comes in to play.
第一层保护 First Layer of Protection：
在还原一个包含数据库或创建一个包含数据库之前，或者之前已经存在一个包含数据库，那么在服务器级别的包含选项必须要启动。如果服务器级别不启动包含选项，那么是完全的保护。你可以通过sp_configure或者通过服务器属性，它在高级选项里，不过他不是一个真的高级选项，所以在你修改或查看服务器级别包含选项时不需要开启Show advanced options选项。
Containment has to be enabled at the server level before a contained database can be restored or created or before an existing database can have containment enabled. As long as you do not enable this setting at the server level, you’re completely protected. You can enable it via sp_configure or via the Server Properties dialog (Advanced tab). Despite the fact that it is listed on the Advanced tab, it is not an advanced option. You do not have to enable Show advanced options before changing or checking this setting. I see a lot of blog posts stating that it is an advanced option and showing that the Show advanced options has to be set, but it is not and does not.
In addition to sp_configure and the Server Properties dialog, you can check the current setting by querying the sys.configurations table. this is my preferred method for querying for configuration settings because it is a lot easier to use in automation scripts. However, if my intent to change it if it needs it, I generally use sp_configure.
The error message you will receive is:
Msg 12824, Level 16, State 1, Line 1 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 1 RESTORE DATABASE is terminating abnormally.
If you have not enabled containment at the server level at you attempt to restore a database that has containment enabled, the restore will fail with an error telling you that containment has to be enabled first. This is a warning to the DBA that the database is contained. It is up to you to decide whether enabling containment is the proper thing to do or not. Please don’t just enable it because the error message said to. This affects the security of your server and databases.
开启或禁用包含数据为选项命令为，注意，这里是Reconfigure，不是Reconfigure WITH OVERRIDE，这里有一个重要的原因，后面对提到。
The option that you are enabling or disabling is contained database authentication and the command to enable/disable containment at the server level is as follows:
-- Enable "contained database authentication" Exec sp_configure 'contained', 1; Reconfigure; -- Disable "contained database authentication" Exec sp_configure 'contained', 0; Reconfigure;
第二层保护 Second Layer of Protection：
如果你启用了包含数据库选项，那么当还原一个包含数据库时，不会有任何警告或是提示。只能由你自己来确定还原的数据库是不是包含数据库，幸运的是，有一个内置的结构可以知道这点，在SQL Server 2012，当你执行RESTORE HEADERONLY时有一个新的列被增加，它就是Containment ，当它为1时，表明是包含数据库，反之则不是，如下图我对一个包含数据库做的检查：
You will notice that I used RECONFIGURE in that code and not RECONFIGURE WITH OVERRIDE. I did so for a very important reason. I will get to that later, but first, let’s take a look at the next layer of protection. Let’s move a step further and see how you are protected after you have enabled containment at the server level. Once the option is enabled, you will get no warnings whatsoever when you restore a contained database. It’s up to you to make sure you know whether or not the backup you are going to restore is contained or not. Fortunately, there is a built-in mechanism for discovering that.
If you are presented with a backup of a new database, you can check for containment yourself. You don’t have to rely on being told about it. A new column (Containment) has been added to the output of the RESTORE HEADERONLY command. You can use this command to inspect the header information for the backup before restoring it. If Containment = 1, containment is enabled in the database. If Containment = 0, it is not enabled.
In this example, I am checking the header information of a backup named c:\bakCDTest.bak:
Restore HeaderOnly From Disk = 'c:\bakCDTest.bak';
It’s very easy to check and there is no reason not to perform this check before restoring a database if the server has containment enabled.
最后一层保护 Final Layer of Protection：
Let’s say you are in a disaster recovery scenario and you need to get the database back online as quickly as possible, but when you attempt to restore the database on a replacement server, you discover that it has containment enabled. Now let’s assume that you had not been aware that this setting was enabled and to the extent of your knowledge and available documentation, the database does not use contained users. Furthermore, let’s assume that there may be some compliancy regulations or security policies that say that you can’t have undocumented contained users. You may not have time to hunt down someone that can explain why there are contained users. You need to get the database restored and also respect your security restrictions.
One option you can do is to enable containment at the server level, restore the database, and then disable containment at the server level. If you try to disable containment at the server level using sp_configure and RECONFIGURE, you will get an error message stating that you can not disable containment because there are contained databases. You can force disabling of containment at the server level by using RECONFIGURE WITH OVERRIDE. This is why I was sure to use just RECONFIGURE in the earlier example. I want to ensure that I don’t accidentally disable the setting if there are contained databases without realizing it.
在禁用时，会报错，需要我们使用WITH OVERRIDE，这其实就是最后一个保护，这就是之前用Reconfigure而不用Reconfigure WITH OVERRIDE的原因。
Here is what happens when you try to disable containment at the server level when a contained database already exists:
-- Disable "contained database authentication" Exec sp_configure 'contained', 0; Reconfigure;
Configuration option 'contained database authentication' changed from 1 to 0. Run the RECONFIGURE statement to install. Msg 12818, Level 16, State 1, Line 3 RECONFIGURE failed. Attempting to change the 'contained database authentication' value to 0 while there are existing contained databases requires a RECONFIGURE WITH OVERRIDE.
-- Force disabling of "contained database authentication" Reconfigure With Override;
Command(s) completed successfully.
There are built-in protections and mechanisms for protecting yourself from restoring a contained database, but there is also a responsibility on us as administrators to make sure we understand our actions and don’t get blind-sided by a contained database. These built-in features should be your second line of defense, and good practices and being diligent about being aware of what we are doing and the implications our actions should be our first line of defense. It’s on us to secure our databases and servers and the built-in functionality is just a fail-safe.