通过SQL Server维护计划的数据库备份任务

Backup strategies should be planned and documented in advance. It’s important to identify and classify areas of the database management system by importance and the corruption risk level. Also, any database backup strategy should be tested and verified in appropriate environment and time scenarios – a failed backup process is equal to no backup process

备份策略应事先计划并形成文件。 根据重要性和损坏风险级别来识别和分类数据库管理系统的区域非常重要。 此外，任何数据库备份策略都应在适当的环境和时间场景中进行测试和验证-失败的备份过程等于没有备份过程

SQL Server provides the Maintenance Plans feature via SQL Server Management Studio which makes execution of backup strategies easy and automated. In the SQL Server Maintenance Plans – benefits, features, and functionalities article we described general characteristics, methods, and properties that can be used to automate common database maintenance tasks. In this article, we’ll focus on the Back Up Database task

SQL Server通过SQL Server Management Studio提供了维护计划功能，该功能使备份策略的执行变得容易且自动化。 在“ SQL Server维护计划–好处，功能和功能”一文中，我们描述了可用于自动执行常见数据库维护任务的一般特征，方法和属性。 在本文中，我们将重点介绍“备份数据库”任务

There are two methods to create a SQL Server maintenance plan with the Back Up Database task within – a manual and via the Maintenance Plan Wizard feature. Note that one maintenance plan can hold a number of additional tasks (e.g. Check Database Integrity, Shrink Database, etc.) at the same time, in appropriate execution order, if required

可以使用两种方法通过手册中的“备份数据库”任务来创建SQL Server维护计划-手册以及通过维护计划向导功能。 请注意，如果需要，一个维护计划可以按适当的执行顺序同时执行许多其他任务（例如，检查数据库完整性，收缩数据库等）。

手动创建数据库备份任务 (Create a database back up task manually)

To create a maintenance plan in SQL Server Management Studio with appropriate database back up task:

要在SQL Server Management Studio中使用适当的数据库备份任务创建维护计划，请执行以下操作：

1. Object Explorer tree, using the context menu of the 对象资源管理器”树中，使用“ Maintenance Plans folder, select the 维护计划”文件夹的上下文菜单，选择“ New Maintenance Plan option 新建维护计划”选项
2. Type in the name of new maintenance plan (e.g. BackupMaintenancePlan)
   输入新维护计划的名称（例如BackupMaintenancePlan）

3. Using the Maintenance Plan Tasks toolbox, add the Back Up Database Task item (task) to the BackupMaintenancePlan design surface

   使用维护计划任务工具箱，将“ 备份数据库任务”项（任务）添加到BackupMaintenancePlan设计图面

   

4. Once added, the backup task is set with default values, such as the BackupAction (Database), BackupDeviceType (File), UseExpiration (False), and so on

   添加后，将使用默认值设置备份任务，例如BackupAction （数据库）， BackupDeviceType （文件）， UseExpiration （False）等。

   There are more than 60 properties on the back up task Properties pane that can be used for defining and fine-tuning the task. Using the Properties pane set the properties per your backup strategy

   备份任务“属性”窗格上有60多个属性，可用于定义和微调任务。 使用“ 属性”窗格根据您的备份策略设置属性

   

5. Additionally, use the Edit option of the Back Up Database Task item on the design surface to open the Back Up Database Task dialog

   此外，使用设计图面上“备份数据库任务”项目的“ 编辑”选项打开“ 备份数据库任务”对话框。

   

   The dialog provides a more convenient method for configuring the backup task properties than the Properties pane, but not all of the properties can be set via the dialog (e.g. the TimeOut, or ObjectTypeSelection property)

   与“ 属性”窗格相比，该对话框提供了一种用于配置备份任务属性的更方便的方法，但是并非可以通过对话框设置所有属性（例如， TimeOut或ObjectTypeSelection属性）

6. If needed, use the steps 3 to 5 to add additional backups or other task types to the maintenance plan
   如果需要，请使用步骤3至5将其他备份或其他任务类型添加到维护计划中
