自动安排SQL Server备份的多种方法

最新推荐文章于 2024-03-01 17:10:44 发布

culuo4781

最新推荐文章于 2024-03-01 17:10:44 发布

阅读量754

点赞数 1

文章标签：数据库 python java mysql 大数据

原文链接：https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/

版权

本文探讨了SQL Server备份的类型，最佳实践和三种自动备份方法：使用SQL Server代理作业、维护计划以及如何验证和恢复备份。强调了备份位置的重要性，自动化备份的必要性，以及定期验证备份完整性的关键性。

摘要由CSDN通过智能技术生成

SQL Server backups are an essential part of every good disaster recovery strategy. That is good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another

SQL Server备份是每个良好灾难恢复策略的重要组成部分。那很好。但目标是设置此类备份以使其轻松运行。在本文中，我们将回顾备份的类型，建议的做法以及用于按计划自动设置SQL Server备份的三种不同方法。注意：这些解决方案也可以相互组合使用

不同类型SQL Server备份 (Different types of SQL Server backups)

另请参阅：了解SQL Server备份类型

Full. This is the most common backup type and it includes everything including objects, system tables data, and transactions that occur during the backup. With a full backup, you can restore your database to the state, when it was backed up. Full backups won’t truncate your transaction log but if your database is in full recovery you. should also consider transaction log backups

已满。这是最常见的备份类型，它包括所有内容，包括对象，系统表数据以及备份期间发生的事务。通过完全备份，您可以将数据库还原到备份时的状态。完全备份不会截断事务日志，但是如果数据库处于完全恢复状态，则可以。还应该考虑事务日志备份

另请参阅： SQL Server 2016完整数据库备份的演练

Differential. This type of backup offers a means to maintain a complete history of your database but without storing redundant data. A differential backup retains data since the last full backup. A differential backup is only useful if used in tandem with a full backup, but allows you to delete/remove previous differential backups as they are redundant

微分。 这种类型的备份提供了一种维护数据库完整历史记录但不存储冗余数据的方法。自上次完整备份以来，差异备份将保留数据。差异备份仅在与完整备份结合使用时才有用，但是由于它们是冗余的，因此您可以删除/删除先前的差异备份

Transaction log. This backup type will backup all of the transactions that have occurred since the last log backup or truncation, then it will truncation the transaction log. This will capture all transaction information, both DML and DDL, that has occurred on the database. With a transaction log backup, you can restore a database to a particular point in time aka point-in-time recovery, like right before a data loss event

交易记录。 此备份类型将备份自上次日志备份或截断以来发生的所有事务，然后将其截断事务日志。这将捕获数据库中发生的所有交易信息，包括DML和DDL。使用事务日志备份，您可以将数据库还原到特定的时间点，也称为时间点恢复，就像发生数据丢失事件之前一样。

另请参阅：了解SQL Server数据库恢复模型

File-group and File: This type of backup is best for larger databases. This type of backup will store all related data in files or file groups (one or more). To use file backups to successfully restore a database, the transaction log also has to be backed up to cover all of the file groups from beginning to end.

文件组和文件 ：这种备份类型最适合大型数据库。这种类型的备份会将所有相关数据存储在文件或文件组（一个或多个）中。要使用文件备份成功还原数据库，还必须备份事务日志以覆盖从头到尾的所有文件组。

另请参阅： SQL Server中的数据库文件组和零碎还原

Copy-only. This backup type is usually used on an ad-hoc basis and as not to disturb and existing process of database backups, since the transaction log copy-backup will ignore the copy backup. Otherwise, this is the same as a full database backup. Copy backups can’t be used for differential backups and transaction log backups

仅复制。 这种备份类型通常是临时使用的，并且不会干扰数据库的现有备份过程，因为事务日志复制备份将忽略复制备份。否则，这与完整数据库备份相同。副本备份不能用于差异备份和事务日志备份

备份最佳做法 (Backup best practices)

位置 (Location)

