如何自动执行SQL Server数据库还原

介绍 ( Introduction )

A few days back I encountered an interesting challenge. The client wanted to have copies of the nightly backups of the transactional databases restored on a warehouse server, to be utilized to update the warehouse.

几天前，我遇到了一个有趣的挑战。 客户希望在仓库服务器上恢复事务数据库夜间备份的副本，以用于更新仓库。

The over all process

整个过程

Prior to the pushing the daily backup to the warehouse server, the previous days restore is deleted. The important point being that the “SQLShackFinancial” database is no longer present on the warehouse server. Having been deleted, downloading of the backup file begins and the restore of the current backup version begins. Normal warehouse processing then ensues and so the cycle continues.

在将每日备份推送到仓库服务器之前，将删除前几天的还原。 重要的一点是，仓库服务器上不再存在“ SQLShackFinancial”数据库。 删除后，将开始下载备份文件，并开始还原当前备份版本。 随后将进行正常的仓库处理，因此循环继续进行。

The interesting part of the challenge was to automate the restore process and to have the SQL Server Agent run the job each morning at 3:00 AM. This is what we shall be looking at in today’s “get together”.

挑战中有趣的部分是使还原过程自动化，并使SQL Server代理每天早上3:00 AM运行该作业。 这就是我们今天在“聚在一起”中要看的东西。

Let’s get started.

让我们开始吧。

入门 ( Getting Started )

To begin, we require a bit of knowledge of the backup that we have been charged to restore. In our case, it is the “SQLShackFinancial.bak” backup and the backup has been placed at the following location.

首先，我们需要了解一些我们需要还原的备份知识。 在我们的例子中，它是“ SQLShackFinancial.bak”备份，并且备份已放置在以下位置。

We begin by opening SQL Server Management Studio and opening a new query.

我们首先打开SQL Server Management Studio并打开一个新查询。

We create a small temporary table which will hold the incoming backup’s properties and respective values, thus insuring that the restored database is exactly similar to the copy on the production server. We also create a variable called @path which will contain the path to the backup (see below).

我们创建一个小的临时表，该表将保存传入备份的属性和各自的值，从而确保还原的数据库与生产服务器上的副本完全相似。 我们还创建了一个名为@path的变量，它将包含备份的路径（请参见下文）。

I have taken the liberty of hardwiring this path to make it easier to grasp, however in a real production environment, this would be set by an environmental variable or the like.

我采取了自由使用此路径的自由方式，以使其更容易掌握，但是在实际的生产环境中，这将由环境变量等来设置。

As a “sanity check”, in SQL Server Management Studio and PRIOR to pushing the latest backup down to our server, we bring up the database properties window for the “SQLShackFinancial” database. We note that there is one primary file with two secondary files and finally the log file (see above). We now delete the database as the most current version of the database will be restored from the current backup.

作为“健全性检查”，在SQL Server Management Studio中以及在将最新备份推送到我们的服务器之前，我们打开“ SQLShackFinancial”数据库的数据库属性窗口。 我们注意到，有一个主文件和两个辅助文件，最后是日志文件（请参见上文）。 现在，我们删除数据库，因为将从当前备份中还原数据库的最新版本。

Meanwhile back in the query that we are constructing, we can execute the code that we have thus far and see the contents of the backup file (see below).

同时，在我们正在构造的查询中，我们可以执行到目前为止的代码，并查看 备份文件 的 内容 （见下文）。

We note that the statistics are fairly similar.

我们注意到统计数据非常相似。

The one important point to note is that the

需要注意的重要一点是

command first lists the primary data file, then the secondary data files and lastly the log file is listed.

命令首先列出主要数据文件，然后列出次要数据文件，最后列出日志文件。

It is this premise that we base the rest of this exercise upon, and the reasons for which we shall see in due course.

以此为前提，我们将在本练习的其余部分以及我们将在适当时候看到的原因作为基础。

We now declare a few variables and count the number of rows in #tmp. This is a critical activity as we wish to track the rows one by one, as we iterate through the rows in the temporary table. Note that @counter is set to 1.

现在，我们声明一些变量并计算#tmp中的行数。 这是一项至关重要的活动，因为我们希望在临时表中的行之间进行迭代，从而逐行跟踪行。 请注意，@ counter设置为1。

As we note in the screen dump above, that we have four rows.

正如我们在上面的屏幕转储中所指出的，我们有四行。

At this point in time, we are going to become “SQL Server Outlaws” and real naughty folks as we are going to do a “no-no” and utilize a cursor. As the source data comes from a temporary table, we shall not lock up any physical tables. I therefore have no qualms in utilizing the cursor.

在这个时候，我们将成为“ SQL Server Outlaws”和真正的顽皮人，因为我们要“不行”并利用游标。 由于源数据来自临时表，因此我们不会锁定任何物理表。 因此，我在使用游标方面没有任何疑虑。

We continue by declaring our cursor (see above highlighted in blue).

我们继续声明光标（请参见上面以蓝色突出显示的光标）。

Opening the cursor, we set our @RestoreString variable as follows:

打开游标，我们将@RestoreString变量设置如下：

We now iterate our way through the rows within the temporary table, utilizing the cursor code shown below:

现在，我们使用下面显示的游标代码遍历临时表中的行：

This code requires some explanation. While there are still rows to be read within the temporary table we shall continue with the outer “while loop”. When there are records to be read, the value of @@FETCH_STATUS will be 0. After the last record is read, @@FETCH_STATUS returns -1 which will cause the code to break out of the loop. As long as there are no missing records, the @@FETCH_STATUS should never be -2 thus the second loop is a “safety valve” and is totally dependent upon the outer loop.

