31 Days of Disaster Recovery

Second Layer of Protection

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.

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

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.

Day 3: Determining Files to Restore Database

Welcome back for day 3 of my month-long series on Disaster Recovery. For today’s post, I want to address something that, in my experience, has really flummoxed a lot of people who find themselves unprepared to handle a disaster scenario. Determining exactly which files you should restore.

Test Your Restores

Everyone on your team who might be called upon to perform a restore should be performing regular test runs of restoring the backups. It’s this lack of testing that leads to fumbling around trying to figure out what files to restore. Well, whether you’ve practiced or not, you might like a little help to figure out exactly what files are required to restore the database to its most current point. Thus I give you my RestoreScripter script. The script traverses the backup information in the backup tracking tables in the msdb database.

The script automatically handles many of the possible pitfalls that you might experience and gives you the details you need to quickly script out the restores. For example, records with the same “RestoreOrder” are multiple files for the same backup and should be scripted together in a single restore command.