创建完整数据库备份 (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 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.

限制和局限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 an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.

建议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).

SecuritySecurity

针对数据库备份,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.

权限Permissions

默认情况下,为 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.
  4. 从下拉列表中选择“数据库” 。Select the Database from the drop-down list.
  5. 从“备份类型”下拉列表中,选择“完整”。 In the Backup type drop-down list, select Full.
  6. 在“备份组件”下,选择“数据库” 。Under Backup component, select Database.
  7. 在“目标”部分中,使用“备份到”下拉列表选择备份目标。 In the Destination section, use the Back up to drop-down list to select the backup destination. 单击“添加”可添加其他备份对象和/或目标 。Click Add to add additional backup objects 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.
  8. (可选)查看“介质选项”和“备份选项”页面下的其他可用设置 。(Optionally) Review the other available settings under the Media Options and Backup Options pages. 要详细了解各种备份选项,请参阅常规页介质选项页备份选项页For more information about the various backup options, see General page, Media options page, and Backup options page.

其他信息Additional information

  • 创建完整数据库备份后,可创建差异数据库备份;有关详细信息,请参阅 创建差异数据库备份 (SQL Server)After creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).
  • 还可以选择“仅复制备份” 复选框创建仅复制备份。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.
  • 如果要备份到 URL,则可禁用“介质选项”页上的“覆盖介质”选项 。The Overwrite media option may be disabled on the Media Options page if you are backing up to URL.

SSMS 示例SSMS Examples

对于以下示例,使用以下 Transact-SQL 代码创建测试数据库:For the following examples, create a test database with the following Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR(100) NOT NULL,
    dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO


USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

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

在此示例中,将 SQLTestDB 数据库备份到默认备份位置处的磁盘。In this example, the SQLTestDB database will be backed up to disk at the default backup location. 从未执行 SQLTestDB 的备份。A backup of SQLTestDB 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. 展开“数据库” ,右键单击 SQLTestDB,然后指向“任务” ,再单击“备份...” 。Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up....
  3. 选择“确定” 。Select OK.

执行 SQL 备份

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

在此示例中,将 SQLTestDB 数据库备份到位于 F:\MSSQL\BAK 的磁盘。In this example, the SQLTestDB database will be backed up to disk at F:\MSSQL\BAK. 之前已执行 SQLTestDB 的备份。Previous back ups of SQLTestDB 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. 选择“删除”,直到所有现有备份文件均已删除 。Select Remove until all existing backup files have been removed.
  5. 选择“添加”,这将打开“选择备份目标”对话框 。Select Add and the Select Backup Destination dialog box will open.
  6. 在“文件名”文本框中输入 F:\MSSQL\BAK\Sales_20160801.bakEnter F:\MSSQL\BAK\Sales_20160801.bak in the file name text box.
  7. 选择“确定” 。Select OK.
  8. 选择“确定” 。Select OK.

更改 DB 位置

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

在此示例中,将已加密的 SQLTestDB 数据库备份到默认备份位置。In this example, the SQLTestDB database will be backed up with encryption to the default backup location.

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

  2. 打开“新建查询”窗口,然后执行以下命令在 SQLTestDB 数据库中创建数据库主密钥证书Open a New Query window and execute the following commands to create a database master key and a certificate within your SQLTestDB database.

    USE [SQLTestDB]
    
    -- Create the database master key
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
    
    
    -- Create the certificate
    CREATE CERTIFICATE MyCertificate   
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
    EXPIRY_DATE = '20201031';  
    GO  
    
  3. 在对象资源管理器中,展开“数据库”,右键单击 SQLTestDB,然后指向“任务”,再单击“备份...” 。In Object Explorer, Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up....

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

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

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

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

  8. 选择“确定” 。Select OK.

加密备份

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

以下三个示例向 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.

已存在到 URL 的条带备份和 SQL Server 凭据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.

存在共享访问签名,但不存在 SQL Server 凭据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.

共享访问签名不存在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 } :在 SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本中,指定是否为此备份执行 备份压缩 ,该设置将替代服务器级默认设置。{ COMPRESSION | NO_COMPRESSION }: 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) :仅在 SQL Server 2014 或更高版本中,指定要使用的加密算法以及要用于保护加密的证书或非对称密钥。ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY): 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.

默认情况下,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 示例Transact-SQL Examples

对于以下示例,使用以下 Transact-SQL 代码创建测试数据库:For the following examples, create a test database with the following Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR(100) NOT NULL,
    dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO


USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

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

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

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

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

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

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

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

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

-- Create a logical backup device,   
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.  
USE master;  
GO  
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;  
GO  
BACKUP DATABASE SQLTestDB  
   TO SQLTestDB_Bak_Tape  
   WITH FORMAT,  
      MEDIANAME = 'SQLTestDB_Bak_Tape',  
      MEDIADESCRIPTION = '\\.\tape0',   
      NAME = 'Full Backup of SQLTestDB';  
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.

PowerShell 示例Powershell 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.B. 完整备份到 Microsoft AzureFull backup to Microsoft Azure

下面的示例向 Microsoft Azure Blob 存储服务在 MyServer 实例上创建 Sales 数据库的完整备份。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