使用维护计划向导Use the Maintenance Plan Wizard

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍了如何在 SQL Server 2019 (15.x)SQL Server 2019 (15.x)中使用维护计划向导创建单服务器或多服务器维护计划。This topic describes how to create a single or multiserver maintenance plan using the Maintenance Plan Wizard in SQL Server 2019 (15.x)SQL Server 2019 (15.x). 维护计划向导用于创建 MicrosoftMicrosoft SQL ServerSQL Server 代理可定期运行的维护计划。The Maintenance Plan Wizard creates a maintenance plan that MicrosoftMicrosoft SQL ServerSQL Server Agent can run on a regular basis. 它使您可以执行各种数据库管理任务,包括备份、数据库完整性检查或以指定的间隔更新数据库统计信息。This allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.

限制和局限Limitations and Restrictions

  • 若要创建多服务器维护计划,必须配置包含一个主服务器和一个(或多个)目标服务器的多服务器环境。To create a multiserver maintenance plan, you must configure a multiserver environment with one master server, and one or more target servers. 必须在主服务器上创建和维护多服务器维护计划。You must create and maintain the multiserver maintenance plans on the master server. 可以在目标服务器上查看计划。You can view plans on target servers.

  • db_ssisadmindc_admin 角色的成员可以将其特权提升为 sysadminMembers of the db_ssisadmin and dc_admin roles may be able to elevate their privileges to sysadmin. 因为这些角色可以修改 Integration ServicesIntegration Services 包,而 SQL ServerSQL Server 使用 代理的 sysadmin SQL ServerSQL Server 安全上下文可以执行这些包,所以可以实现特权提升。This elevation of privilege can occur because these roles can modify Integration ServicesIntegration Services packages; these packages can be executed by SQL ServerSQL Server using the sysadmin security context of SQL ServerSQL Server Agent.

若要防止在运行维护计划、数据收集组和其它 Integration ServicesIntegration Services 包时提升特权,请将运行包的 SQL ServerSQL Server 代理作业配置为具有有限特权的代理帐户,或仅将 sysadmin 成员添加到 db_ssisadmindc_admin 角色。To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration ServicesIntegration Services packages, configure SQL ServerSQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

先决条件Prerequisites

必须启用 “代理 XP”服务器配置选项You must enable Agent XPs Server Configuration Option.

权限Permissions

若要创建或管理维护计划,您必须是 sysadmin 固定服务器角色的成员。To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. 对象资源管理器只为属于 sysadmin 固定服务器角色成员的用户显示 “维护计划” 节点。Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

使用维护计划向导Use the Maintenance Plan Wizard

