创建完整数据库备份 (SQL Server)Create a Full Database Backup (SQL Server)

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

对于 SQL Server 2014,请转到创建完整数据库备份 (SQL Server)For SQL Server 2014, go to Create a Full Database Backup (SQL Server).

本主题说明如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio或 PowerShell 在 Transact-SQLTransact-SQL中创建完整数据库备份。This topic describes how to create a full database backup in SQL Server 2017SQL Server 2017 using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

若要了解如何将 SQL Server 备份到 Azure Blob 存储服务,请参阅使用 Microsoft Azure Blob 存储服务进行 SQL Server 备份和还原SQL Server 备份到 URLFor information on SQL Server backup to the Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

开始之前!Before You begin!

限制和局限Limitations and Restrictions

  • 不允许在显式或隐式事务中使用 BACKUP 语句。The BACKUP statement is not allowed in an explicit or implicit transaction.

  • 无法在早期版本的 SQL ServerSQL Server 中还原较新版本的 SQL ServerSQL Server创建的备份。Backups created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

  • 有关备份概念和任务的概述、深入研究,请在继续操作前参阅 备份概述 (SQL Server)For and overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceding.

建议Recommendations

  • 随着数据库不断增大,完整数据库备份的完成时间会延长,并且需要占用更多存储空间。As a database increases in size full database backups take more time to complete, and require more storage space. 对于大型数据库,请考虑用一系列差异数据库备份来补充完整数据库备份。For a large database, consider supplementing a full database backup with a series of differential database backups. 有关详细信息,请参阅 SQL Server Backup to URLFor more information, see SQL Server Backup to URL.

  • 使用 sp_spaceused 系统存储过程估计完整数据库备份的大小。Estimate the size of a full database backup by using the sp_spaceused system stored procedure.

  • 默认情况下,每个成功的备份操作都会在 SQL ServerSQL Server 错误日志和系统事件日志中添加一个条目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 若频繁备份,这些成功消息会迅速累积,从而生成巨大的错误日志!If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! 这会使查找其他消息变得非常困难。This can make finding other messages difficult. 在这些情况下,如果任何脚本均不依赖于这些备份日志条目,则可以使用跟踪标志 3226 取消这些条目。In such cases you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. 有关详细信息,请参阅跟踪标志 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

安全性Security

针对数据库备份,TRUSTWORTHY 设置为 OFF。TRUSTWORTHY is set to OFF on a database backup. 有关如何将 TRUSTWORTHY 设置为 ON 的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始, PASSWORDMEDIAPASSWORD 选项不再可用于创建备份。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x) the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. 不过,您仍可以还原使用密码创建的备份。You can still restore backups created with passwords.

PermissionsPermissions

默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户 必须 具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

使用 SQL Server Management StudioSQL Server Management Studio 指定备份任务时,可以通过单击“脚本”按钮并选择脚本目标生成相应的 Transact-SQLTransact-SQL BACKUP 脚本。When you specify a back up task by using SQL Server Management StudioSQL Server Management Studio, you can generate the corresponding Transact-SQLTransact-SQL BACKUP script by clicking the Script button and selecting a script destination.