Separating the location where backups reside from the server where the database, itself, exists is critical, because otherwise, some failures that affect the database might also mitigate your ability to use the backups to recover from it, for example physical drive failure.

将备份驻留的位置与数据库本身所在的服务器分开是至关重要的，因为否则，某些影响数据库的故障可能还会减轻您使用备份从数据库中恢复的能力，例如物理驱动器故障。

Without explicit guidance for the file location as to where to store the backups, SQL Server will use the default database location.

在没有明确的文件位置存储备份位置指导的情况下，SQL Server将使用默认的数据库位置。

Please note that simply changing the default file location, will have no effect on previously saved backup files, it will only determine the location where new backups are stored.

请注意，仅更改默认文件位置不会对以前保存的备份文件产生任何影响，只会确定新备份的存储位置。

自动化和计划备份 (Automating and scheduling backups)

Getting a database successfully backed up, to the file location and with the configuration settings you want is just a first step. Once accomplished, you will want to be able to replicate this process over and over again, automatically and on a schedule. Scheduling automated backups is critical for ensuring database continuity while reducing the manual effort required to achieve this.

将数据库成功备份到文件位置并使用所需的配置设置只是第一步。一旦完成，您将希望能够一次又一次地自动重复该过程。计划自动备份对于确保数据库连续性同时减少实现此任务所需的手动工作至关重要。

Regular database backups not only are a great insurance policy against accidental data loss and other disaster type scenarios, they also provide point-in-store capability (for transaction log backups) and reduced downtime, if you do have to restore (shorter backup windows > less data loss)

常规数据库备份不仅是防止意外数据丢失和其他灾难类型情形的重要保证，而且还提供存储点功能（用于事务日志备份）并减少了停机时间（如果必须还原的话）（较短的备份窗口>减少数据丢失）

How often should I schedule backups? This depends on your business requirements and Recovery Point Objectives (RPO), for example, if your standard is no more than 15 minutes of data can be lost, then then backups need to be scheduled every 15 minutes

我应该多久安排一次备份？这取决于您的业务需求和恢复点目标（RPO），例如，如果您的标准丢失的数据不超过15分钟，则需要每15分钟安排一次备份

Note: If your database is in full recovery mode you can use ApexSQL Log to avoid full restores. ApexSQL Log can isolate and reverse rogue transactions, mitigating the damaged data without having to do a full restore. It can also replay previous transaction to restore data that was lost

注意：如果数据库处于完全恢复模式，则可以使用ApexSQL Log避免完全还原。 ApexSQL Log可以隔离和逆向恶意交易，从而减轻了损坏的数据，而无需进行完全还原。 它还可以重播先前的事务以恢复丢失的数据

You can also use ApexSQL Data Diff to compare the current database to the last database backup (without even restoring it), identifying different/damaged rows and surgically repairing them with a synchronization script, again, without having to restore the backup.

您还可以使用ApexSQL Data Diff将当前数据库与上次数据库备份进行比较（甚至不还原），识别不同/损坏的行，并再次使用同步脚本进行外科修复，而无需还原备份。

恢复和测试备份 (Restoring and testing backups)

A backup is only good if it can be restored successfully and this must be verified, and continuously re-verified to ensure your backup and restore strategy can be executed successfully when needed. Backups must be restored in a test environment and fully verified to ensure they meet all business requirements and various permutations, contingencies and contexts of the company recovery strategy

只有能够成功还原备份并且必须对其进行验证，并不断进行重新验证以确保您的备份和还原策略可以在需要时成功执行，备份才是好的。必须在测试环境中还原备份并进行充分验证，以确保备份满足所有业务需求以及公司恢复策略的各种变更，突发事件和上下文。

When testing backups many variables existing including environment, data, recovery time frames and data loss windows, application downtime and maximum acceptable threshold for data loss

测试备份时，存在许多变量，包括环境，数据，恢复时间范围和数据丢失窗口，应用程序停机时间以及数据丢失的最大可接受阈值

验证备份 (Verifying backups)

SQL Server backup verification includes the following checks