启动向导Start the Wizard

  1. 展开要创建您的管理计划的服务器。Expand the server where you want to create your management plan.

  2. 展开 “管理” 文件夹。Expand the Management folder.

  3. 右键单击“维护计划” 文件夹,然后选择“维护计划向导” 。Right-click the Maintenance Plans folder and select Maintenance Plan Wizard.

  4. “SQL Server 维护计划向导” 页上,单击 “下一步”On the SQL Server Maintenance Plan Wizard page, click Next.

  5. 关于 “选择计划属性” 页:On the Select Plan Properties page:

    1. “名称” 框中,输入您创建的维护计划的名称。In the Name box, enter the name of the maintenance plan you are creating.

    2. “说明” 框中,简要介绍您的维护计划。In the Description box, briefly describe your maintenance plan.

    3. “运行身份” 列表中,指定执行维护计划时 Microsoft SQL Server 代理使用的凭据。In the Run as list, specify the credential that Microsoft SQL Server Agent uses when executing the maintenance plan.

    4. 选择 “每项任务单独计划”“整个计划统筹安排或无计划” 指定维护计划的重复执行计划。Select either Separate schedules for each task or Single schedule for the entire plan or no schedule to specify the recurring schedule of the maintenance plan.

      注意: 如果您选中 “每项任务单独计划” ,则需要为您维护计划中的每个任务按照 e.NOTE: If you select Separate schedules for each task, you will need to follow the steps in e. 下面的步骤执行操作。below for each task in your maintenance plan.

    5. 如果您选择了 “整个计划统筹安排或无计划” ,则在 “计划” 下面单击 “更改”If you selected Single schedule for the entire plan or no schedule, under Schedule, click Change.

      1. 在“新建作业计划”对话框的“名称”框中,输入作业计划的名称 。In the New Job Schedule dialog box, in the Name box, enter the job schedule's name.

      2. “计划类型” 列表中选择计划类型:On the Schedule type list, select the type of schedule:

        • SQL Server 代理启动时自动启动Start automatically when SQL Server Agent starts

        • CPU 空闲时启动Start whenever the CPUs become idle

        • 重复执行Recurring. 这是默认选项。This is the default selection.

        • 一次One time

      3. 选择或清除 “已启用” 复选框以启用或禁用计划。Select or clear the Enabled check box to enable or disable the schedule.

      4. 如果选择 “重复执行”If you select Recurring:

        1. “频率” 下的 “执行” 列表中,指定执行的频率:Under Frequency, on the Occurs list, specify the frequency of occurrence:

          • 如果选择 “每天” ,请在 “执行间隔” 框中输入重复作业计划的频率(天)。If you select Daily, in the Recurs every box, enter how often the job schedule repeats in days.

          • 如果选择 “每周” ,请在 “执行间隔” 框中输入重复作业计划的频率(周)。If you select Weekly, in the Recurs every box, enter how often the job schedule repeats in weeks. 选择在其运行作业计划每周天数。Select the days of the week on which the job schedule is run.

          • 如果选择 “每月” ,可以选择 “天”“特定日期”If you select Monthly, select either Day or The.

            • 如果选择 “天” ,请输入要运行作业计划的当月日期和作业计划的重复频率(月)。If you select Day, enter both the date of the month you want the job schedule to run and how often the job schedule repeats in months. 例如,如果要每隔一个月在当月的 15 日运行计划作业,请选择“天”,在第一个框中输入“15”,在第二个框中输入“2” 。For example, if you want the job schedule to run on the 15th day of the month every other month, select Day and enter "15" in the first box and "2" in the second box. 请注意,第二个框中允许的最大数是“99”。Please note that the largest number allowed in the second box is "99".

            • 如果选择 “特定日期” ,请选择要运行作业计划的当月内一周的特定一天和作业计划的重复频率(月)。If you select The, select the specific day of the week within the month that you want the job schedule to run and how often the job schedule repeats in months. 例如,如果要每隔一个月在当月的最后一个工作日运行作业计划,请选择“天”,从第一个列表中选择“最后一周”,从第二个列表中选择“工作日”,然后在最后一个框中输入“2” 。For example, if you want the job schedule to run on the last weekday of the month every other month, select Day, select last from the first list and weekday from the second list, and then enter "2" in the last box. 还可以从前两个列表中选择“第一周” 、“第二周” 、“第三周” 或“第四周” 以及特定工作日(例如:星期日或星期三)。You can also select first, second, third, or fourth, as well as specific weekdays (for example: Sunday or Wednesday) from the first two lists. 请注意,最后一个框中允许的最大数是“99”。Please note that the largest number allowed in the last box is "99".

        2. “每天频率” 下,指定作业计划运行的当天作业计划的重复频率。Under Daily frequency, specify how often the job schedule repeats on the day the job schedule runs:

          • 如果选择 “执行一次,时间为:” ,请在 “执行一次,时间为:” 框中输入运行作业计划的当天的特定时间。If you select Occurs once at, enter the specific time of day when the job schedule should run in the Occurs once at box. 输入当天的小时、分钟和秒以及 AM 或 PM。Enter the hour, minute, and second of the day, as well as AM or PM.

          • 如果选择 “执行间隔” ,请在 “频率” 下指定所选日运行作业计划的频率。If you select Occurs every, specify how often the job schedule runs during the day chosen under Frequency. 例如,如果要在运行作业计划的当天每隔 2 小时重复一次,请选择“执行间隔”,在第一个框中输入“2”,然后从列表中选择“小时” 。For example, if you want the job schedule to repeat every 2 hours during the day that the job schedule is run, select Occurs every, enter "2" in the first box, and then select hour(s) from the list. 从此列表中还可以选择“分钟” 和“秒” 。From this list you can also select minute(s) and second(s). 请注意,第一个框中允许的最大数是“100”。Please note that the largest number allowed in the first box is "100".

            “开始时间” 框中,输入开始运行作业计划的时间。In the Starting at box, enter the time that the job schedule should start running. “结束时间” 框中,输入停止重复作业计划的时间。In the Ending at box, enter the time that the job schedule should stop repeating. 输入当天的小时、分钟和秒以及 AM 或 PM。Enter the hour, minute, and second of the day, as well as AM or PM.

        3. “持续时间” 下的 “开始日期” 中,输入希望作业计划开始运行的日期。Under Duration, in Start date, enter the date that you want the job schedule to start running. 选择 “结束日期”“无结束日期” 以指示作业计划应在何时停止运行。Select End date or No end date to indicate when the job schedule should stop running. 如果选择 “结束日期” ,输入希望作业计划停止运行的日期。If you select End date, enter the date that you want to job schedule to stop running.

      5. 如果选择“执行一次” ,请在“执行一次” 下的“日期” 框中输入将运行作业计划的日期。If you select One Time, under One-time occurrence, in the Date box, enter the date that the job schedule will be run. “时间” 框中,输入将运行作业计划的时间。In the Time box, enter the time that the job schedule will be run. 输入当天的小时、分钟和秒以及 AM 或 PM。Enter the hour, minute, and second of the day, as well as AM or PM.

      6. “摘要” 下的 “说明” 中,验证所有作业计划设置均正确。Under Summary, in Description, verify that all job schedule settings are correct.

      7. 单击“确定” 。Click OK.

    6. 单击“下一步” 。Click Next.

  6. “选择目标服务器” 页上,选择要运行维护计划的服务器。On the Select Target Servers page, select the servers where you want to run the maintenance plan. 此页仅在配置为主服务器的 SQL ServerSQL Server 实例上可见。This page is only visible on SQL ServerSQL Server instances that are configured as master servers.

    注意: 若要创建多服务器维护计划,必须配置包含一台主服务器和一台或多台目标服务器的多服务器环境,并且应将本地服务器配置为主服务器。NOTE: To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured, and the local server should be configured as a master server. 在多服务器环境中,此页显示“(本地)” 主服务器和所有相应的目标服务器。In multiserver environments, this page displays the (local) master server and all corresponding target servers.

  7. “选择维护任务” 页上,选择一个或多个要添加到该计划中的维护任务。On the Select Maintenance Tasks page, select one or more maintenance tasks to add to the plan. 当您已选择所有必要的任务时,请单击 “下一步”When you have selected all of the necessary tasks, click Next.

    注意: 你在此处选择的任务将确定在“选择维护任务顺序” 页之后将需要完成的页。NOTE: The tasks you select here will determine which pages you will need to complete after the Select Maintenance Task Order page below.

  8. 在“选择维护任务顺序”页上,选择一个任务,然后单击“上移…”或“下移…”以更改其执行顺序 。On the Select Maintenance Task Order page, select a task and click either Move Up... or Move Down... to change its order of execution. 完成操作后,或如果您对当前任务的顺序感到满意时,请单击 “下一步”When finished, or if you are satisfied with the current order of tasks, click Next.

    注意: 如果你在上面的“选择计划属性” 页中选择了“每项任务单独计划” ,则无法在此页上更改维护任务的顺序。NOTE: If you selected Separate schedules for each task on the Select Plan Properties page above, you will not be able to change the order of the maintenance tasks on this page.

定义数据库检查完整性 (CHECKDB)Define Database Check Integrity (CHECKDB)