备份数据库Back up a database

  1. 连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine 实例之后,在“对象资源管理器”中,单击服务器名称以展开服务器树。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展开“数据库”,选择用户数据库,或展开“系统数据库”,选择系统数据库。Expand Databases, and either select a user database or expand System Databases and select a system database.

  3. 右键单击数据库,指向 “任务”,再单击 “备份”Right-click the database, point to Tasks, and then click Back Up. 将出现 “备份数据库” 对话框。The Back Up Database dialog box appears.

    “常规”页General Page

  4. 在“数据库”列表中,确认数据库名称。In the Database drop-down list, verify the database name. 或者从列表中选择其他数据库。Optionally, you can select a different database from the list.

  5. “恢复模式”文本框仅供参考。The Recovery model text box is for reference only. 可以对任意恢复模式(FULLBULK_LOGGEDSIMPLE)执行数据库备份。You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

  6. 从“备份类型”下拉列表中,选择“完整”。In the Backup type drop-down list, select Full.

    请注意,创建了完整数据库备份后,可以创建差异数据库备份;有关详细信息,请参阅 创建差异数据库备份 (SQL Server)Note that after creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).

  7. 还可以选择“仅复制备份”复选框创建仅复制备份。Optionally, you can select the Copy-only backup checkbox to create a copy-only backup. 仅复制备份SQL ServerSQL Server独立于常规备份序列 SQL ServerSQL Server的备份。A copy-only backup is a SQL ServerSQL Server backup that is independent of the sequence of conventional SQL ServerSQL Server backups. 有关详细信息,请参阅仅复制备份 (SQL Server)For more information, see Copy-Only Backups (SQL Server). 仅复制备份不可用于差异备份类型。A copy-only backup is not available for the Differential backup type.

  8. 对于备份组件,请选择“数据库”单选按钮。For Backup component, select the Database radio button.

  9. 在“目标”部分中,使用“备份到”下拉列表选择备份目标。In the Destination section, use the Back up to drop-down list to select the backup destination. 单击“添加”可添加其他备份对象和/或目标。Click Add to add additional backup ojects and/or destinations.

    若要删除备份目标,请选择该备份目标并单击 “删除”To remove a backup destination, select it and click Remove. 若要查看现有备份目标的内容,请选择该备份目标并单击“内容”。To view the contents of an existing backup destination, select it and click Contents.

    介质选项页Media Options Page

  10. 若要查看或选择介质选项,请在 “选择页” 窗格中单击 “介质选项”To view or select the media options, click Media Options in the Select a page pane.

  11. 可以通过单击以下选项之一来选择 “覆盖介质” 选项:Select an Overwrite Media option, by clicking one of the following:

    重要

    如果在“常规”页中选择了“URL”作为备份目标,则禁用“覆盖介质”选项。The Overwrite media option is disabled if you selected URL as the backup destination in the General page. 有关详细信息,请参阅备份数据库(媒体选项页)For more information, see Back Up Database (Media Options Page)

  • 备份到现有介质集Back up to the existing media set

    重要

    如果要使用加密,则请勿选择此选项。If you plan to use encryption, do not select this option. 如果选择此选项,则将禁用 “备份选项” 页中的加密选项。If you select this option, the encryption options in the Backup Options page will be disabled. 追加到现有备份集时不支持加密。Encryption is not supported when appending to the existing backup set.

    对于此选项,请单击 “追加到现有备份集”“覆盖所有现有备份集”For this option, click either Append to the existing backup set or Overwrite all existing backup sets. 有关详细信息,请参阅 媒体集、媒体簇和备份集 (SQL Server)For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

    或者选择 “检查介质集名称和备份集过期时间” ,以使备份操作对介质集和备份集的过期日期和时间进行验证。Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

    或者在 “介质集名称” 文本框中输入名称。Optionally, enter a name in the Media set name text box. 如果没有指定名称,将使用空白名称创建介质集。If no name is specified, a media set with a blank name is created. 如果指定了介质集名称,将检查介质(磁带或磁盘),以确定实际名称是否与此处输入的名称匹配。If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

  • 备份到新介质集并清除所有现有备份集Back up to a new media set, and erase all existing backup sets

    对于该选项,请在 “新建介质集名称” 文本框中输入名称,并在 “新建介质集说明” 文本框中描述介质集(可选)。For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

  1. “可靠性” 部分中,根据需要选中以下任意选项:In the Reliability section, optionally check:

  2. 除非备份的是事务日志(如同“常规”页的“备份类型”部分中指定的一样),否则“事务日志”部分处于不活动状态。The Transaction log section is inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).

  3. 在“磁带驱动器”部分中,如果备份到磁带驱动器(如同“常规”页的“目标”部分指定的一样),则“备份后卸载磁带”选项处于活动状态。In the Tape drive section, the Unload the tape after backup option is active if you are backing up to a tape drive (as specified in the Destination section of the General page). 单击此选项可以激活 “卸载前倒带” 选项。Clicking this option activates the Rewind the tape before unloading option.

    备份选项页Backup Options Page

  4. 若要查看或选择备份选项,请在 “选择页” 窗格中单击 “备份选项”To view or select the backup options, click Backup Options in the Select a page pane.

  5. 在“名称”文本框中可以接受默认的备份集名称,也可以为备份集输入其他名称。In the Name text box either accept the default backup set name, or enter a different name for the backup set.

  6. 可以在“说明”文本框中,输入备份集的说明。In the Description text box, you can optionally enter a description of the backup set.

  7. 指定备份集何时过期以及何时可以覆盖备份集而不用显式跳过过期数据验证:Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration data:

    • 若要使备份集在特定天数后过期,请单击 “之后”(默认选项),并输入备份集从创建到过期所需的天数。To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. 此值范围为 0 到 99999 天;0 天表示备份集将永不过期。This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

      默认值在“服务器属性”对话框(位于“数据库设置”页上)的“默认备份媒体保持期(天)”选项中设置。The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). 若要访问它,请在对象资源管理器中右键单击服务器名称,选择属性,再选择“数据库设置”页。To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

    • 若要使备份集在特定日期过期,请单击 “在”,并输入备份集的过期日期。To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

      有关备份过期日期的详细信息,请参阅 BACKUP (Transact-SQL)For more information about backup expiration dates, see BACKUP (Transact-SQL).

  8. 在“压缩”部分中,使用“设置备份压缩”下拉列表选择所需的压缩级别。In the Compression section, use the Set backup compression drop-down list to select the desired compression level. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本支持 备份压缩and later supports backup compression. 默认情况下,是否压缩备份取决于 backup-compression default 服务器配置选项的值。By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. 但是,不管当前服务器级默认设置如何,都可以通过选中“压缩备份”来压缩备份,并且可以通过选中“不压缩备份”来防止压缩备份。However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    有关备份压缩设置的详细信息,请参阅查看或配置备份压缩默认服务器配置选项For more information on backup compression settings, see View or Configure the backup compression default Server Configuration Option

  9. 在“加密”部分中,使用“加密备份”复选框来决定是否对备份使用加密。In the Encryption section, use the Encrypt backup checkbox to decide whether to use encryption for the backup. 使用“算法”下拉列表选择一种加密算法。Use the Algorithm drop-down list to select an encryption algorithm. 使用“证书”或“非对称密钥”下拉列表选择现有证书或非对称密钥。Use the Certificate or Asymmetric key drop-down list, to select an existing Certificate or Asymmetric key. SQL Server 2014 或更高版本中支持加密。Encryption is supported in SQL Server 2014 or later. 有关加密选项的详细信息,请参阅 备份数据库(“备份选项”页)For more details on the Encryption options, see Back Up Database (Backup Options Page).

