作为MS SQL Server数据库恢复这个系列的第一篇文章，我们看一下如果一个数据库存在快照数据库，那么当执行DBCC CHECK命令时，是否会自动使用已存在的快照数据库呢？我一直认为是不会的，并且也这样告诉其它人。为了证明给我自己以及其它人，本篇将尝试最终去证明DBCC CHECK命令将不会使用已存在的数据库快照。

For my first post of this MS SQL Server Disaster Recovery, 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.

执行DBCC CHECK命令时是否会自动使用已存在的快照数据库 ?

我做了大量DBCC CHECK命令的调查试图找到办法查看DBCC CHECK命令是否有隐示的创建和使用数据库快照，最终发现快照没有显示在sys.databases, sys.master_file以及其它的系统目录中，另外，数据库快照的创建不会触发服务器级别的事件也不会触发库级别的事件以及SQL跟踪和扩展事件。

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.

最终，我发现通过扩展示件的databases_dbcc_logical_scan事件可以看到它，这个事件会返回当前正针对某个数据库运行的数据库的ID以及实际正在哪个数据库上操作的数据库ID，当前实际操作的数据库是一个隐藏的数据为快照，它的database_id不会显示在sys.databases,而且DB_NAME()函数会返回它的源数据库的名称，我设置了一个扩展事件会话来收集databases_dbcc_logical_scan事件，它含有database_id和database_name列。我使用ring buffer target因为我并不打算保留任何数据，执行下面的脚本，它暂时还未激活。

I found it in the [i]databases_dbcc_logical_scan[/i] 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(
    ACTION(sqlserver.database_id,
        sqlserver.database_name)) 
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB);

然后为AdventureWorks2012数据库创建一个快照库AWSnap，这样我们就有了一个已存在的数据库和它的快照库。

Then I create a snapshot of the AdventureWorksDW2012 database called AWSnap so there is an existing snapshot.

CREATE DATABASE AWSnap
    ON (NAME = N'AdventureWorks2012_Data',
        FILENAME = N'D:\SQL2012\SNP\AdventureWorks2012_Data.ndf')
        AS SNAPSHOT OF AdventureWorks2012;

现在启用扩展事件会话，如下图点击Start Session，然后再点击Watch Live Data，这样当对AdventureWorks2012数据库运行DBCC CHECKDB时就能实时看到事件内容，接下来我们打开一个查询窗口运行DBCC CHECKDB。

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.

从下图中我们可以看到每一个记录中，database_id和database_id(action)都是不同的，如果你查询sys.databases那么没有database_id(action)的ID记录，如果我们用DB_NAME(7)查数据库名称，当DBCC CHECKDB正在运行时，它返回AdventureWorks2012，当DBCC CHECKDB运行完后，它返回NULL.

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.

接下来我们做另一个测试，先关掉刚才的Live Data 窗口并重新打开一个新的，以保证全部清空记录，再对AWSnap库运行DBCC CHECKDB，我们可以看到database_id和database_id(action)现在是相同的值，如果查询sys.databases能够看到它的值对应到数据库AWSnap.

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 CHECKDB使用已存在的数据库快照，需要特别指定快照数据库，否则DBCC CHECKDB不会自动检测并使用已存在的快照数据库。

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.

本文系转载，如有侵犯版权问题，请通知。我们立即删除。

转载请注明：成都千喜数据恢复中心 » SQL Server 灾难恢复之一：DBCC CHECK命令会自动使用已经存在的数据库快照吗？