“定义数据库检查完整性任务” 页中,选择将检查用户和系统表以及索引的分配和结构完整性的数据库。On the Define Database Check Integrity Task page, choose the database or databases where the allocation and structural integrity of user and system tables and indexes will be checked. 通过运行 DBCC CHECKDBTransact-SQLTransact-SQL 语句,该任务可确保报告数据库中存在的所有完整性问题,以便系统管理员或数据库所有者能够在以后加以解决。By running the DBCC CHECKDBTransact-SQLTransact-SQL statement, this task ensures that any integrity problems with the database are reported, thereby allowing them to be addressed later by a system administrator or database owner. 有关详细信息,请参阅DBCC CHECKDB (Transact-SQL)。完成后,单击“下一步” 。For more information, see DBCC CHECKDB (Transact-SQL)When complete, click Next.

此页还提供以下选项:The following options are available on this page.

“数据库” 列表Databases list
指定受此任务影响的数据库。Specify the databases affected by this task.

  • “所有数据库”All databases

生成的维护计划将对除 MicrosoftMicrosoft SQL ServerSQL Server databases except tempdb.Generate a maintenance plan that runs this task against all MicrosoftMicrosoft SQL ServerSQL Server databases except tempdb.

系统数据库System databases

  • 生成的维护计划将对除 SQL ServerSQL Server tempdb 和用户创建的数据库之外的 系统数据库运行此任务。Generate a maintenance plan that runs this task against SQL ServerSQL Server system databases except tempdb and user-created databases.

所有用户数据库(master、model、msdb、tempdb 除外)All user databases (excluding master, model, msdb, tempdb)

  • 生成的维护计划将对用户创建的所有数据库运行此任务。Generate a maintenance plan that runs this task against all user-created databases. 但不会对 SQL ServerSQL Server 系统数据库运行任何维护任务。No maintenance tasks are run against the SQL ServerSQL Server system databases.

以下数据库These databases

  • 生成的维护计划只对所选数据库运行此任务。Generate a maintenance plan that runs this task against only those databases that are selected. 如果选择此选项,则必须至少在列表中选择一个数据库。At least one database in the list must be selected if this option is chosen.

“包含索引” 复选框Include indexes check box

  • 检查所有索引页以及表数据页的完整性。Check the integrity of all the index pages as well as the table data pages.

仅物理Physical only

  • 限制为仅检查页和记录标头的物理结构完整性以及数据库的分配一致性。Limits the check to the integrity of the physical structure of the page, record headers, and the allocation consistency of the database. 使用此选项可减少 DBCC CHECKDB 在大型数据库上的运行时。因此,如果你需要频繁使用生产系统,我们建议你使用此选项。Using this option may reduce run-time for DBCC CHECKDB on large databases, and is recommended for frequent use on production systems.

TablockTablock

  • 使 DBCC CHECKDB 获取锁,而不使用内部数据库快照。Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. 这包括一个短期数据库排他 (X) 锁。This includes a short-term exclusive (X) lock on the database. 使用此选项可帮助 DBCC CHECKDB 在负荷较重的数据库上运行得更快,但会在 DBCC CHECKDB 运行的同时降低数据库上的并发性。Using this option may help DBCC CHECKDB run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

定义数据库收缩任务Define Database Shrink Tasks

  1. “定义收缩数据库任务” 页上,使用 DBCC SHRINKDATABASE 语句以及 NOTRUNCATETRUNCATEONLY 选项,可以创建一个任务,以尝试减小所选数据库的大小。On the Define Shrink Database Task page, create a task that attempts to reduce the size of the selected databases by using the DBCC SHRINKDATABASE statement, with either the NOTRUNCATE or TRUNCATEONLY option. 有关详细信息,请参阅 DBCC SHRINKDATABASE (Transact-SQL)For more information, see DBCC SHRINKDATABASE (Transact-SQL). 完成后,单击 “下一步”When complete, click Next.

    警告!!!!WARNING!!!! 被移动用来收缩文件的数据可以分布到文件的任何可用位置。Data moved to shrink a file can be scattered to any available location in the file. 这将导致索引碎片并使搜索索引范围的查询变慢。This causes index fragmentation and can slow the performance of queries that search a range of the index. 若要消除碎片,请考虑在收缩后重新生成文件的索引。To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

    此页还提供以下选项:The following options are available on this page.

    “数据库” 列表Databases list
    指定受此任务影响的数据库。Specify the databases affected by this task. 有关此列表上可用选项的详细信息,请参阅上述步骤 9。See step 9, above, for more information on the available options on this list.

    “当数据库大小超过指定值时收缩数据库” 框Shrink database when it grows beyond box
    指定引发此任务的数据库大小 (MB)。Specify the size in megabytes that causes the task to execute.

    “收缩后保留的可用空间” 框Amount of free space to remain after shrink box
    当数据库文件中的可用空间达到此值时停止收缩(以百分比表示)。Stop shrinking when free space in database files reaches this size (as a percentage).

    将释放的空间保留在数据库文件中Retain freed space in database files
    将数据库精简为连续页,但不释放这些页,因此数据库文件不会收缩。The database is condensed to contiguous pages but the pages are not deallocated, and the database files do not shrink. 如果希望数据库再次扩大,但不希望重新分配空间,则使用此选项。Use this option if you expect the database to expand again, and you do not want to reallocate space. 使用此选项时,数据库文件不会尽可能地收缩。With this option, the database files do not shrink as much as possible. 这将使用 NOTRUNCATE 选项。This uses the NOTRUNCATE option.

    将释放的空间归还给操作系统Return freed space to operating system
    将数据库精简为连续页,并将这些页释放回操作系统,以供其他程序使用。The database is condensed to contiguous pages and the pages are released back to the operating system for use by other programs. 这将使用 TRUNCATEONLY 选项。This uses the TRUNCATEONLY option. 这是默认选项。This is the default option.

