c#代码如何连数据库快照

Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.

在我将数据仓库和OLTP副本环境（例如运营数据存储）作为一种数据冗余策略的形式之前，我很早就在使用数据库快照。 在此期间，分析和报告团队被定向到数据库快照作为其开发的数据源。 拥有数据库快照的另一个好处是，很容易知道应该在源代码中进行意外更改，因此可以通过从快照还原源数据库来轻松地还原操作。

However, working with database snapshots is not always a happy affair. I was recently reminded of some of the frustrations that may come up when one is attempting to create database snapshots. One such frustration is trying to understand the meaning of the following error message:

但是，使用数据库快照并不总是一件容易的事。 最近，我想起了尝试创建数据库快照时可能会遇到的一些挫败感。 一种挫败感是试图理解以下错误消息的含义：

Msg 5127, Level 16, State 1, Line 1

All files must be specified for database snapshot creation. Missing the file “SingleFileDB”.

讯息5127，第16级，状态1，第1行

必须指定所有文件才能创建数据库快照。 缺少文件“ SingleFileDB”。

Having setup a test environment to understand the causes that could lead to 5127 error code, I realised that under different scenario, the meaning behind the error code could be interpreted differently – which could be burdensome when you are trying to identify the root cause of the error. In this article, we try to alleviate the frustrations by exploring all causes that could lead to different interpretations of the 5127 error code. We conclude by proposing a stored procedure that could be utilised to create database snapshots without encountering much of the 5127 error code.

设置了测试环境以了解可能导致5127错误代码的原因后，我意识到在不同的情况下，错误代码背后的含义可能会有所不同，这在您尝试确定导致错误代码的根本原因时可能会很麻烦。错误。 在本文中，我们尝试通过探究可能导致5127错误代码的不同解释的所有原因来减轻这种挫败感。 最后，我们提出了一种存储过程，该存储过程可用于创建数据库快照，而不会遇到很多5127错误代码。

案例分析 (CASE STUDY)

Likewise, a proper discussion of this topic is inadequate without illustrating our discussion points by means of examples and screenshots. Therefore, for this discussion, I setup an instance of SQL Server 2012 with self-explanatory databases SingleFileDB (which has a single data file) and MultiFilesDB (which is made up of multiple data files).

同样，在没有通过示例和屏幕截图说明我们的讨论要点的情况下，对此主题的适当讨论也是不够的。 因此，在此讨论中，我设置了一个SQL Server 2012实例，该实例具有不言自明的数据库SingleFileDB （具有单个数据文件）和MultiFilesDB （由多个数据文件组成）。

单一数据文件方案 (Single Data File Scenario)

Figure 1 lists all the files that belong to SingleFileDB database:

图1列出了属于SingleFileDB数据库的所有文件：

Because we only have a single data file linked to SingleFileDB database, its script for creating a database snapshot becomes very simple, as shown in Figure 2:

因为我们只有一个链接到SingleFileDB数据库的数据文件，所以用于创建数据库快照的脚本非常简单， 如图2所示：

However, if we deliberately modify the script in Figure 2 and enter an incorrect data file name (i.e. SingleFileDB1 instead of SingleFileDB) as shown in Figure 3, we receive the 5127 error code.

但是，如果我们故意修改图2中的脚本并输入错误的数据文件名（即SingleFileDB1而不是SingleFileDB ）（如图3所示）， 则会收到5127错误代码。

Thus, in a scenario whereby the source database has a single data file, the 5127 error code can only be interpreted as referring to a mismatch between the provided data file name in the Name parameter (of the script) and that of the source database’s data file name. In order to resolve this, you should refer to the “Missing the file” part of the error message whereby as illustrated in Figure 3, you are given the expected name that should have been provided (in our example, it should have been).

因此，在源数据库只有一个数据文件的情况下， 5127错误代码只能解释为引用（脚本的） Name参数中提供的数据文件名称与源数据库的数据名称之间的不匹配文档名称。 为了解决此问题，您应该参考错误消息的“缺少文件”部分，从而如图3所示 ，为您提供了应提供的预期名称（在我们的示例中，应为该名称）。

多个数据文件方案 (Multiple Data File Scenario)

Unlike a single data file scenario, there are several causes that could lead to 5127 error code whilst attempting to create a snapshot against a source database with multiple data files. One of the causes is similar to the single data file scenario in that it could mean that the provided value in the Name parameter does not match that of the expected data file name, as shown in Figure 4 whereby the provided name of MultiFilesDB1 does not match with the expected value of MultipleFilesDB.

与单个数据文件方案不同，在尝试针对具有多个数据文件的源数据库创建快照时，有多种原因可能导致5127错误代码。 原因之一与单个数据文件方案相似，原因可能是Name参数中提供的值与预期数据文件名的值不匹配， 如图4所示，其中MultiFilesDB1的提供的名称不匹配。具有MultipleFilesDB的期望值。