SQL Server备份验证包括以下检查

The backup was created successfully
备份已成功创建
It is currently intact, physically and that all the files not only exist but are also readable
当前它是完整的，并且所有文件不仅存在而且可读
The backup can be restored, when it is needed
可以在需要时还原备份
All the transactions are consistent
所有交易均一致

Note that verifying a backup doesn’t ensure the integrity or completeness of all of the data but if the original backup was created with WITH CHECKSUMS, that allows WITH CHECKSUMS to be used to verify the data, at an aggregate level, to give a reasonable confidence level that no data was lost. Let’s look at a few backup cases below to provide some practical examples

请注意，验证备份并不能确保所有数据的完整性或完整性，但是如果原始备份是使用WITH CHECKSUMS创建的，则可以使用WITH CHECKSUMS来汇总级别地验证数据，以提供合理的数据。没有数据丢失的置信度。让我们看看下面的一些备用案例，以提供一些实际示例

with T-SQL:

使用T-SQL：

When we include the CHECKSUM statutement we can later ensure the integrity of the data, when the backup is complete and written to file. To accomplish this, see the following example:

当我们包括CHECKSUM法规时，当备份完成并写入文件时，我们以后可以确保数据的完整性。为此，请参见以下示例：

BACKUP DATABASE [CurrencyExchange]
TO  DISK = N'G:\DatabaseBackups\CE.bak'
WITH CHECKSUM;

SSMS provides the facility for backup verification using CHECKSUM when creating a backup task

SSMS提供了在创建备份任务时使用CHECKSUM进行备份验证的功能。

Two options are available in SSMS to implement this functionality including Verify backup when finished and Perform checksum before writing to media. These options are meant to bullet-proof your database backup process and identify any failures

SSMS提供了两个选项来实现此功能，包括完成后验证备份 和在写入介质之前执行校验和 。这些选项旨在使您的数据库备份过程更加安全，并确定任何故障

It is important to include this functionality in your automatically scheduled backups as well

在自动计划的备份中也包括此功能很重要

We’ll demonstrate, in this article, how to automatically create and schedule a SQL Server backup using SQL Server Agent jobs and a Maintenance plans

在本文中，我们将演示如何使用SQL Server代理作业和维护计划自动创建和计划SQL Server备份。

使用SQL Server代理作业创建计划的备份 (Creating a scheduled backup using SQL Server Agent jobs)

To schedule an automated backup with a SQL Server Agent job:

要使用SQL Server代理作业安排自动备份，请执行以下操作：

Right click Jobs and select New job from the context menu, in the object explorer panel, in SSMS, under the SQL Server Agent node.

右键单击“ 作业” ，然后从上下文菜单中的对象浏览器面板中的SSMS中，在“ SQL Server代理”节点下选择“ 新建作业” 。
New job dialog 新作业”对话框中输入作业的名称
Click on the New button, under the Steps tab, and create a new backup step by adding a SQL statement

单击“步骤”选项卡下的“新建”按钮，然后通过添加SQL语句来创建新的备份步骤
```
USE CurrencyExchange
GO
BACKUP DATABASE [CurrencyExchange]
TO  DISK = N'G:\DatabaseBackups\CE.bak'
WITH CHECKSUM;
```
Use this next SQL code to create a differential backup

使用此下一个SQL代码创建差异备份
```
USE CurrencyExchange
GO
BACKUP DATABASE [CurrencyExchange]
TO  DISK = N'G:\DatabaseBackups\CE.bak'
WITH CHECKSUM;
 
 
BACKUP DATABASE [CurrencyExchange]
   TO  DISK = N'G:\DatabaseBackups\CE.bak'
   WITH DIFFERENTIAL;
   WITH CHECKSUM;
 
GO
```
For creating a transaction log backup, use this SQL statement

要创建事务日志备份，请使用此SQL语句
```
BACKUP LOG [CurrencyExchange]
   TO  DISK = N'F:\TLogBackups\CE.log';
GO
```
A necessary pre-requisite to creating a transaction log or differential backup is that a full SQL Server database backup must already exist. So you must create a full database backup, if the database has never been backed-up before, before creating a differential.