定义索引任务Define the Index Tasks

  1. “定义重新组织索引任务” 页上,选择用来移动索引页以提高搜索顺序效率的服务器。On the Define Reorganize Index Task page, select the server or servers where you'll be moving index pages into a more efficient search order. 此任务使用 ALTER INDEX ... REORGANIZE 语句。This task uses the ALTER INDEX ... REORGANIZE statement. 有关详细信息,请参阅 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL). 完成后,单击 “下一步”When complete, click Next.

    此页还提供以下选项:The following options are available on this page.

    “数据库” 列表Databases list
    指定受此任务影响的数据库。Specify the databases affected by this task. 有关此列表上可用选项的详细信息,请参阅上述步骤 9。See step 9, above, for more information on the available options on this list.

    “对象” 列表Object list
    将“选择” 列表限制为显示表、视图或同时显示两者。Limit the Selection list to display tables, views, or both. 仅当从上面的 “数据库” 列表中选中单个数据库时,该列表才可用。This list is only available if a single database is chosen from the Databases list above.

    “选择” 列表Selection list
    指定受此任务影响的表或索引。Specify the tables or indexes affected by this task. 在“对象”框中选择 “表和视图” 时不可用。Not available when Tables and Views is selected in the Object box.

    “压缩大型对象” 复选框Compact large objects check box
    在可能的情况下,释放表和视图的空间。Deallocate space for tables and views when possible. 此选项使用 ALTER INDEX ... LOB_COMPACTION = ONThis option uses ALTER INDEX ... LOB_COMPACTION = ON.

  2. 在“定义重新生成索引任务” 页上,选择你将重新创建多个索引的数据库。On the Define Rebuild Index Task page, select the database or databases where you'll be re-creating multiple indexes. 此任务使用 ALTER INDEX ... REBUILD PARTITION 语句。This task uses the ALTER INDEX ... REBUILD PARTITION statement. 有关详细信息,请参阅 CREATE INDEX (Transact-SQL)。)完成后,单击“下一步” 。For more information, see ALTER INDEX (Transact-SQL).) When complete, click Next.

    此页还提供以下选项:The following options are available on this page.

    “数据库” 列表Databases list
    指定受此任务影响的数据库。Specify the databases affected by this task. 有关此列表上可用选项的详细信息,请参阅上述步骤 9。See step 9, above, for more information on the available options on this list.

    “对象” 列表Object list
    将“选择” 列表限制为显示表、视图或同时显示两者。Limit the Selection list to display tables, views, or both. 仅当从上面的 “数据库” 列表中选中单个数据库时,该列表才可用。This list is only available if a single database is chosen from the Databases list above.

    “选择” 列表Selection list
    指定受此任务影响的表或索引。Specify the tables or indexes affected by this task. 在“对象”框中选择 “表和视图” 时不可用。Not available when Tables and Views is selected in the Object box.

    “可用空间选项” 区域Free space options area
    提供了用于将填充因子应用到索引和表的选项。Presents options for applying fill factor to indexes and tables.

    每页的默认可用空间Default free space per page
    使用默认可用空间重新组织页。Reorganizes the pages with the default amount of free space. 这将删除数据库中表上的索引,并使用在创建索引时指定的填充因子重新创建索引。This will drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created. 这是默认选项。This is the default option.

    “将每页可用空间更改为” 框Change free space per page to box
    删除数据库中表上的索引,并使用新的、自动计算的填充因子重新创建索引,从而在索引页上保留指定的可用空间。Drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor, thereby reserving the specified amount of free space on the index pages. 百分比越高,索引页上保留的可用空间就越多,并且索引增长也就越大。The higher the percentage, the more free space is reserved on the index pages, and the larger the index grows. 有效值为 0 到 100。Valid values are from 0 through 100. 使用 FILLFACTOR 选项。Uses the FILLFACTOR option.

    “高级选项” 区域Advanced options area
    提供用于排序索引和重建索引的其他选项。Presents additional options for sorting indexes and reindexing.

    “对 tempdb 中的结果进行排序” 复选框Sort results in tempdb check box
    使用 SORT_IN_TEMPDB 选项,该选项确定在索引创建过程中生成的中间排序结果的临时存储位置。Uses the SORT_IN_TEMPDB option which determines where the intermediate sort results, generated during index creation, are temporarily stored. 如果不需要执行排序操作,或者可以在内存中执行排序,则忽略 SORT_IN_TEMPDB 选项。If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

    “填充索引” 复选框Pad Index check box
    使用 PAD_INDEX 选项。Uses the PAD_INDEX option.

    “重建索引时保持索引联机” 复选框Keep index online while reindexing check box
    使用 ONLINE 选项,用户可以在索引操作期间访问基础表或聚集索引数据以及任何关联的非聚集索引。Uses the ONLINE option which allows users to access the underlying table or clustered index data and any associated nonclustered indexes during index operations. 选择此选项将激活用于重新生成不允许联机重新生成的索引的其他选项:“不重新生成索引”和“脱机重新生成索引” 。Selecting this option activates additional options for rebuilding indexes that do not allow for online rebuilds: Do not rebuild indexes and Rebuild indexes offline.

    选中此选项还可激活“使用低优先级”(其使用 WAIT_AT_LOW_PRIORITY 选项)。Selecting this option also activates Low Priority Used, which uses the WAIT_AT_LOW_PRIORITY option. 联机索引重新生成操作将等待低优先级锁 MAX_DURATION 分钟,从而允许其他操作在联机索引生成操作正在等待的同时继续进行。Online index rebuild operations will wait for low priority locks for MAX_DURATION minutes, allowing other operations to proceed while the online index build operation is waiting.

    注意:SQL Server 2019 (15.x)SQL Server 2019 (15.x)的各版本中均不提供联机索引操作。NOTE: Online index operations are not available in every edition of SQL Server 2019 (15.x)SQL Server 2019 (15.x). 有关详细信息,请参阅 SQL Server 2016 各个版本支持的功能For more information, see Features Supported by the Editions of SQL Server 2016.

    “MAXDOP” 复选框MAXDOP check box
    对于 DBCC CHECKDB,替代 sp_configure 的最大并行度配置选项。Overrides the max degree of parallelism configuration option of sp_configure for DBCC CHECKDB. 有关详细信息,请参阅 DBCC CHECKDB (Transact-SQL)For more information, see DBCC CHECKDB (Transact-SQL)