你可以使用 维护计划向导 来创建数据库备份。You can use the Maintenance Plan Wizard to create database backups.

示例Examples

A.完整备份到默认位置的磁盘A. Full back up to disk to default location

在此示例中,将 Sales 数据库备份到默认备份位置的磁盘。In this example the Sales database will be backed up to disk at the default backup location. 从未执行 Sales 的备份。A back up of Sales has never been taken.

  1. 在“对象资源管理器”中,连接到一个 SQL Server 数据库引擎实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 展开“数据库”,右键单击 Sales,然后指向“任务”,再单击“备份...”。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

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

B.完整备份到非默认位置的磁盘B. Full back up to disk to non-default location

在此示例中,将 Sales 数据库备份到位于 E:\MSSQL\BAK的磁盘。In this example the Sales database will be backed up to disk at E:\MSSQL\BAK. 之前已执行 Sales 的备份。Previous back ups of Sales have been taken.

  1. 在“对象资源管理器”中,连接到一个 SQL Server 数据库引擎实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 展开“数据库”,右键单击 Sales,然后指向“任务”,再单击“备份...”。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. 在“常规”页的“目标”部分中,从“备份到:”下拉列表中选择“磁盘”。On the General page in the Destination section select Disk from the Back up to: drop-down list.

  4. 单击“删除”,直到删除所有现有备份文件。Click Remove until all existing backup files have been removed.

  5. 单击“添加”“选择备份目标”对话框。Click Add and the Select Backup Destination dialog box will open.

  6. 在“文件名”文本框中输入 E:\MSSQL\BAK\Sales_20160801.bakEnter E:\MSSQL\BAK\Sales_20160801.bak in the file name text box.

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

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

