本篇进入数据库灾难恢复第二篇，主要讨论还原包含数据库备份的还原时的保护措施，特别是当你拿到一个包含数据库备份，但没有人告诉你他是一般数据库还是包含数据库时，它本身会是一个什么样的保护措施呢？
还原包含数据库 Restoring a Contained Database：
当我了解到包含性数据库，我第一个担心的问题是，作为一个dba如何阻止那些没有专业知识的人来访问数据库呢？还好，已经有三个保护层面了。
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.
如果要查看是否开启了服务器级别的包含选项，也可以查询sys.configurations这个系统表，用它的好处就是可以自动化脚本，如果要修改，还是需要用sp_configure。
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.
这个信息告诉你需要先启用服务器级别包含选项才能还原，对于DBA来说，这是一个警告，是否启用这个包含选项由你自己决定，并不是看到这样的信息就一定要去启用，因为可能会影响服务器或数据库的安全。
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.
总结 Summary：
三层保护按我个人理解应该就是：服务器级别的contained启用、RESTORE HEADERONLY可以确认是否为包含数据库、禁用时如果用Reconfigure，那么存在包含库就会有警告。作为DBA来讲，有责任理清这些，DBA是应该是第一道防护，其次才依靠这三个防护。
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.
