使用维护计划刷新SQL数据库

In this article, I am going to explain how we can refresh a SQL Database using the Maintenance plan. To demonstrate the process, I have configured two virtual machines. I have installed SQL Server 2016 on both. To refresh the SQL database on destination database server, we will generate a copy-only backup of the SQL database on source database server. I have created a network share directory. Both the source and destination server have the read-write permission on it. The backup file will be copied on a shared directory. Once the database is restored to the destination, we will delete the file from the network share directory.

在本文中，我将解释如何使用维护计划来刷新SQL数据库。 为了演示该过程，我配置了两个虚拟机。 我在两者上都安装了SQL Server 2016。 为了刷新目标数据库服务器上SQL数据库，我们将在源数据库服务器上生成SQL数据库的仅复制备份。 我已经创建了一个网络共享目录。 源服务器和目标服务器都对其具有读写权限。 备份文件将被复制到共享目录中。 一旦数据库恢复到目标位置，我们将从网络共享目录中删除该文件。

Details are as following:

详细信息如下：

Host Name	Role	IP Address	Note
SQL01.DC.Local	Source database server	192.168.0.132	Source Database: AdventureWorks2017
SQL02.Dc.Local	Destination database server	192.168.0.133	Destination Database: AdventureWorks2017
Domain.Dc.Local	Network share	192.168.0.100	Network Share: \\Domain\Backups

主机名	角色	IP地址	注意
SQL01.DC.Local	源数据库服务器	192.168.0.132	源数据库： AdventureWorks2017
SQL02.Dc.Local	目标数据库服务器	192.168.0.133	目标数据库： AdventureWorks2017
Domain.Dc.Local	网络共享	192.168.0.100	网络共享： \\ Domain \ Backups

First, let us create a database maintenance plan:

首先，让我们创建一个数据库维护计划：

创建数据库维护计划以刷新SQL数据库 (Create a database maintenance plan to refresh the SQL database)

We will create a maintenance plan on SQL01.Dc.Local server. First, connect to the Database server → launch SQL Server Management Studio → Connect to the Database instance (SQL01) → Expand Database Engine → Expand Management → Right-click on Maintenance Plan → Select New Maintenance plan. See the following image:

我们将在SQL01.Dc.Local服务器上创建维护计划。 首先，连接到数据库服务器→启动SQL Server Management Studio →连接到数据库实例 （ SQL01 ）→展开数据库引擎 →展开管理 →右键单击维护计划 →选择新维护计划 。 见下图：

In the New Maintenance Plan dialog box, provide the desired name of the maintenance plan, and click OK.

在“ 新建维护计划”对话框中，提供所需的维护计划名称，然后单击“ 确定” 。

Database Maintenance plan designer opens. Now, we will refresh the AdventureWorks2017 database of SQL02.DC.Local using the backup of the AdventureWorks2017 database of SQL01.DC.Local server. First, we must create a new connection that can be used to connect to the SQL02.DC.Local server and restore the database on it. To do that, click on Manage connections. See the following image:

数据库维护计划设计器打开。 现在，我们将刷新使用SQL01.DC.Local服务器的AdventureWorks2017数据库的备份SQL02.DC.Local的AdventureWorks2017数据库。 首先，我们必须创建一个新连接，该连接可用于连接到SQL02.DC.Local服务器并在其上还原数据库。 为此，点击管理连接 。 见下图：

Dialog box Manage Connection opens. Click on Add. See the following image:

将打开“ 管理连接”对话框。 点击添加 。 见下图：

In dialog box Connection Properties opens, enter the desired connection name in Connection name text box, enter the hostname in select or enter a server name textbox. We will use SQL Server authentication, hence choose the Use a specific username and password option and enter the username and password. Click OK. See the following image:

在“ 连接属性”对话框中，在“ 连接名称”文本框中输入所需的连接名称，在“选择”中输入主机名或输入服务器名称文本框。 我们将使用SQL Server身份验证，因此选择“ 使用特定的用户名和密码”选项，然后输入用户名和密码。 单击确定 。 见下图：

Once the connection string is configured, let us configure the maintenance plan.

配置了连接字符串后，让我们配置维护计划。

We will generate a backup using T-SQL Query. We can run queries on SQL Database by using the Execute T-SQL Statement task. To do that, drag and drop the Execute T-SQL Statement Task from the database maintenance plan toolbox in maintenance plan designer. See the following image:

我们将使用T-SQL查询生成备份。 我们可以使用“ 执行T-SQL语句”任务在SQL数据库上运行查询。 为此，请从维护计划设计器中的数据库维护计划工具箱中拖放“ 执行T-SQL语句任务” 。 见下图：

Double-click on the Execute T-SQL Statement task. We are generating a backup of the AdventureWorks2016, which I have restored locally hence choose Local server connection from connection drop-down box. In the T-SQL Statement text box, enter the following T-SQL query. The query will generate a backup of the database and copy it to the network location:

双击执行T-SQL语句任务 。 我们正在生成AdventureWorks2016的备份，该备份已在本地还原，因此请从“ 连接”下拉框中选择“ 本地服务器连接 ”。 在“ T-SQL语句”文本框中，输入以下T-SQL查询。 该查询将生成数据库的备份并将其复制到网络位置：