定义更新统计信息任务Define the Update Statistics Task

  1. “定义更新统计信息任务” 页上,定义将对其进行更新表和索引统计信息的数据库。On the Define Update Statistics Task page, define the database or databases on which table and index statistics will be updated. 此任务使用 UPDATE STATISTICS 语句。This task uses the UPDATE STATISTICS statement. 有关详细信息,请参阅 更新统计信息 (Transact-SQL)。完成后,单击“下一步” For more information, see UPDATE STATISTICS (Transact-SQL) When finished, click Next

    此页还提供以下选项:The following options are available on this page.

    “数据库” 列表Databases list
    指定受此任务影响的数据库。Specify the databases affected by this task. 有关此列表上可用选项的详细信息,请参阅上述步骤 9。See step 9, above, for more information on the available options on this list.

    “对象” 列表Object list
    将“选择” 列表限制为显示表、视图或同时显示两者。Limit the Selection list to display tables, views, or both. 仅当从上面的 “数据库” 列表中选中单个数据库时,该列表才可用。This list is only available if a single database is chosen from the Databases list above.

    “选择” 列表Selection list
    指定受此任务影响的表或索引。Specify the tables or indexes affected by this task. 在“对象”框中选择 “表和视图” 时不可用。Not available when Tables and Views is selected in the Object box.

    所有现有统计信息All existing statistics
    同时更新列和索引的统计信息。Update statistics for both columns and indexes.

    仅限列统计信息Column statistics only
    仅更新列统计信息。Only update column statistics. 使用 WITH COLUMNS 选项。Uses the WITH COLUMNS option.

    仅限索引统计信息Index statistics only
    仅更新索引统计信息。Only update index statistics. 使用 WITH INDEX 选项。Uses the WITH INDEX option.

    扫描类型Scan type
    用于收集已更新统计信息的扫描的类型。Type of scan used to gather updated statistics.

    完全扫描Full scan
    读取表或视图中的所有行来收集统计信息。Read all rows in the table or view to gather the statistics.

    抽样依据Sample by
    指定在收集较大型的表或视图的统计信息时要抽样的表或索引视图的百分比或者行数。Specify the percentage of the table or indexed view, or the number of rows to sample when collecting statistics for larger tables or views.

定义清除历史记录任务Define the History Cleanup Task

  1. “定义清除历史记录任务” 页上,选择您要删除旧的任务历史记录的数据库。On the Define History Cleanup Task page, define the database or databases where you want to discard old task history. 该任务使用 EXEC sp_purge_jobhistoryEXEC sp_maintplan_delete_logEXEC sp_delete_backuphistory 语句从 msdb 表中删除历史记录信息。This task uses the EXEC sp_purge_jobhistory, EXEC sp_maintplan_delete_log, and EXEC sp_delete_backuphistory statements to remove history information from the msdb tables. 完成后,单击 “下一步”When finished, click Next.

    此页还提供以下选项:The following options are available on this page.

    选择要删除的历史数据Select the historical data to delete
    选中要删除的任务数据的类型Chose the type of task data to delete/

    备份和还原历史记录Backup and restore history
    当您希望还原数据库时,保留有关最近备份创建时间的记录可帮助 SQL ServerSQL Server 创建恢复计划。Retaining records of when recent backups were created can help SQL ServerSQL Server create a recovery plan when you want to restore a database. 保留期应当至少为完整数据库备份的频率。The retention period should be at least the frequency of full database backups.

    SQL Server 代理作业历史记录SQL Server Agent Job history
    使用此历史记录有助于排除失败作业的故障,或者确定数据库操作发生的原因。This history can help you troubleshoot failed jobs, or determine why database actions occurred.

    维护计划历史记录Maintenance Plan history
    使用此历史记录有助于排除失败的维护计划作业的故障,或者确定数据库操作发生的原因。This history can help you troubleshoot failed maintenance plan jobs, or determine why database actions occurred.

    删除历史数据,如果其保留时间超过Remove historical data older than
    指定要删除项的保留时间。Specify age of items that you want to delete. 你可以指定“小时” 、“天” 、“周” (默认值)、“月” 或“年” You can specify Hour(s), Day(s), Week(s) (the default), Month(s), or Year(s)

定义执行代理作业任务Define the Execute Agent Job Task

  1. “定义执行代理作业任务” 页的 “可用的 SQL Server 代理作业” 下面,选中要运行的作业。On the Define Execute Agent Job Task page, under Available SQL Server Agent jobs, choose the job or jobs to run. 如果没有 SQL 代理作业,此选项将不可用。This option will not be available if you have no SQL Agent jobs. 此任务使用 EXEC sp_start_job 语句。This task uses the EXEC sp_start_job statement. 有关详细信息,请参阅 sp_start_job (Transact SQL)。完成后,单击“下一步” 。For more information, see sp_start_job (Transact-SQL)When finished, click Next.

