备份期间计划的SQL Server作业会如何处理？ [英] What happens to SQL Server jobs that are scheduled during a backup?

问题描述

假设的问题：

如果计划维护计划以在线运行多个数据库的完整备份，并且在此期间其他作业也计划为运行（存储过程，SSIS包等），在备份过程中这些作业会发生什么？

我猜是：

• 作业被暂停直到备份完成，然后按照预定的顺序运行它们。

或

• SQL Server计算出哪些表将受到每个计划的作业的影响，并在备份后对其进行备份工作完成了吗？！

或

• SQL Server在备份开始之前为所有表创建一个快照，将对它们的任何更改（包括备份期间运行的作业所做的更改）添加到事务日志中，应分别备份。

...我的想法是否正确？！

解决方案

想法＃3最接近发生的情况。关键是备份操作完成后，备份文件将处于允许将数据库还原到一致状态的状态。

来自文档：

执行备份操作对
正在运行的事务的影响最小；因此，可以在常规
操作期间运行备份操作。在备份操作期间，SQL Server将数据
直接从数据库文件复制到备份设备。数据
保持不变，并且备份期间运行的事务不会延迟
。因此，您可以执行
对生产工作负载影响最小的SQL Server备份。

...

SQL Server使用联机备份过程来允许数据库
的备份，而数据库仍在使用中。在备份期间，大多数
操作都是可能的；例如，在备份操作期间允许INSERT，UPDATE或DELETE
语句。

Hypothetical question:

If a maintenance plan is scheduled to run a full backup of several databases while they're online, and during this time other jobs are scheduled to run (stored procedures, SSIS packages etc), what happens to these jobs during the backup?

I'm guessing either:

• The job is paused until the backup is completed, then they're run in the same order they were scheduled to.

Or

• SQL Server works out what tables will be affected by each scheduled job and backs them up after the job completes?!

Or

• SQL Server creates a "snapshot" of all the tables before the back up starts, any changes to them (including changes made by the jobs run during the backup) are added to the transaction log, which should be backed up separately.

...are any of my ideas correct?!

解决方案

Idea #3 is the closest to what happens. The key is that when the backup operation completes, the backup file will be in a state that allows for the restore of the database to a consistent state.

From the documentation:

Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. During a backup operation, SQL Server copies the data directly from the database files to the backup devices. The data is not changed, and transactions that are running during the backup are never delayed. Therefore, you can perform a SQL Server backup with minimal effect on production workloads.

...

SQL Server uses an online backup process to allow for a database backup while the database is still being used. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.

这篇关于备份期间计划的SQL Server作业会如何处理？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！