Another cause of the 5127 error code in a multi-data file scenario is that you may have skipped a data file in your list of data files that make up a given source databases. For instance, in Figure 5 I have intentionally provided an incorrect file name (MDF2TEST instead of MDF2), however, the error message returned only relates to a missing MultiFilesDB file.

在多数据文件情况下5127错误代码的另一个原因是，您可能已跳过组成给定源数据库的数据文件列表中的数据文件。 例如，在图5中，我故意提供了错误的文件名（ MDF2TEST而不是MDF2 ），但是，返回的错误消息仅与缺少的MultiFilesDB文件有关。

This means that when the script was being validated, the query processor realised that I haven’t included a file that should have been part of my create database snapshot script. The reason it picked up on the missing file was by going through the sys.database_files system view. Figure 6 shows the results of the sys.database_files system view in which MultipleFilesDB database is made up of 3 data files and in terms the file_id order, MultipleFilesDB data file was created ahead of the MDF2 file and thus should have been added in my create database snapshot script.

这意味着在验证脚本时，查询处理器意识到我没有包括应该属于我的创建数据库快照脚本一部分的文件。 之所以选择丢失的文件，是因为通过sys.database_files系统视图。 图6显示了sys.database_files系统视图的结果，其中MultipleFilesDB数据库由3个数据文件组成，并且按照file_id顺序， MultipleFilesDB数据文件是在MDF2文件之前创建的，因此应该已将其添加到我的创建数据库中快照脚本。

动态创建SQL Server数据库快照 (Dynamically Create SQL Server Database Snapshots )

One limitation of the 5127 error code is that it doesn’t return a consolidated list of all missing files that should have been included in your create database snapshot script. Thus, I setup a stored procedure (SP) that could be used to address this limitation by dynamically creating database snapshot which guarantees correct capture of data file names and proper listing of all associated files of a source database. The stored procedure can be downloaded . The only requirement for the SP is that you parse source database name parameter.

5127错误代码的一个限制是，它不会返回应该包含在创建数据库快照脚本中的所有丢失文件的合并列表。 因此，我设置了一个存储过程（SP），该存储过程可用于通过动态创建数据库快照来解决此限制，该快照可确保正确捕获数据文件名并正确列出源数据库的所有关联文件。 可以在下载存储过程。 SP的唯一要求是您解析源数据库名称参数。

Although you can go through the SP in detail once you have downloaded it, I felt that I need to bring a few things about the SP into your attention:

尽管您可以在下载SP后详细了解它，但我还是需要引起您注意有关SP的一些事情：

1. As a form of naming convention, all database snapshots created via my SP, will be suffixed with _snap

   作为命名约定的一种形式，通过我的SP创建的所有数据库快照都将带有_snap后缀

2. The dynamically capabilities of the SP are made possible by a T-SQL cursor. Although, the usage of the cursor shouldn’t be resource intensive, nevertheless beware that the SP uses a T-SQL cursor.

   T-SQL游标使SP的动态功能成为可能。 尽管游标的使用不应占用大量资源，但是请注意SP使用T-SQL游标。

3. Finally, because I understand the consequences of incorrectly dropping database objects – in a case of an existing database snapshot, the SP will not drop and recreate such an object instead it skip the creation of the database snapshot and returns a “database snapshot exists” message shown in Figure 7:

   最后，因为我了解不正确删除数据库对象的后果–在现有数据库快照的情况下，SP不会删除并重新创建这样的对象，而是跳过数据库快照的创建并返回“数据库快照存在”消息如图7所示：

   
   Figure 7: Database snapshot exists message
   图7：数据库快照存在消息

结论 (CONCLUSION)

In this article we have attempted to remove the ambiguities of SQL Server 5127 error code by recreating the error within a single data file database scenario as well as in a multiple data files database scenario. It was during the scenarios that we realised that the message is two-fold: it may mean indicate a mismatch between a specified file name and a given source database; it could also mean that you have not listed all the files of source database with a multiple data files. The underlying error with this message is that we need to find a mechanism to dynamically retrieve data files. Thus, a stored procedure – sp_DynamicDBSnapshotCreator – was developed for the sole purpose of eliminating data filenames mismatch and successfully create a database snapshot.

在本文中，我们尝试通过在单个数据文件数据库方案中以及在多个数据文件数据库方案中重新创建错误，消除SQL Server 5127错误代码的歧义。 在这种情况下，我们意识到消息是双重的：它可能意味着表明指定的文件名与给定的源数据库不匹配； 这也可能意味着您没有列出具有多个数据文件的源数据库的所有文件。 此消息的根本错误是我们需要找到一种动态检索数据文件的机制。 因此，开发存储过程sp_DynamicDBSnapshotCreator的唯一目的是消除数据文件名不匹配并成功创建数据库快照。

Download sp_DynamicDBSnapshotCreator scripts

下载sp_DynamicDBSnapshotCreator脚本

参考资料 (References)

翻译自:

c#代码如何连数据库快照