定义备份任务。Define Backup Tasks

  1. 在“定义备份数据库(完整)任务” 页上,选择要对其运行完整备份的数据库。On the Define Backup Database (Full) Task page, select the database or databases on which to run a full backup. 此任务使用 BACKUP DATABASE 语句。This task uses the BACKUP DATABASE statement. 有关详细信息,请参阅 BACKUP (Transact-SQL)For more information, see BACKUP (Transact-SQL). 完成后,单击 “下一步”When finished, click Next.

    此页还提供以下选项:The following options are available on this page.

    “备份类型” 列表Backup type list
    显示要执行的备份类型。Displays the type of backup to be performed. 这是只读的。This is read-only.

    “数据库” 列表Databases list
    指定受此任务影响的数据库。Specify the databases affected by this task. 有关此列表上可用选项的详细信息,请参阅上述步骤 9。See step 9, above, for more information on the available options on this list.

    备份组件Backup component
    选择“数据库” 将备份整个数据库。Select Database to back up the entire database. 选择 “文件和文件组” 将只备份部分数据库。Select File and filegroups to back up only a portion of the database. 如果选择此选项,请提供文件或文件组名称。If selected, provide the file or filegroup name. 如果在 “数据库” 框中选择了多个数据库,只能对 “备份组件” 指定 “数据库”When multiple databases are selected in the Databases box, only specify Databases for the Backup components. 若要执行文件或文件组备份,请为每个数据库创建一个任务。To perform file or filegroup backups, create a task for each database. 仅当选中上述 “数据库” 列表中的单个数据库时,这些选项才可用。These options are only available if a single database is chosen from the Databases list above.

    “备份集过期时间” 复选框Backup set will expire check box
    指定允许覆盖该备份的备份集的日期。Specifies when the backup set for this backup can be overwritten. 选择 “晚于” ,然后输入过期前的天数,或选择 “在” ,然后输入过期日期。Select After and enter a number of days to expiration, or select On and enter a date of expiration. 如果选择 URL 作为备份目标,则禁用该选项。This option is disabled if URL is selected as the backup destination.

    备份到Back up to
    指定要将数据库备份到的介质。Specifies the medium on which to back up the database. 选择 “磁盘”“磁带”URLSelect Disk, Tape, or URL. 只有连接到该数据库所在计算机的磁带设备才可用。Only tape devices attached to the computer containing the database are available.

    跨一个或多个文件备份数据库Back up database(s) across one or more files
    单击“添加” 可以打开“选择备份目标” 对话框。Click Add to open the Select Backup Destination dialog box. 如果选择 URL 作为备份目标,则禁用该选项。This option is disabled if you selected URL as the backup destination.

    单击 “删除” 将文件从该框中删除。Click Remove to remove a file from the box.

    单击 “内容” 将读取文件头,并显示此文件的当前备份内容。Click Contents to read the file header and display the current backup contents of the file.

    “选择备份目标” 对话框Select Backup Destination dialog box
    选择拥有备份目标的文件、磁带机或备份设备。Select the file, tape drive, or backup device for the backup destination. 如果选择 URL 作为备份目标,则禁用该选项。This option is disabled if you selected URL as the backup destination.

    “如果备份文件存在” 列表If backup files exist list
    指定如何处理现有备份。Specify how to handle existing backups. 选择 “追加” 将新备份添加到文件或磁带中的所有现有备份之后。Select Append to add the new backups after any existing backups in the file or on the tape. 选择 “覆盖” 将删除文件或磁带中的旧内容,并将其替换为新备份。Select Overwrite to remove the old content of a file or tape, and replace it with this new backup.

    为每个数据库创建备份文件Create a backup file for every database
    在文件夹框中指定的位置创建一个备份文件。Create a backup file in the location specified in the folder box. 为选定的每个数据库创建一个文件。One file is created for each database selected. 如果选择 URL 作为备份目标,则禁用该选项。This option is disabled if you selected URL as the backup destination.

    “为每个数据库创建子目录” 复选框Create a sub-directory for each database check box
    在指定磁盘目录下创建一个子目录,指定的磁盘目录包含维护计划中要备份的每一个数据库的数据库备份。Create a sub-directory under the specified disk directory that contains the database backup for each database being backed up as part of the maintenance plan.

    重要说明!IMPORTANT! 子目录将从父目录继承权限。The sub-directory will inherit permissions from the parent directory. 请限制相关权限,以避免未经授权的访问。Restrict permissions to avoid unauthorized access.

    “文件夹” 框Folder box
    指定用来放置自动创建的数据库文件的文件夹。Specify the folder to contain the automatically created database files. 如果选择 URL 作为备份目标,则禁用该选项。This option is disabled if you selected URL as the backup destination.

    SQL 凭据SQL Credential
    选择可用于对 Azure 存储进行身份验证的 SQL 凭据。Select a SQL Credential used to authenticate to Azure Storage. 如果没有可使用的现有 SQL 凭据,则单击 “创建” 按钮可创建新的 SQL 凭据。If you do not have an existing SQL Credential you can use, click the Create button to create a new SQL Credential.

    重要说明!IMPORTANT! 单击 “创建” 打开的对话框需要管理证书或订阅的发布配置文件。The dialog that opens when you click Create requires a management certificate or the publishing profile for the subscription. 如果您无权访问管理证书或发布配置文件,可以创建一个 SQL 凭据,方法是使用 Transact-SQL 或 SQL Server Management Studio 指定存储帐户名称和访问密钥信息。If you do not have access to the management certificate or publishing profile, you can create a SQL Credential by specifying the storage account name and access key information using Transact-SQL or SQL Server Management Studio. 请参阅 创建凭据 主题中的示例代码,使用 Transact-SQL 创建凭据。See the sample code in the Create a Credential topic to create a credential using Transact-SQL. 或者,使用 SQL Server Management Studio,从数据库引擎实例中右键单击 “安全性” ,依次选择 “新建”“凭据”Alternatively, using SQL Server Management Studio, from the database engine instance, right-click Security, select New, and select Credential. “标识” 字段中指定存储帐户名称,在 “密码” 字段中指定访问密钥。Specify the storage account name for Identity and the access key in the Password field.

    Azure 存储容器Azure storage container
    指定 Azure 存储容器的名称Specify the name of the Azure storage container

    URL 前缀:URL prefix:
    这是基于在 SQL 凭据中存储的存储帐户信息以及您指定的 Azure 存储容器名称自动生成的。This is automatically generated based on the storage account information stored in the SQL Credential, and Azure storage container name you specified. 我们建议你不要编辑此字段中的信息,除非你使用的域采用 <存储帐户>.blob.core.windows.net 以外的格式。We recommend that you do not edit the information in this field unless you are using a domain that uses a format other than <storage account>.blob.core.windows.net.

    “备份文件扩展名” 框Backup file extension box
    指定备份文件要使用的扩展名。Specify the extension to use for the backup files. 默认为 .bak。The default is .bak.

    “验证备份完整性” 复选框Verify backup integrity check box
    验证备份集是否完整以及所有卷是否都可读。Verify that the backup set is complete and that all volumes are readable.

    “检查校验和” 复选框Perform checksum check box
    在启用此可用功能后,验证每页的校验和以及确认是否有残缺页,并为整个备份生成校验和。Verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

    “出错时继续” 复选框Continue on error check box
    指示 BACKUP 继续执行,不管是否遇到无效校验和或页撕裂之类的错误。Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

    备份加密Backup Encryption
    若要创建加密的备份,请选中 “加密备份” 复选框。To create an encrypted backup, check the Encrypt backup check box. 选择要用于加密步骤的加密算法,然后从现有证书或非对称密钥的列表中提供一个证书或非对称密钥。Select an encryption algorithm to use for the encryption step, and provide a Certificate or Asymmetric key from a list of existing certificates or asymmetric keys. 可用于加密的算法是:The available algorithms for encryption are:

    • AES 128AES 128

    • AES 192AES 192

    • AES 256AES 256

    • Triple DESTriple DES

    如果您选择了追加到现有备份集,则禁用加密选项。The encryption option is disabled if you selected to append to existing backup set.

    建议做法是备份证书或密钥,而存储它们的位置要与所加密的备份不同。It is recommended practice to back up your certificate or keys and store them in a different location than the backup you encrypted.

    仅支持位于可扩展密钥管理 (EKM) 中的密钥。Only keys residing in the Extensible Key Management (EKM) are supported.

    “块大小” 复选框,列表Block size check box, list

    用字节数来指定物理块的大小。Specifies the physical block size, in bytes. 在写入磁带设备、RAID 数组或 SAN 时,此选项通常会影响性能。This option typically affects performance when writing to tape devices, RAID arrays, or SAN.

    “最大传输大小” 复选框,列表Max transfer size check box, list

    指定 SQL Server 和备份媒体之间的最大传输单元(以字节为单位)。Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media.

    “设置备份压缩” 列表Set backup compression list
    SQL Server 2008 EnterpriseSQL Server 2008 Enterprise (或更高版本)中,选择以下 备份压缩 值之一:In SQL Server 2008 EnterpriseSQL Server 2008 Enterprise (or later versions), select one the following backup compression values:

    使用默认服务器设置Use the default server setting 单击此选项可使用服务器级别默认值。Click to use the server-level default. 此默认值可通过 backup compression default 服务器配置选项进行设置。This default is set by the backup compression default server-configuration option. 有关如何查看此选项当前设置的信息,请参阅 查看或配置 backup compression default 服务器配置选项For information about how to view the current setting of this option, see View or Configure the backup compression default Server Configuration Option.
    压缩备份Compress backup 单击此选项可压缩备份,而不考虑服务器级别默认值。Click to compress the backup, regardless of the server-level default.

    ** 重要说明 ** 默认情况下,压缩会大大提高 CPU 使用率,并且压缩进程占用的额外 CPU 可能会对并发操作造成不利影响。** Important ** By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely affect concurrent operations. 因此,您可能需要在会话中创建低优先级的压缩备份,其 CPU 使用率受资源调控器限制。Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by the Resource Governor. 有关详细信息,请参阅本主题后面的 使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)限制。For more information, see Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).
    不压缩备份Do not compress backup 单击此选项可创建未压缩的备份,而不考虑服务器级别默认值。Click to create an uncompressed backup, regardless of the server-level default.
  2. 在“定义备份数据库(差异)任务” 页上,选择要对其运行部分备份的数据库。On the Define Backup Database (Differential) Task page, select the database or databases on which to run a partial backup. 有关此页上可用选项的详细信息,请参阅上述步骤 16 中列出的定义。See the definition list in step 16, above, for more information about the available options on this page. 此任务使用 BACKUP DATABASE ... WITH DIFFERENTIAL 语句。This task uses the BACKUP DATABASE ... WITH DIFFERENTIAL statement. 有关详细信息,请参阅 BACKUP (Transact-SQL)For more information, see BACKUP (Transact-SQL). 完成后,单击 “下一步”When finished, click Next.

  3. 在“定义备份数据库(事务日志)任务” 页上,选择要对事务日志运行备份的数据库。On the Define Backup Database (Transaction Log) Task page, select the database or databases on which to run a backup for a transaction log. 有关此页上可用选项的详细信息,请参阅上述步骤 16 中列出的定义。See the definition list in step 16, above, for more information about the available options on this page. 此任务使用 BACKUP LOG 语句。This task uses the BACKUP LOG statement. 有关详细信息,请参阅 BACKUP (Transact-SQL)For more information, see BACKUP (Transact-SQL). 完成后,单击 “下一步”When finished, click Next.

