By Robert L Davis, originally posted on www.sqlsoldier.com
Back in January, 2013, I started a blog series dubbed 31 Days of Disaster Recovery. I posted 31 blog posts (though it took longer than 31 days) covering a variety of disaster recovery topics such as backups, restore, corruption, best practices, etc. I have decided to make the blog post series into a downloadable eBook for easy consumption.
Day 1: Does DBCC Automatically Use Existing Snapshot?
Welcome to my series on Disaster Recovery. I will spend the entire month of January focusing on all things related to disaster recovery including topics like corruption, data integrity, data loss, DBCC commands, and more.
For my first post of this month, I want to take a look at the myth that the DBCC CHECK commands will automatically use an existing database snapshot if one exists for the database. This is a myth I believed myself at one time and told to others. This was done to prove it to myself as much as to anyone else. This is an attempt to prove definitively that the DBCC CHECK commands will not use an existing snapshot for a database.
Does DBCC Automatically Use Existing Snapshot?
I was doing some bigger investigations into DBCC CHECK commands (post to come) and was looking for a way to see that hidden snapshot the DBCC CHECK commands create and use. The snapshots are not visible in sys.databases, sys.master_files, nor any other system catalog that I could find. Additionally, the snapshot creation does not trigger server level events for a DDL trigger nor the database create or database start events for SQL Trace or Extended Events.
I found it in the databases_dbcc_logical_scan event via Extended Events. The event reports database_id of the database the command was run against as well as the database_id of the database where the action is actually occurring. If the action database is the hidden snapshot, the database_id will not show up in sys.databases, but the DB_NAME() function will return the name of the source database. I set up an Extended Events session to capture this event with the columns database_id and database_name. I use the ring buffer target because I don’t intend to retain any of this data. This session will not be active yet.
CREATE EVENT SESSION [TestSnap] ON SERVER
ADD EVENT sqlserver.databases_dbcc_logical_scan(
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=30 SECONDS,
Then I create a snapshot of the AdventureWorksDW2012 database called AWSnap so there is an existing snapshot.
Now, using Object Explore in SQL Server Management Studio (SSMS), I start the Extended Events session (expand Management -> expand Extended Events -> right-click on the session -> click Start Session). Then I right-click on the session and click on Watch Live Data. This allows me to see the events in real-time while DBCC CHECKDB is running against the AdventureWorksDW2012 database. Next I open a new query window and run DBCC CHECKDB.
We can see by looking at any one entry in the Extended Events session that the database_id and database_id (Action) are different database IDs. If you query sys.databases, you will see that database_id of 6 is the AdventureWorksDW2012 database and there is no entry for the database_id (Action). If we check the action database_id with the DB_NAME() function while the CHECKDB is running, it will return AdventureWorksDW2012, but after DBCC completes, it will return NULL.
DBCC Scan Output
For the next part of the test, I close out the Live Data window and reopen it so that it is all clear again. Then I run DBCC CHECKDB against the snapshot AWSnap. checking the Live Data window, we see that the database_id and database_id (Action) are now the same value. If you query sys.databases, you will see that the value for both maps to the snapshot AWSnap.
DBCC Scan Output
As I have shown here, if you want to control the snapshot that DBCC uses, you need to specifically run DBCC on the snapshot itself. It won’t detect and use the snapshot just because one exists. The reason why this is important to understand will become apparent in a later post. So be sure to check back and see how I build on this information.
Welcome to day 2 of my month-long series on Disaster Recovery. For today’s post, I want to talk a little bit about restoring backups of contained databases. In particular, what protections are in place in case you are given a backup of a contained database to restore without being told it has containment enabled.
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
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.
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.
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.
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: