IT门户网,专注电脑知识与技术及手机硬件信息服务网站!

当前位置:IT门户网 > 电脑笔记本 > 数据恢复 >

SQL Server 灾难恢复之一:DBCC CHECK命令会自动使用已经存在的数据库快照吗?

时间:2021-07-02    来源:IT门户网    人气:

作为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.

相关文章

数据恢复排行榜

更多>>

网络知识排行榜

更多>>

系统教程排行榜

更多>>

微信号