定义清除维护任务Define Maintenance Cleanup Tasks

  1. “定义清除维护任务” 页上,指定要作为维护计划的一部分删除的文件类型,包括由维护计划文件和数据库备份文件创建的文本报告。On the Define Maintenance Cleanup Task page, specify the types of files to delete as part of the maintenance plan, including text reports created by maintenance plans and database backup files. 此任务使用 EXEC xp_delete_file 语句。This task uses the EXEC xp_delete_file statement. 完成后,单击 “下一步”When finished, click Next.

    重要说明!!IMPORTANT!! 此任务不会自动删除指定目录的子文件夹中的文件。This task does not automatically delete files in the subfolders of the specified directory. 此预防措施减少了使用清除维护任务删除文件的恶意攻击的可能性。This precaution reduces the possibility of a malicious attack that uses the Maintenance Cleanup task to delete files. 如果要删除一级子文件夹中的文件,必须选择“包括一级子文件夹” 。If you want to delete files in first-level subfolders, you must select Include first-level subfolders.

    此页还提供以下选项:The following options are available on this page.

    删除以下类型的文件Delete files of the following type
    指定要删除的文件类型。Specify the type of files to be deleted.

    备份文件Backup files
    删除数据库备份文件。Delete database backup files.

    维护计划文本报告Maintenance Plan text reports
    删除以前运行的维护计划的文本报告。Delete text reports of previously run maintenance plans.

    文件位置File location
    指定要删除的文件的路径。Specify path to files to be deleted.

    删除特定文件Delete specific file
    删除在“文件名” 文本框中提供的特定文件。Delete the specific file provided in the File name text box.

    搜索文件夹并根据扩展名删除文件Search folder and delete files based on an extension
    删除指定文件夹中带有指定扩展名的所有文件。Delete all files with the specified extension in the specified folder. 使用此选项可一次删除多个文件,例如 Tuesday 文件夹中带有 .bak 扩展名的所有备份文件。Use this to delete multiple files at once, such as all backup files in the Tuesday folder with the .bak extension.

    “文件夹” 框Folder box
    要删除的文件所在的文件夹的路径和名称。Path and name of the folder containing the files to be deleted.

    “文件扩展名” 框File extension box
    提供要删除的文件的文件扩展名。Provide the file extension of the files to be deleted. 若要一次删除多个文件(例如 Tuesday 文件夹中带有 .bak 扩展名的所有备份文件),请指定 .bak。To delete multiple files at one time, like all backup files with the .bak extension in the Tuesday folder, specify .bak.

    “包含一级子文件夹” 复选框Include first-level subfolders check box
    从“文件夹” 中指定的文件夹下的一级子文件夹中删除具有为“文件扩展名” 指定的扩展名的文件。Delete files with the extension specified for File extension from first-level subfolders under the folder specified in Folder.

    “在任务运行时根据文件保留时间删除文件” 复选框Delete files based on the age of the file at task run time check box
    通过在“删除文件,如果其保留时间超过” 框中提供数字和时间单位,指定将要删除的文件所要保留的最短时间。Specify the minimum age of the files that you want to delete by providing a number, and unit of time in the Delete files older than the following box.

    删除文件,如果其保留时间超过Delete files older than the following
    通过提供数字和时间单位(“小时” 、“天” 、“周” 、“月” 或“年” ),指定要删除的文件所保留的最短时间。Specify the minimum age of the files that you want to delete by providing a number, and unit of time (Hour, Day, Week, Month, or Year). 保留时间长于指定时间长度的文件将被删除。Files older than the time frame specified will be deleted.