创建事务日志或差异备份的必要先决条件是完整SQL Server数据库备份必须已经存在。因此，如果以前从未备份过数据库，则必须创建完整的数据库备份，然后再创建差异。

Transaction log and differential backups play well together with full database backups and can be used in tandem. By taking a sophisticated approach to backups you can achieve a high level of database continuity and insurance/protection from data loss while minimizing backup file storage requirements. You can, for example, schedule a full SQL Server backup every 12 hours, but a differential, much more often, say every 4 hours and finally, back up your transaction log every 15 minutes. The key is finding the sweet spot between mitigating potential data loss and storage requirements that is optimal for your organization

事务日志和差异备份与完整数据库备份一起可以很好地发挥作用，并且可以串联使用。通过采用复杂的备份方法，您可以实现高水平的数据库连续性并保证/防止数据丢失，同时最大程度地减少备份文件的存储需求。例如，您可以每12小时安排一次完整SQL Server备份，但是通常每4小时安排一次差异备份，最后每15分钟备份一次事务日志。关键是在减轻潜在数据丢失和存储需求之间找到最佳结合点，这对您的组织来说是最佳的
Click OK to add a step and OK. again, to create a job:

单击确定以添加一个步骤，然后单击确定。再次创建工作：
New Job dialog, under the 计划”选项卡下的“ Schedule tab 新建作业”对话框中单击“新建”。
In the Job Schedule tab, select a recurring frequency and a start date and click OK:

在“ 作业计划”选项卡中，选择重复频率和开始日期，然后单击“确定”：

Right click the job and select Start job at step option, to check a created job in the SSMS Object Explorer pane, under the SQL Server Agent then Jobs node

右键单击该作业，然后选择“在步骤中启动作业”选项，以在“ SSMS对象资源管理器”窗格中的“ SQL Server代理”和“ 作业”节点下检查已创建的作业。

To back up all databases, using SQL Server Agent, for one SQL Server instance there are two approaches; both will require some additional effort. One way is to create an SSIS package using the Backup Database Task option from the SSIS toolbar and create a SQL Server Agent job, then schedule it

要使用SQL Server代理备份所有数据库，对于一个SQL Server实例，有两种方法：两者都需要额外的努力。一种方法是使用SSIS工具栏中的“ 备份数据库任务”选项创建SSIS程序包，并创建一个SQL Server代理作业，然后计划该作业。

The approach is to create a SQL script to backup all databases in the SQL Server Agent Job Step dialog.

该方法是创建一个SQL脚本来备份“ SQL Server代理作业步骤”对话框中的所有数据库。

使用SQL Server维护计划创建计划SQL Server备份 (Creating a scheduled SQL Server backup with SQL Server Maintenance Plans)

You can either use SQL Server Maintenance Plans, manually, to create a scheduled backup or use the Maintenance Plan Wizard.

您可以手动使用SQL Server维护计划来创建计划的备份，也可以使用维护计划向导。

To create a scheduled SQL Server backup task manually:

若要手动创建计划SQL Server备份任务，请执行以下操作：

Click Maintenance Plans, under the Management node in the SSMS Object explorer, and select New Maintenance Plan.

单击SSMS对象浏览器中“管理”节点下的维护计划 ，然后选择新建维护计划 。
Select Back Up Database Task: from the Maintenance Plan Tasks toolbar

从“维护计划任务”工具栏中选择“备份数据库任务” ：

A Check database integrity task option exists, as an option in the Maintenance Plan Wizard, allowing this functionality to be included in your maintenance plan.

作为“维护计划向导”中的一个选项，存在一个“ 检查数据库完整性”任务选项，该功能可以包含在维护计划中。
Click on the newly added plan to configure the backup settings:

单击新添加的计划以配置备份设置：
Use the Sub plan scheduling option, to schedule a SQL Server Agent job
使用“子计划计划”选项来计划SQL Server代理作业
Save and this will create a new job under the SQL Server Agent then Jobs folder. 保存” ，这将在SQL Server代理下创建新作业，然后在“作业”文件夹下创建。

The Maintenance Plan Wizard is easier and more convenient but provides less granular control and fewer configuration options

维护计划向导更容易，更方便，但提供的粒度控制更少，配置选项更少

To automatically schedule a SQL Server backup using the SQL Server Maintenance Plan Wizard see the following steps:

若要使用SQL Server维护计划向导自动计划SQL Server备份，请执行以下步骤：

Right click Maintenance Plans and select the Maintenance Plan Wizard option, In the Object Explorer pane under the Management node

右键单击“维护计划”，然后在“管理”节点下的“对象资源管理器”窗格中选择“ 维护计划向导”选项。
In the Select Plan Properties window provide a name for the plan. click the Change button to schedule the job:

在“ 选择计划属性”窗口中，提供计划的名称。单击更改按钮以计划作业：
Select the Back Up Database option, in Select Maintenance Plan Tasks, and the Check data integrity setting. Check data integrity task performs an consistency check of the data and index pages within the database:

在“ 选择维护计划任务”中 ，选择“ 备份数据库”选项，然后选择“ 检查数据完整性”设置。 检查数据完整性任务对数据库中的数据页和索引页执行一致性检查：
Configure the maintenance task, in the next dialog, by specifying a database for backup and the backup configuration settings. Check the Verify backup integrity option, in the Define Back Up Database Task window also:

通过指定用于备份的数据库和备份配置设置，在下一个对话框中配置维护任务。还要在“ 定义备份数据库任务”窗口中检查“ 验证备份完整性”选项：
Click Finish once you are satisfied with the configuration of your job:
对作业的配置满意后，单击完成：

Maintenance plans, due to their value-added features and point and click interface (vs having to write scripts), are a good option for people who are less experienced. But the ease of use comes at the expense of an inability to customize many options or exercise much granular control

维护计划由于其增值功能和指向和单击界面（相对于必须编写脚本）而为经验不足的人们提供了不错的选择。但是易用性是以无法自定义许多选项或进行大量粒度控制为代价的

Furthermore, the extensibility of Maintenance Plans is extremely limited in that they are basically one-trick ponies, unable to process multiple tasks at once. One plan can support only one maintenance task, so by definition running one plan will result in executing only one task. Imagine creating a plan to delete older backup files but only being able to delete one file type at a time. The “solution” is to create more and more plans aka “Plan creep” to scale task processing and remember each Maintenance Plan requires its own SQL Server Agent job if it is to be scheduled. The result of this can quickly become an unmanageable ecosystem of plans, that requires a lot of effort and maintenance to organize

此外，维护计划的可扩展性受到极大限制，因为它们基本上是一招小马，无法一次处理多个任务。一个计划只能支持一项维护任务，因此根据定义，运行一项计划将导致仅执行一项任务。想象一下创建一个删除旧备份文件的计划，但一次只能删除一种文件类型。 “解决方案”是创建越来越多的计划（也称为“计划蠕变”）以扩展任务处理，并记住每个维护计划（如果要计划）都需要其自己SQL Server代理作业。这样的结果很快就会变成难以管理的计划生态系统，需要大量的精力和维护才能组织起来

To learn how to create backups with SSIS see Creating Backups with SSIS
To learn how to backup with Docker containers see Understanding Backup and Restore operations in SQL Server Docker Containers

若要了解如何使用SSIS 创建备份，请参阅使用SSIS 创建备份。
若要了解如何使用Docker容器进行备份，请参阅了解SQL Server Docker容器中的备份和还原操作

另请参阅： SQL Server 2017中的智能数据库备份

Here is a summary of both approaches on how to schedule a database backup compared side to side:

以下是两种如何安排数据库备份的方法的摘要：

	Maintenance Plans	SQL Server Agent
Run jobs without SQL scripting required	X
Backup all databases without SQL coding	X
Execute a task with a single job		X