此代码需要一些解释。 尽管临时表中仍有行要读取，我们将继续外部的“ while循环”。 当有记录要读取时，@@ FETCH_STATUS的值将为0。读取最后一条记录后，@@ FETCH_STATUS返回-1，这将导致代码脱离循环。 只要没有丢失的记录，@@ FETCH_STATUS都不应为-2，因此第二个循环是“安全阀”，并且完全取决于外部循环。

Details of the return codes may be found at the following URL:

返回代码的详细信息可以在以下URL中找到：

https://msdn.microsoft.com/en-us/library/ms187308.aspx

https://msdn.microsoft.com/zh-CN/library/ms187308.aspx

Above I had mentioned that routinely the way that the physical files are rendered when utilizing the “restore file list only” command are that the first file pulled is the primary data file, then come the secondary files and last the log file. The fact that this is so makes our task all the easier.

上面我已经提到，通常使用“仅还原文件列表”命令时呈现物理文件的方式是，首先拉出的文件是主数据文件，然后是次要文件，最后是日志文件。 这样的事实使我们的任务更加容易。

Looking at the code snippet below (extracted from the code above), when the counter is “1” then we create the physical primary file name from the logical file name plus the hardwired path and give the file name an extension of “mdf”. All of this is concatenated to @ReportString (see code in green below).

查看下面的代码片段（从上面的代码中提取），当计数器为“ 1”时，我们从逻辑文件名加上硬连线路径创建物理主文件名，并将文件名扩展为“ mdf”。 所有这些都连接到@ReportString（请参见下面绿色的代码）。

When the value of @counter is greater than one BUT less than the total count of rows then we KNOW that we are dealing with secondary data files and perform the same naming process however this time the file name extension will be “.ndf” (see the code in purple below):

当@counter的值比行总数少一个BUT时，我们知道我们正在处理辅助数据文件并执行相同的命名过程，但是这次文件扩展名将为“ .ndf”（请参见下面的紫色代码）：

When the value of @counter is equal to the total count of rows then we KNOW that we are dealing the log file see the code in brown below:

当@counter的值等于行的总数时，我们知道正在处理日志文件，请参见下面的棕色代码：

The astute reader will note that regardless of which option, the “case” turns out to be, @counter is incremented by 1 (with each pass) and the next row is retrieved from the cursor.

精明的读者会注意到，无论选择哪个选项，“大小写”都是这样，@ counter每次递增1（每次通过），然后从游标中检索下一行。

Running what we have thus far, we can see the string that we have created by concatenating all the file names (see above).
Our final task is to execute the string and close and de-allocate the cursor. The code that achieves this may be seen below:

运行到此为止，我们可以通过串联所有文件名来查看创建的字符串（请参见上文）。
我们最后的任务是执行字符串，然后关闭并取消分配游标。 实现此目的的代码如下所示：

We set the value of @NRestoreString to the value of @RestoreString.

我们将@NRestoreString的值设置为@RestoreString的值。

“ sp_executesql “ requires the string to be in NVARCHAR() format.
The reader will remember that @NRestoreString is NVARCHAR(max). We could have utilized @NRestoreString right from the outset.

“ sp_executesql ”要求字符串采用NVARCHAR（）格式。
读者将记住@NRestoreString是NVARCHAR（max）。 我们一开始就可以使用@NRestoreString。

Utilizing sp_executesql we now execute @NRestoreString. The results of the execution may be seen below.

现在，使用sp_executesql执行@NRestoreString。 执行结果如下所示。

We note that SQLShackFinancial has been restored (see below and to the left).

我们注意到SQLShackFinancial已恢复（请参见下面和左侧）。

Looking at the location of the physical files, we find that the data files were actually restored to where we had requested (see below).

查看物理文件的位置，我们发现数据文件实际上已还原到我们请求的位置（请参见下文）。

It should be noted at this point that I deliberately restored the database to the directory “SQL Server Data” for this exercise. Normally these files are located in the proper SQL Server 11 Data Directory.

在这一点上应该注意的是，我为此练习故意将数据库还原到“ SQL Server Data”目录中。 通常，这些文件位于正确SQL Server 11数据目录中。

Looking our “SQL Server Data” directory, we note our four files.

在查看“ SQL Server数据”目录时，我们注意到了四个文件。

结论 ( Conclusion )

It is often said that necessity is the mother of invention. Whilst the techniques shown in this code are far from “Rocket Science”, they are techniques that we often never think of. Restoring databases on a cyclical basis is par for the course in many enterprises.

人们常说，必要性是发明之母。 尽管此代码中显示的技术远非“火箭科学”，但它们是我们通常从未想到的技术。 在许多企业中，定期恢复数据库对于这门课程而言是同等重要的。

Thus we come to the end of another “get together”. As a reminder, the code that we worked with in today’s session may be found below in Addenda 1.

因此，我们走到了另一个“聚在一起”的结尾。 提醒一下，我们在今天的会议中使用的代码可以在下面的附录1中找到。

As always, should you have any questions or concerns, please feel free to contact me.

与往常一样，如果您有任何疑问或疑虑，请随时与我联系。

In the interim, happy programming!!

在此期间，编程愉快！！

附录1 ( Addenda 1 )

翻译自: https://www.sqlshack.com/automate-sql-server-database-restores/