C.创建加密备份C. Create an encrypted backup

在此示例中,将已加密的 Sales 数据库备份到默认备份位置。In this example the Sales database will be backed up with encryption to the default backup location. 已创建 数据库主密钥A database master key has already been created. 已创建名为 证书 MyCertificateA certificate has already been created called MyCertificate. 可以在创建加密备份中找到有关创建数据库主密钥证书的 T-SQL 示例。A T-SQL example of creating a database master key and certificate can be seen at Create an Encrypted Backup.

  1. 在“对象资源管理器”中,连接到一个 SQL Server 数据库引擎实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 展开“数据库”,右键单击 Sales,然后指向“任务”,再单击“备份...”。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. 在“介质选项”页的“覆盖介质”部分中,选择“备份到新介质集,并清除所有现有备份集”。On the Media Options page in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.

  4. 在“备份选项”页的“加密”部分中,选择“加密备份”复选框。On the Backup Options page in the Encryption section select the Encrypt backup check box.

  5. 从“算法”下拉列表中选择“AES 256”。From the Algorithm drop-down list select AES 256.

  6. 从“证书”或“非对称密钥”下拉列表中选择 MyCertificateFrom the Certificate or Asymmetric key drop-down list select MyCertificate.

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

D.备份到 Azure Blob 存储服务D. Back up to the Azure Blob storage service

一般步骤Common Steps

以下三个示例向 Microsoft Azure Blob 存储服务执行完整的 Sales 数据库备份。The three examples below perform a full database backup of Sales to the Microsoft Azure Blob storage service. 存储帐户名称为 mystorageaccountThe storage Account name is mystorageaccount. 容器名称为 myfirstcontainerThe container is called myfirstcontainer. 出于简洁的目的,在此处一次列出前四个步骤,之后所有示例将从 步骤 5 开始。For brevity, the first four steps are listed here once and all examples will start on Step 5.

  1. 在“对象资源管理器”中,连接到一个 SQL Server 数据库引擎实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 展开“数据库”,右键单击 Sales,然后指向“任务”,再单击“备份...”。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. 在“目标”部分中的“常规”页上,从“备份到:”下拉列表中选择“URL”On the General page in the Destination section select URL from the Back up to: drop-down list.

  4. 单击“添加”“选择备份目标”对话框。Click Add and the Select Backup Destination dialog box will open.

    D1.已存在到 URL 和 SQL Server 凭据的条带备份D1. Striped Backup to URL and a SQL Server credential already exists
    已经创建具有读取、写入和表权限的存储访问策略。A stored access policy has been created with read, write, and list rights. 已使用与存储访问策略相关联的共享访问签名创建 SQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy.
    *

    1. 从“Azure 存储容器:”文本框中选择 https://mystorageaccount.blob.core.windows.net/myfirstcontainerSelect https://mystorageaccount.blob.core.windows.net/myfirstcontainer from the Azure storage container: text box

    2. 在“备份文件”文本框中输入 Sales_stripe1of2_20160601.bakIn the Backup File: text box enter Sales_stripe1of2_20160601.bak.

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

    4. 重复步骤 45Repeat Steps 4 and 5.

    5. 在“备份文件”文本框中输入 Sales_stripe2of2_20160601.bakIn the Backup File: text box enter Sales_stripe2of2_20160601.bak.

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

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

    D2.存在共享访问签名,但不存在 SQL Server 凭据D2. A shared access signature exists and a SQL Server Credential does not exist

    1. 在“Azure 存储容器:”文本框中输入 https://mystorageaccount.blob.core.windows.net/myfirstcontainerEnter https://mystorageaccount.blob.core.windows.net/myfirstcontainer in the Azure storage container: text box

    2. 在“共享访问策略:”文本框中输入共享访问签名。Enter the shared access signature in the Shared Access Policy: text box.

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

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

    D3.共享访问签名不存在D3. A shared access signature does not exist

    1. 单击“新建容器”按钮,将会打开“连接到 Microsoft 订阅”对话框。Click the New container button and the Connect to a Microsoft Subscription dialog box will open.

    2. 完成“连接到 Microsoft 订阅”对话框,然后单击“确定”,返回到“选择备份目标”对话框。Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select Backup Destination dialog box. 有关其他信息,请参阅 连接到 Microsoft Azure 订阅See See Connect to a Microsoft Azure Subscription for additional information.

    3. 单击“选择备份目标”对话框中的“确定”。Click OK at the Select Backup Destination dialog box.

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

