本文共 6976 字,大约阅读时间需要 23 分钟。
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 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提供了维护计划功能,该功能使备份策略的执行变得容易且自动化。 在“ 文中,我们描述了可用于自动执行常见数据库维护任务的一般特征,方法和属性。 在本文中,我们将重点介绍“备份数据库”任务
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维护计划-手册以及通过维护计划向导功能。 请注意,如果需要,一个维护计划可以按适当的执行顺序同时执行许多其他任务(例如,检查数据库完整性,收缩数据库等)。
To create a maintenance plan in SQL Server Management Studio with appropriate database back up task:
要在SQL Server Management Studio中使用适当的数据库备份任务创建维护计划,请执行以下操作:
Using the Maintenance Plan Tasks toolbox, add the Back Up Database Task item (task) to the BackupMaintenancePlan design surface
使用维护计划任务工具箱,将“ 备份数据库任务”项(任务)添加到BackupMaintenancePlan设计图面
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多个属性,可用于定义和微调任务。 使用“ 属性”窗格根据您的备份策略设置属性
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属性)
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:
通过向导使用适当的数据库备份任务创建维护计划:
The Select Plan Properties dialog will open and provide initial settings, such as a name and schedule options
“ 选择计划属性”对话框将打开并提供初始设置,例如名称和计划选项
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
在“ 选择维护任务”对话框中,选择所需的备份数据库任务(“完整”,“差异”或“事务性”日志)。 当然,也可以选择任何其他维护任务
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 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版本之前的版本)使用而不是本机SQL Server备份操作。 另外,还需要维护数据库备份维护计划和相应的作业,以保持一致性并防止备份过程冲突。 此外,没有碰撞检测,并且如果同时在同一数据库上调度并发任务,则任务执行将失败。 为了避免这种情况,建议在维护计划由您负责备份策略之前对其进行测试
翻译自:
转载地址:http://cyiwd.baihongyu.com/