备份期间计划的 SQL Server 作业会发生什么情况？
假设问题：
如果维护计划计划在多个数据库在线时运行完整备份，并且在此期间计划运行其他作业（存储过程、SSIS 包等），那么这些作业在备份期间会发生什么情况？
我猜测：
- 作业暂停，直到备份完成，然后它们按照预定的顺序运行。
或者
- SQL Server 计算出哪些表将受到每个计划作业的影响，并在作业完成后备份它们？！
或者，
- SQL Server 在备份开始之前创建所有表的“快照”，对它们的任何更改（包括备份期间运行的作业所做的更改）都会添加到事务日志中，应单独备份事务日志。
……我的想法正确吗？！
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?!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
想法#3 最接近实际发生的情况。关键是当备份操作完成时，备份文件将处于允许数据库恢复到一致状态的状态。
来自文档：
...
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:
...