使用 Transact-SQLUsing Transact-SQL

创建完整数据库备份Create a full database backup

  1. 执行 BACKUP DATABASE 语句可以创建完整数据库备份,同时指定:Execute the BACKUP DATABASE statement to create the full database backup, specifying:

    • 要备份的数据库的名称。The name of the database to back up.

    • 写入完整数据库备份的备份设备。The backup device where the full database backup is written.

      完整数据库备份的基本 Transact-SQLTransact-SQL 语法如下:The basic Transact-SQLTransact-SQL syntax for a full database backup is:

      BACKUP DATABASE databaseBACKUP DATABASE database

      TO backup_device [ ,...n ]TO backup_device [ ,...n ]

      [ WITH with_options [ ,...o ] ] ;[ WITH with_options [ ,...o ] ] ;

    选项Option “说明”Description
    databasedatabase 要备份的数据库。Is the database that is to be backed up.
    backup_device [ ,...n ]backup_device [ ,...n ] 指定一个列表,它包含 1 至 64 个用于备份操作的备份设备。Specifies a list of from 1 to 64 backup devices to use for the backup operation. 您可以指定物理备份设备,也可以指定对应的逻辑备份设备(如果已定义)。You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. 若要指定物理备份设备,请使用 DISK 或 TAPE 选项:To specify a physical backup device, use the DISK or TAPE option:

    { DISK | TAPE } =物理_备份_设备_名称{ DISK | TAPE } =physical_backup_device_name

    有关详细信息,请参阅 备份设备 (SQL Server)For more information, see Backup Devices (SQL Server).
    WITH with_options [ ,...o ]WITH with_options [ ,...o ] 您也可以指定一个或多个附加选项 oOptionally, specifies one or more additional options, o. 有关某些基本 WITH 选项的信息,请参阅步骤 2。For information about some of the basic with options, see step 2.
  2. (可选)指定一个或多个 WITH 选项。Optionally, specify one or more WITH options. 下面描述了几个基本 WITH 选项。A few basic WITH options are described here. 有关所有 WITH 选项的详细信息,请参阅 BACKUP (Transact-SQL)For information about all the WITH options, see BACKUP (Transact-SQL).

    • 基本备份集 WITH 选项:Basic backup set WITH options:

      { COMPRESSION | NO_COMPRESSION }{ COMPRESSION | NO_COMPRESSION }
      SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本中,指定是否为此备份执行 备份压缩 ,该设置将替代服务器级默认设置。In SQL Server 2008 EnterpriseSQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.

      ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY)ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY)
      仅在 SQL Server 2014 或更高版本中,指定要使用的加密算法以及要用于保护加密的证书或非对称密钥。In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.

      DESCRIPTION = { 'text' | @text_variable }DESCRIPTION = { 'text' | @text_variable }
      指定说明备份集的自由格式文本。Specifies the free-form text that describes the backup set. 该字符串最长可达 255 个字符。The string can have a maximum of 255 characters.

      NAME = { backup_set_name | @backup\_set\_name\_var }NAME = { backup_set_name | @backup_set_name_var }
      指定备份集的名称。Specifies the name of the backup set. 名称最长可达 128 个字符。Names can have a maximum of 128 characters. 如果未指定 NAME,它将为空。If NAME is not specified, it is blank.

    • 基本备份集 WITH 选项:Basic backup set WITH options:

      默认情况下,BACKUP 将备份追加到现有介质集中,并保留现有备份集。By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. 若要显式指定此设置,请使用 NOINIT 选项。To explicitly specify this, use the NOINIT option. 有关附加到现有备份集的信息,请参阅 媒体集、媒体簇和备份集 (SQL Server)For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).

      或者,若要将备份介质格式化,可以使用 FORMAT 选项:Alternatively, to format the backup media, use the FORMAT option:

      FORMAT [ , MEDIANAME= { media_name | @media\_name\_variable } ] [ , MEDIADESCRIPTION = { text | @text\_variable } ]FORMAT [ , MEDIANAME= { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
      当您第一次使用介质或者希望覆盖所有现有数据时可以使用 FORMAT 子句。Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. 根据需要,可以为新介质指定介质名称和说明。Optionally, assign the new media a media name and description.

      重要

      当使用 BACKUP 语句的 FORMAT 子句时要十分小心,因为它会破坏以前存储在备份介质中的所有备份。Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.

示例 (Transact-SQL)Examples (Transact-SQL)

A.备份到磁盘设备A. Back up to a disk device

下面的示例通过使用 AdventureWorks2012AdventureWorks2012 创建新的介质集,将整个 FORMAT 数据库备份到磁盘。The following example backs up the complete AdventureWorks2012AdventureWorks2012 database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

B.备份到磁带设备B. Back up to a tape device

下面的示例将完整的 AdventureWorks2012AdventureWorks2012 数据库备份到磁带上,并将该备份追加到以前的备份中。The following example backs up the complete AdventureWorks2012AdventureWorks2012 database to tape, appending the backup to the previous backups.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO TAPE = '\\.\Tape0'  
   WITH NOINIT,  
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

C.备份到逻辑磁带设备C. Back up to a logical tape device

下例为某个磁带驱动器创建一个逻辑备份设备,The following example creates a logical backup device for a tape drive. 然后将完整的 AdventureWorks2012AdventureWorks2012 数据库备份到该设备上。The example then backs up the complete AdventureWorks2012AdventureWorks2012 database to that device.

-- Create a logical backup device,   
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.  
USE master;  
GO  
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO AdventureWorks2012_Bak_Tape  
   WITH FORMAT,  
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',  
      MEDIADESCRIPTION = '\\.\tape0',   
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

使用 PowerShellUsing PowerShell

使用 Backup-SqlDatabase cmdlet。Use the Backup-SqlDatabase cmdlet. 若要显式指示这是完整数据库备份,请使用其默认值 Database 指定 -BackupAction参数。To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. 对于完整数据库备份而言,此参数是可选的。This parameter is optional for full database backups.

示例Examples

A.完整的本地备份A. Full local backup

下面的示例在服务器实例 MyDB 的默认备份位置创建数据库 Computer\Instance的完整数据库备份。The following example creates a full database backup of the MyDB database to the default backup location of the server instance Computer\Instance. 此示例也可以指定 -BackupAction DatabaseOptionally, this example specifies -BackupAction Database.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  

B.完整备份到 Microsoft AzureB. Full backup to Microsoft Azure

下面的示例向 Microsoft Azure Blob 存储服务在 Sales 实例上创建 MyServer 数据库的完整备份。The following example creates a full backup of the database Sales on the MyServer instance to the Microsoft Azure Blob Storage service. 已经创建具有读取、写入和表权限的存储访问策略。A stored access policy has been created with read, write, and list rights. 已使用与存储访问策略相关联的共享访问签名创建 SQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. PowerShell 命令使用 BackupFile 参数指定位置 (URL) 和备份文件名。The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.

import-module sqlps;
$container = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer';
$FileName = 'Sales.bak';
$database = 'Sales';
$BackupFile = $container + '/' + $FileName ;

Backup-SqlDatabase -ServerInstance "MyServer" -Database $database -BackupFile $BackupFile;

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

相关任务Related Tasks

另请参阅See also

SQL Server 备份和还原操作故障排除 Troubleshooting SQL Server backup and restore operations
备份概述 (SQL Server) Backup Overview (SQL Server)
事务日志备份 (SQL Server) Transaction Log Backups (SQL Server)
媒体集、媒体簇和备份集 (SQL Server) Media Sets, Media Families, and Backup Sets (SQL Server)
sp_addumpdevice (Transact-SQL) sp_addumpdevice (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
备份数据库(“常规”页) Back Up Database (General Page)
备份数据库(“备份选项”页) Back Up Database (Backup Options Page)
差异备份 (SQL Server) Differential Backups (SQL Server)
完整数据库备份 (SQL Server)Full Database Backups (SQL Server)