7. Subplan Schedule option to set the schedule for the task(s). The option will open the standard 子计划计划”选项可以设置任务的计划。 该选项将打开SQL Server Jobs功能所使用的标准“ New Job Schedule dialog, as used by the SQL Server Jobs feature. Additionally, use the New Job Schedule”对话框。 此外，使用“ Add Subplan option to add subplans to the maintenance plan in order to group maintenance tasks per schedule time添加子计划”选项可将子计划添加到维护计划中，以便按计划时间将维护任务分组
8. Jobs sub node of the SQL Server代理节点的SQL Server Agent node. You can use the job to set additional actions, such as alerts and notifications (e.g. via email)Jobs子节点中自动创建相应的作业（BackupMaintenancePlan.Subplan_1）。 您可以使用该作业设置其他操作，例如警报和通知（例如，通过电子邮件）

使用向导创建数据库备份计划 (Create a database back up plan using the wizard)

SQL Server Management Studio provides an additional wizard-like method to create a database back up plan. This method is more convenient than the manual one, but is provides less fine-tuning options. However, if your backup strategy doesn’t require additional tuning, it might be the right solution

SQL Server Management Studio提供了另一种类似于向导的方法来创建数据库备份计划。 此方法比手动方法更方便，但提供的微调选项较少。 但是，如果您的备份策略不需要其他调整，则可能是正确的解决方案

To create a maintenance plan with an appropriate database back up task via the wizard:

通过向导使用适当的数据库备份任务创建维护计划：

1. Object Explorer tree, select the 对象资源管理器”树中，从“维护计划”文件夹的上下文菜单中选择“ New Maintenance Plan option from the context menu of the Maintenance Plans folder新建维护计划”选项。

2. The Select Plan Properties dialog will open and provide initial settings, such as a name and schedule options

   “ 选择计划属性”对话框将打开并提供初始设置，例如名称和计划选项

   

3. In the Select Maintenance Tasks dialog, select the required back up database tasks (Full, Differential, or Transactional Log). Of course, any additional maintenance task can be selected too

   在“ 选择维护任务”对话框中，选择所需的备份数据库任务（“完整”，“差异”或“事务性”日志）。 当然，也可以选择任何其他维护任务

   

4. Using the Select Maintenance Task Order dialog, set up the execution priorities of the tasks
5. 使用“ 选择维护任务顺序”对话框，设置任务的执行优先级
6. Depending on the selected tasks, the wizard will continue with appropriate dialogs (e.g. for the Back Up Database task, the wizard will provide the same dialog as described in the Create a backup task manually section)
   根据选择的任务，向导将继续显示适当的对话框（例如，对于“备份数据库”任务，向导将提供与“手动创建备份任务”部分中所述的对话框相同的对话框）

7. After the wizard is finished, a new maintenance plan is created with the appropriate subplan(s) and job(s). Note that if the Separate schedules for each task option is selected in the Select Plan Properties dialog, multiple subplans and corresponding jobs will be created when the wizard is completed

   向导完成后，将创建一个新的维护计划，其中包含相应的子计划和作业。 请注意，如果在“ 选择计划属性”对话框中选择了“ 每个任务的单独计划”选项，则向导完成后将创建多个子计划和相应的作业

   Once created, the database maintenance plan can be modified using the previously described manual method – the wizard can be used only to create, not to modify existing plans

   创建数据库维护计划后，可以使用先前描述的手动方法进行修改–该向导只能用于创建，而不能用于修改现有计划

Although the SQL Server maintenance back up plans provide viable advantages, such as GUI and native schedule support, there are several disadvantages that DBAs should be aware of. The Maintenance Plans feature (prior to SQL Server 2012 version) uses the sqlmaint utility instead of native SQL Server backup operations. Also, it’s required to maintain both database back up maintenance plans and corresponding jobs in order to preserve consistency and to prevent back up process collisions. Moreover, there is no collision detection and a task execution will fail if concurrent tasks are scheduled on the same database(s) at the same time. In order to avoid such scenarios, it’s advisable to test execution of maintenance plans before they are left in charge of your backup strategy

尽管SQL Server维护备份计划提供了可行的优势，例如GUI和本机计划支持，但DBA应该意识到一些缺点。 维护计划功能（SQL Server 2012版本之前的版本）使用sqlmaint实用程序而不是本机SQL Server备份操作。 另外，还需要维护数据库备份维护计划和相应的作业，以保持一致性并防止备份过程冲突。 此外，没有碰撞检测，并且如果同时在同一数据库上调度并发任务，则任务执行将失败。 为了避免这种情况，建议在维护计划由您负责备份策略之前对其进行测试

翻译自: https://www.sqlshack.com/database-back-up-task-via-sql-server-maintenance-plans/