SQL Server disaster recovery: 31 days to 2nd days: includes protection for database backup during restoration

Note:Disaster recovery SeriesOfArticleYesby Robert Davis Written in sqlsoldier,I personally think it is quite good, so according to my own understanding, testing and sorting are not directly translated. If there is any inaccuracy, please correct me.

 

This article introduces the second article on Database disaster recovery. It mainly discusses the protection measures for restoring a database backup, especially when you get a backup that contains a database, but no one tells you whether a database is a general database or contains a database. What kind of protection measures does it take?

Restore included databases:
When I learned about the contained database, my first concern was how a DBA would prevent people without professional knowledge from accessing the database? Fortunately, there are already three protection layers.

The first layer of protection:
Before restoring a contained database, creating a contained database, or having a contained database, the server-level inclusion option must be enabled. If the server-level startup does not include the option, it is completely protected. You can use sp_configure or server attributes in advanced options, but it is not a real advanced option, therefore, you do not need to enable the show advanced options option when modifying or viewing the server-level inclusion options.

If you want to check whether the server-level inclusion option is enabled, you can also query the system table SYS. configurations. The advantage of using this table is that it can be used to automate scripts. If you want to modify it, you still need to use sp_configure.

If a contained database is restored on a server instance without the option enabled, the system will fail and return the following information:

 
MSG 12824, level 16, state 1, line 1The sp_configure value 'ed ined 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.
 

This information tells you that you need to enable the server-level inclusion option to restore. For dBA, this is a warning. Whether to enable this inclusion option is determined by yourself, if you do not see such information, you must enable it because it may affect the security of the server or database.

The "enable or disable include data" command is as follows: note that reconfigure is not reconfigure with override. Here is an important reason, as mentioned later.

--Enable "contained Database Authentication"ExecSp_configure'Contained',1;Reconfigure;--Disable "contained Database Authentication"ExecSp_configure'Contained',0;Reconfigure;
 

Gui:

Layer 2 protection:
If you have enabled the include database option, there will be no warning or prompt when you restore an include database. You can only determine whether the restored database contains a database by yourself. Fortunately, you can know this with a built-in structure. In SQL Server 2012, when you execute restore headeronly, a new column is added, which is containment. When it is 1, it indicates that it contains the database, and vice versa, for example, I perform a check on a database that contains:

Last layer of protection:
Suppose there is such a disaster recovery scenario, you need to restore a database online as soon as possible, but when you restore it to an alternative server, you find that it is a contains database. Now let us assume that you have not realized that this setting has been enabled, and you will think that this database is not used to include users based on your capabilities and documents that can be viewed, assume that there may be some regulations or security policies that cannot include users without the support of specific and valid documents. However, you may not have time to find relevant persons to explain why users are included, now you need to restore the database as soon as possible and observe the security policy.

In this case, you can enable the include database option first, recover the database, and then disable it.

When disabling, an error is reported. We need to use with override, which is actually the last protection. This is why we used reconfigure instead of reconfigure with override.

--Disable "contained Database Authentication"ExecSp_configure'Contained',0;Reconfigure;
 

 
Configuration option 'registry 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 'ed ined Database Authentication 'value to 0 while there are existing contained databases requires a reconfigure with override.
 

--Force disabling of "contained Database Authentication"Reconfigure WithOverride;
 

 
Command (s) completed successfully.
 

 

Summary:
In my personal understanding, layer-3 protection should be: Enable contained at the server level, and enable restore headeronly to check whether it contains the database. If reconfigure is used when it is disabled, there will be a warning when the contained database exists. As a DBA, it is the responsibility to clarify this. DBA should be the first protection, followed by the three protection.

SQL Server disaster recovery: 31 days to 3rd days: Determine which backup files are needed when restoring the database