use master
go
Backup database AdventureWorks2017 to disk = '\\Domain\Backup\AdventureWorks2017_ProductionCopy.bak' with copy_only, compression, append, stats = 5

See the following image:

见下图：

Click OK to close the window. As mentioned, we will restore the database on SQL02.DC.Local server. Before we restore the database, we must drop the existing database but, first, we will change the state of SQL database from multi_user to single_user to do that, we will use the following query:

单击“ 确定”关闭窗口。 如前所述，我们将在SQL02.DC.Local服务器上还原数据库。 之前我们恢复数据库，我们必须删除现有的数据库，但是，第一，我们将SQL数据库的状态从MULTI_USER更改为SINGLE要做到这一点，我们将使用下面的查询：

USE [master]
GO
Alter database [AdventureWorks2017] set  single_user with rollback immediate
Go

To drop the database, we will use the following query:

要删除数据库，我们将使用以下查询：

Drop database [AdventureWorks2017]
Go

To restore the database, we will use the following query.

要还原数据库，我们将使用以下查询。

USE [master]
RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'\\Domain\Backups\AdventureWorks2017_ProductionCopy.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Following is the entire code block:

以下是整个代码块：

USE [master]
GO
Alter database [AdventureWorks2017] set  single_user with rollback immediate
Go
Drop database [AdventureWorks2017]
Go
USE [master]
RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'\\Domain\Backups\AdventureWorks2017_ProductionCopy.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Now, drag and drop the Execute T-SQL Statement Task, choose SQL02.DC.Local from connection drop-down box, and enter above code block and click OK. See the following image:

现在，拖放Execute T-SQL Statement Task，从连接下拉框中选择SQL02.DC.Local ，然后输入上面的代码块，然后单击OK 。 见下图：

Once the database is restored, we must drop the backup file that is used to restore the database. To do that, we must use the Maintenance cleanup task. Drag and drop the maintenance cleanup task from toolbox to maintenance plan designer. See the following image:

恢复数据库后，我们必须删除用于恢复数据库的备份文件。 为此，我们必须使用维护清理任务 。 将维护清理任务从工具箱拖放到维护计划设计器。 见下图：

To configure the Maintenance cleanup task, double click on it. Now, we want to delete a specific backup file located on the network share, so select the Backup files option, choose to Delete a specific file option, and provide the location of the backup file in File name textbox. We are not using any backup retention policy hence uncheck the Delete files based on the age of the file at task run-time option. Once all settings are completed, click OK to close the window. See the following image:

要配置维护清理任务 ，请双击它。 现在，我们要删除网络共享上的特定备份文件，因此选择“ 备份文件”选项，选择“ 删除特定文件”选项，然后在“ 文件名”文本框中提供备份文件的位置。 我们没有使用任何备份保留策略，因此请取消选中“ 基于任务运行时文件的年龄删除文件”选项。 完成所有设置后，单击“ 确定”关闭窗口。 见下图：

Once the entire maintenance plan designed, it looks like as following:

一旦设计了整个维护计划，它看起来将如下所示：

Save the maintenance plan.

保存维护计划。

计划数据库刷新维护计划 (Schedule the database refresh maintenance plan)

Now, to schedule the maintenance plan, click on the calendar icon on top of the maintenance plan designer. See the following image:

现在，要计划维护计划，请单击维护计划设计器顶部的日历图标。 见下图：

A dialog box opens. As mentioned, the maintenance plan should refresh the database every Sunday at 12:00 AM. The schedule can be configured as follows:

打开一个对话框。 如前所述，维护计划应在每个星期日的12:00 AM刷新数据库。 时间表可以配置如下：

1. Schedule type: Choose 计划类型：从“ Recurring from 计划类型”下拉框中选择“ Schedule type drop-down box 重复发生 ”
2. Frequency: Choose 频率：从Weekly from 发生次数中选择Occurs drop-down box and select 每周一次 ，然后从天数列表中选择Sunday from the list of days 星期日
3. Time: Choose 时间：选择Occurs once at and enter 一次发生一次，然后在时间文本框中输入12:00:00 AM in the time text box 12:00:00 AM

Once the schedule is configured, click OK to save the schedule. See the following image:

计划表配置完成后，单击“ 确定”保存计划表。 见下图：

Save the maintenance plan.

保存维护计划。

测试数据库刷新维护计划 (Test the database refresh maintenance plan)

To test the job, right-click on the maintenance plan and click on Execute. The execution process maintenance plan will be started. Once the maintenance plan completes successfully, it looks like the following image:

要测试作业，请右键单击维护计划，然后单击执行 。 执行过程维护计划将开始。 维护计划成功完成后，将如下图所示：

摘要 (Summary)

In this article, I have explained how we can use the SQL Server maintenance plan to refresh a SQL database.

在本文中，我已经解释了如何使用SQL Server维护计划来刷新SQL数据库。

翻译自: https://www.sqlshack.com/refresh-a-sql-database-using-the-maintenance-plan/