选择报告选项Select Report Options

  1. “选择报告选项” 页中,选择对维护计划操作报表进行保存或分发的选项。On the Select Report Options page, select options for saving or distributing a report of the maintenance plan actions. 此任务使用 EXEC sp_notify_operator 语句。This task uses the EXEC sp_notify_operator statement. 有关详细信息,请参阅 sp_notify_operator (Transact SQL)。完成后,单击“下一步” 。For more information, see sp_notify_operator (Transact-SQL).When finished, click Next.

    此页还提供以下选项:The following options are available on this page.

    “将报告写入文本文件” 复选框Write a report to a text file check box
    将报告保存到一个文件中。Save the report in a file.

    “文件夹位置” 框Folder location box
    指定将要包含报告的文件的位置。Specify the location of the file that will contain the report.

    “以电子邮件形式发送报告” 复选框E-mail report check box
    任务失败时发送电子邮件。Send an e-mail when a task fails. 你必须在 MSDB 为邮件主机数据库的情况下启用和正确配置数据库邮件,并拥有有效电子邮件地址的 MicrosoftMicrosoftSQL ServerSQL Server 代理操作员,才能使用此任务。To use this task you must have Database Mail enabled and correctly configured with MSDB as a Mail Host Database, and have a MicrosoftMicrosoftSQL ServerSQL Server Agent operator with a valid e-mail address.

    代理操作员Agent operator
    指定电子邮件的收件人。Specify the recipient of the e-mail.

    邮件配置文件Mail profile
    指定定义电子邮件发件人的配置文件。Specify the profile that defines the sender of the e-mail.

完成该向导Complete the Wizard

  1. “完成该向导” 页上,验证在先前页上所做的选择,然后单击 “完成”On the Complete the Wizard page, verify the choices made on the previous pages, and click Finish.

  2. “维护向导进度” 页上,监视有关维护计划向导的状态信息。On the Maintenance Wizard Progress page, monitor status information about the actions of the Maintenance Plan Wizard. 根据在向导中选择的选项,“进度”页可能会包含一个操作或多个操作。Depending on the options that you selected in the wizard, the progress page might contain one or more actions. 最上面的方框显示向导的总体状态和向导已接收到的状态、错误和警告消息数。The top box displays the overall status of the wizard and the number of status, error, and warning messages that the wizard has received.

    “维护向导进度” 页上提供以下选项:The following options are available on the Maintenance Wizard Progress page:

    详细信息Details
    提供向导执行的操作所返回的操作、状态和所有消息。Provides the action, status, and any messages that are returned from action taken by the wizard.

    操作Action
    指定每个操作的类型和名称。Specifies the type and name of each action.

    “状态”Status
    指示向导操作作为一个整体返回的值是“成功” 还是“失败” 。Indicates whether the wizard action as a whole returned the value of Success or Failure.

    消息Message
    提供从该进程中返回的任何错误或警告消息。Provides any error or warning messages that are returned from the process.

    报告Report
    创建包含创建分区向导结果的报告。Creates a report that contains the results of the Create Partition Wizard. 这些选项是 “查看报告”“将报告保存到文件”“将报告复制到剪贴板”“将报告作为电子邮件发送”The options are View Report, Save Report to File, Copy Report to Clipboard, and Send Report as Email.

    查看报告View Report
    打开“查看报告” 对话框,其中包含关于创建分区向导进度的文本报告。Opens the View Report dialog box, which contains a text report of the progress of the Create Partition Wizard.

    将报告保存到文件Save Report to File
    打开“将报告另存为” 对话框。Opens the Save Report As dialog box.

    将报告复制到剪贴板Copy Report to Clipboard
    将向导的进度报告结果复制到剪贴板。Copies the results of the wizard's progress report to the Clipboard.

    “将报告作为电子邮件发送”Send Report as Email
    将向导的进度报告结果复制到电子邮件。Copies the results of the wizard's progress report into an email message.