BACKUP (Transact-SQL)BACKUP (Transact-SQL)

備份 SQL 資料庫。Backs up a SQL database.

按一下下列其中一個索引標籤,以查看您所使用特定 SQL 版本的語法、引數、備註、權限和範例。Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.

如需語法慣例的詳細資訊,請參閱 Transact-SQL 語法慣例For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

按一下產品!Click a product!

在下一行中,按一下您感興趣的產品名稱。In the following row, click whichever product name you are interested in. 視您所按下的產品而定,此點選會在這裡顯示不同的內容:The click displays different content here, appropriate for whichever product you click:

* SQL Server *  * SQL Server *   SQL Database
受控執行個體
SQL Database
managed instance
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

SQL ServerSQL Server

備份完整 SQL ServerSQL Server 資料庫以建立資料庫備份,或備份資料庫的一或多個檔案或檔案群組以建立檔案備份 (BACKUP DATABASE)。Backs up a complete SQL ServerSQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). 同時,可在完整復原模式或大量記錄復原模式下備份資料庫的交易記錄,以建立記錄備份 (BACKUP LOG)。Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG).

語法Syntax

--Backing Up a Whole Database
BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL
           | <general_WITH_options> [ ,...n ] } ]
[;]

--Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }
 <file_or_filegroup> [ ,...n ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Creating a Partial Backup
BACKUP DATABASE { database_name | @database_name_var }
 READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Backing Up the Transaction Log (full and bulk-logged recovery models)
BACKUP LOG
  { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { <general_WITH_options> | \<log-specific_optionspec> } [ ,...n ] ]
[;]

<backup_device>::=
 {
  { logical_device_name | @logical_device_name_var }
 | {   DISK
     | TAPE
     | URL } =
     { 'physical_device_name' | @physical_device_name_var | 'NUL' }
 }

<MIRROR TO clause>::=
 MIRROR TO <backup_device> [ ,...n ]

<file_or_filegroup>::=
 {
   FILE = { logical_file_name | @logical_file_name_var }
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
 }

<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

<general_WITH_options> [ ,...n ]::=
--Backup Set Options
   COPY_ONLY
 | { COMPRESSION | NO_COMPRESSION }
 | DESCRIPTION = { 'text' | @text_variable }
 | NAME = { backup_set_name | @backup_set_name_var }
 | CREDENTIAL
 | ENCRYPTION
 | FILE_SNAPSHOT
 | { EXPIREDATE = { 'date' | @date_var }
        | RETAINDAYS = { days | @days_var } }

--Media Set Options
   { NOINIT | INIT }
 | { NOSKIP | SKIP }
 | { NOFORMAT | FORMAT }
 | MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Tape Options
   { REWIND | NOREWIND }
 | { UNLOAD | NOUNLOAD }

--Log-specific Options
   { NORECOVERY | STANDBY = undo_file_name }
 | NO_TRUNCATE

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   `SERVER CERTIFICATE` = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

引數Arguments

DATABASE 指定完整的資料庫備份。DATABASE Specifies a complete database backup. 如果指定了檔案和檔案群組清單,就只會備份這些檔案和檔案群組。If a list of files and filegroups is specified, only those files and filegroups are backed up. 在完整或差異資料庫備份期間,SQL ServerSQL Server 會備份足夠的交易記錄,以便在還原備份時,產生一致的資料庫。During a full or differential database backup, SQL ServerSQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.

當您還原 BACKUP DATABASE 所建立的備份 (「資料備份」 ) 時,就會還原整個備份。When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. 只有記錄備份可以還原至備份內的特定時間或交易。Only a log backup can be restored to a specific time or transaction within the backup.

注意

master 資料庫上只能執行完整資料庫備份。Only a full database backup can be performed on the master database.

LOGLOG

指定只備份交易記錄。Specifies a backup of the transaction log only. 記錄的備份是從最後執行成功的記錄備份至目前的記錄結尾。The log is backed up from the last successfully executed log backup to the current end of the log. 您必須先建立完整備份,才能建立第一個記錄備份。Before you can create the first log backup, you must create a full backup.

您可以透過在 RESTORE LOG 陳述式中指定 WITH STOPATSTOPATMARKSTOPBEFOREMARK,以將記錄備份還原至備份內的特定時間或交易。You can restore a log backup to a specific time or transaction within the backup by specifying WITH STOPAT, STOPATMARK, or STOPBEFOREMARK in your RESTORE LOG statement.

注意

建立典型的記錄備份之後,除非您指定 WITH NO_TRUNCATECOPY_ONLY,否則有些交易記錄檔記錄會變成非使用中狀態。After a typical log backup, some transaction log records become inactive, unless you specify WITH NO_TRUNCATE or COPY_ONLY. 當一個或多個虛擬記錄檔案中的所有記錄變成非使用中狀態之後,記錄會發生截斷。The log is truncated after all the records within one or more virtual log files become inactive. 如果記錄在例行的記錄備份之後並未截斷,可能會發生延遲記錄截斷。If the log is not being truncated after routine log backups, something might be delaying log truncation. 如需詳細資訊,請參閱可能會延遲記錄截斷的因素For more information, see Factors that can delay log truncation.

{ database_name | @ database_name_var } 這是要備份交易記錄、部分資料庫或完整資料庫的來源資料庫。{ database_name | @database_name_var } Is the database from which the transaction log, partial database, or complete database is backed up. 如果這個名稱是以變數 ( @ database_name_var) 的形式提供,您還可以將這個名稱指定為字串常數 ( @ database_name_var = database name),或指定為字元字串資料類型的變數,但 ntexttext 資料類型除外。If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.

注意

資料庫鏡像合作關係中的鏡像資料庫無法備份。The mirror database in a database mirroring partnership cannot be backed up.

<file_or_filegroup> [ , ...n ] 只能搭配 BACKUP DATABASE 使用,可用來指定要包含在檔案備份中的資料庫檔案或檔案群組,或是指定要包含在部分備份中的唯讀檔案或檔案群組。<file_or_filegroup> [ ,...n ] Used only with BACKUP DATABASE, specifies a database file or filegroup to include in a file backup, or specifies a read-only file or filegroup to include in a partial backup.

FILE = { logical_file_name | @ logical_file_name_var } 這是指要包含在備份中的檔案邏輯名稱,或是其值等於該檔案邏輯名稱的變數。FILE = { logical_file_name | @logical_file_name_var } Is the logical name of a file or a variable whose value equates to the logical name of a file that is to be included in the backup.

FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } 這是指要包含在備份中的檔案群組邏輯名稱,或是其值等於該檔案群組邏輯名稱的變數。FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } Is the logical name of a filegroup or a variable whose value equates to the logical name of a filegroup that is to be included in the backup. 在簡單復原模式之下,只允許唯讀檔案群組使用檔案群組備份。Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.

注意

當資料庫備份因資料庫大小和效能需求而不可行時,請考慮使用檔案備份。Consider using file backups when the database size and performance requirements make a database backup impractical. NUL 裝置可用來測試備份的效能,但不應用於生產環境。The NUL device can be used to test the performance of backups, but should not be used in production environments.

n 這是一個預留位置,表示可以在逗號分隔清單中指定多個檔案和檔案群組。n Is a placeholder that indicates that multiple files and filegroups can be specified in a comma-separated list. 數目沒有限制。The number is unlimited.

如需詳細資訊,請參閱完整檔案備份備份檔案和檔案群組For more information, see Full File Backups and Back Up Files and Filegroups.

READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } [ , ...n ] ] 指定部分備份。READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ] Specifies a partial backup. 部分備份包含資料庫中所有的讀取/寫入檔案:主要檔案群組和任何一種讀取/寫入次要檔案群組,以及任何指定的唯讀檔案或檔案群組。A partial backup includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups.

READ_WRITE_FILEGROUPS 指定要在部分備份進行備份的所有讀取/寫入檔案群組。READ_WRITE_FILEGROUPS Specifies that all read/write filegroups be backed up in the partial backup. 如果資料庫是唯讀的,READ_WRITE_FILEGROUPS 只會包括主要檔案群組。If the database is read-only, READ_WRITE_FILEGROUPS includes only the primary filegroup.

重要

使用 FILEGROUP 取代 READ_WRITE_FILEGROUPS 來明確列出讀取/寫入檔案群組,以建立檔案備份。Explicitly listing the read/write filegroups by using FILEGROUP instead of READ_WRITE_FILEGROUPS creates a file backup.

FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } 這是指要包含在部分備份中的唯讀檔案群組邏輯名稱,或是其值等於該唯讀檔案群組邏輯名稱的變數。FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } Is the logical name of a read-only filegroup or a variable whose value equates to the logical name of a read-only filegroup that is to be included in the partial backup. 如需詳細資訊,請參閱本主題前面的 "<file_or_filegroup>"。For more information, see "<file_or_filegroup>," earlier in this topic.

n 這是一個預留位置,表示可以在逗號分隔清單中指定多個唯讀檔案群組。n Is a placeholder that indicates that multiple read-only filegroups can be specified in a comma-separated list.

如需部分備份的詳細資訊,請參閱部分備份For more information about partial backups, see Partial Backups.

TO <backup_device> [ , ...n ] 指出隨附的備份裝置集是未鏡像的媒體集,或是鏡像媒體集內的前幾個鏡像 (已針對其宣告了一或多個 MIRROR TO 子句)。TO <backup_device> [ ,...n ] Indicates that the accompanying set of backup devices is either an unmirrored media set or the first of the mirrors within a mirrored media set (for which one or more MIRROR TO clauses are declared).

<backup_device><backup_device>

指定備份作業要使用的邏輯或實體備份裝置。Specifies a logical or physical backup device to use for the backup operation.

{ logical_device_name | @ logical_device_name_var } 適用於: SQL Server 這是用來備份資料庫的備份裝置邏輯名稱。{ logical_device_name | @logical_device_name_var } Applies to: SQL Server Is the logical name of the backup device to which the database is backed up. 邏輯名稱必須遵照識別碼的規則。The logical name must follow the rules for identifiers. 如果備份裝置名稱是以變數 (@logical_device_name_var) 提供,就可將它指定為字串常數 (@logical_device_name_var = 邏輯備份裝置名稱) 或任何字元字串資料類型的變數,但 ntexttext 資料類型除外。If supplied as a variable (@logical_device_name_var), the backup device name can be specified either as a string constant (@logical_device_name_var= logical backup device name) or as a variable of any character string data type except for the ntext or text data types.

{ DISK | TAPE | URL} = { ' physical_device_name ' | @ physical_device_name_var | 'NUL' } 用於: DISK、TAPE 和 URL 適用於 SQL Server。{ DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' } Applies to: DISK, TAPE, and URL apply to SQL Server. 指定磁碟檔案或磁帶裝置,或是 Microsoft Azure Blob 儲存體服務。Specifies a disk file or tape device, or a Microsoft Azure Blob storage service. URL 格式可用來建立備份至 Microsoft Azure 儲存體服務。The URL format is used for creating backups to the Microsoft Azure storage service. 如需詳細資訊和範例,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For more information and examples, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 如需教學課程,請參閱教學課程:SQL Server 備份及還原至 Microsoft Azure Blob 儲存體服務For a tutorial, see Tutorial: SQL Server Backup and Restore to Microsoft Azure Blob Storage Service.

注意

NUL 磁碟裝置將捨棄傳送給它的所有資訊,而且只應用於測試。The NUL disk device will discard all information sent to it and should only be used for testing. 這不適用於生產環境。This is not for production use.

重要

SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 開始至 SQL Server 2014 (12.x)SQL Server 2014 (12.x),當您備份至 URL 時,可以只備份到單一裝置。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2014 (12.x)SQL Server 2014 (12.x), you can only backup to a single device when backing up to URL. 為了在備份到 URL 時能夠備份到多部裝置,您必須使用 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017,而且必須使用共用存取簽章 (SAS) 權杖。In order to backup to multiple devices when backing up to URL, you must use SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and you must use Shared Access Signature (SAS) tokens. 如需建立共用存取簽章的範例,請參閱 SQL Server 備份至 URL在 Azure 儲存體上使用 Powershell 搭配共用存取簽章 (SAS) 權杖來簡化 SQL 認證的建立 (英文)。For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.

URL 適用於SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 至 SQL Server 2017SQL Server 2017)。URL applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017SQL Server 2017).

在 BACKUP 陳述式內指定磁碟裝置之前,該裝置不需要存在。A disk device does not have to exist before it is specified in a BACKUP statement. 如果實體裝置存在,且 BACKUP 陳述式並未指定 INIT 選項,就會將備份附加至裝置中。If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

注意

NUL 裝置將捨棄傳送到此檔案的所有輸入,不過,備份仍會將所有頁面標記為已備份。The NUL device will discard all input sent to this file, however the backup will still mark all pages as backed up.

如需詳細資訊,請參閱備份裝置For more information, see Backup Devices.

注意

未來的 SQL ServerSQL Server 版本將移除 TAPE 選項。The TAPE option will be removed in a future version of SQL ServerSQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

n 這是一個預留位置,表示可以在逗號分隔清單中指定最多達 64 個備份裝置。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.

MIRROR TO <backup_device> [ , ...n ] 指定一組最多三個的次要備份裝置,其中每個裝置都會鏡像處理 TO 子句中所指定的備份裝置。MIRROR TO <backup_device> [ ,...n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. MIRROR TO 子句必須指定與 TO 子句相同的備份裝置類型和數目。The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. 最大 MIRROR TO 子句數目是 3。The maximum number of MIRROR TO clauses is three.

只有 SQL ServerSQL Server 的 Enterprise 版本才提供這個選項。This option is available only in the Enterprise edition of SQL ServerSQL Server.

注意

如果 MIRROR TO = DISK,BACKUP 會自動根據磁碟的磁區大小,來判斷磁碟裝置的適當區塊大小。For MIRROR TO = DISK, BACKUP automatically determines the appropriate block size for disk devices based on the sector size of the disk. 如果使用與指定為主要備份裝置的磁碟不同的磁區大小來格式化 MIRROR TO 磁碟,備份命令將會失敗。If the MIRROR TO disk is formatted with a different sector size than the disk specified as the primary backup device, the backup command will fail. 為了鏡像具有不同磁區大小的裝置備份,必須指定 BLOCKSIZE 參數,且應該設定為所有目標裝置之間的最大磁區大小。In order to mirror backups to devices that have different sector sizes, the BLOCKSIZE parameter must be specified, and should be set to the highest sector size amongst all the target devices. 如需區塊大小的詳細資訊,請參閱本主題稍後的<BLOCKSIZE>。For more information about block size, see "BLOCKSIZE" later in this topic.

<backup_device> 請參閱本節前面的 "<backup_device>"。<backup_device> See "<backup_device>," earlier in this section.

n 這是一個預留位置,表示可以在逗號分隔清單中指定最多達 64 個備份裝置。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list. MIRROR TO 子句中的裝置數目必須等於 TO 子句中的裝置數目。The number of devices in the MIRROR TO clause must equal the number of devices in the TO clause.

如需詳細資訊,請參閱本主題稍後備註一節中的<鏡像媒體集中的媒體家族>。For more information, see "Media Families in Mirrored Media Sets" in the Remarks section, later in this topic.

[ next-mirror-to ] 這是一個預留位置,表示單一 BACKUP 陳述式除了可以包含單一 TO 子句,還可以包含最多三個 MIRROR TO 子句。[ next-mirror-to ] Is a placeholder that indicates that a single BACKUP statement can contain up to three MIRROR TO clauses, in addition to the single TO clause.

WITH 選項WITH Options

指定要搭配備份作業使用的選項。Specifies options to be used with a backup operation.

CREDENTIAL 適用於SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 至 SQL Server 2017SQL Server 2017)。CREDENTIAL Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017SQL Server 2017). 只有當建立備份到 Microsoft Azure Blob 儲存體服務時才使用。Used only when creating a backup to the Microsoft Azure Blob storage service.

FILE_SNAPSHOT 適用於SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。FILE_SNAPSHOT Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

使用 Azure Blob 儲存體服務來儲存所有 SQL Server 資料庫檔案時,用來建立資料庫檔案的 Azure 快照集。Used to create an Azure snapshot of the database files when all of the SQL Server database files are stored using the Azure Blob storage service. 如需詳細資訊,請參閱 Microsoft Azure 中的 SQL Server 資料檔案For more information, see SQL Server Data Files in Microsoft Azure. SQL ServerSQL Server 快照集備份會以一致的狀態建立資料庫檔案 (資料和記錄檔) 的 Azure 快照集。Snapshot Backup takes Azure snapshots of the database files (data and log files) at a consistent state. 一組一致的 Azure 快照集會組成一個備份,並記錄於備份檔案中。A consistent set of Azure snapshots make up a backup and are recorded in the backup file. BACKUP DATABASE TO URL WITH FILE_SNAPSHOTBACKUP LOG TO URL WITH FILE_SNAPSHOT 之間的唯一差異是,後者也會截斷交易記錄,但前者不會。The only difference between BACKUP DATABASE TO URL WITH FILE_SNAPSHOT and BACKUP LOG TO URL WITH FILE_SNAPSHOT is that the latter also truncates the transaction log while the former does not. 使用 SQL ServerSQL Server 快照集備份,在 SQL ServerSQL Server 建立備份鏈結所需的初始完整備份之後,只需使用單一交易記錄備份就能將資料庫還原至交易記錄備份的時間點。With SQL ServerSQL Server Snapshot Backup, after the initial full backup that is required by SQL ServerSQL Server to establish the backup chain, only a single transaction log backup is required to restore a database to the point in time of the transaction log backup. 此外,只需要兩個交易記錄備份,就能將資料庫還原至這兩個交易記錄備份時間之間的時間點。Furthermore, only two transaction log backups are required to restore a database to a point in time between the time of the two transaction log backups.

DIFFERENTIALDIFFERENTIAL

只能搭配 BACKUP DATABASE 使用,可用來指定資料庫或檔案備份應該只含有資料庫或檔案在前次完整備份之後又變更過的部分。Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup. 差異備份所用的空間通常會比完整備份少。A differential backup usually takes up less space than a full backup. 使用這個選項,便不需要套用自前次完整備份之後所執行的所有個別記錄備份。Use this option so that all individual log backups performed since the last full backup do not have to be applied.

注意

根據預設,BACKUP DATABASE 會建立完整備份。By default, BACKUP DATABASE creates a full backup.

如需詳細資訊,請參閱差異備份For more information, see Differential Backups.

ENCRYPTION 用來指定備份的加密。ENCRYPTION Used to specify encryption for a backup. 您可以指定加密演算法來加密備份,或指定 NO_ENCRYPTION 不加密備份。You can specify an encryption algorithm to encrypt the backup with or specify NO_ENCRYPTION to not have the backup encrypted. 加密是有助於保護備份檔案的建議作法。Encryption is recommended practice to help secure backup files. 您可以指定的演算法清單包括:The list of algorithms you can specify are:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果您選擇加密,則也需要使用加密程式選項指定加密程式:If you choose to encrypt you will also have to specify the encryptor using the encryptor options:

  • SERVER CERTIFICATE = Encryptor_NameSERVER CERTIFICATE = Encryptor_Name
  • SERVER ASYMMETRIC KEY = Encryptor_NameSERVER ASYMMETRIC KEY = Encryptor_Name

SERVER CERTIFICATESERVER ASYMMETRIC KEY 是在資料庫 master 中建立的憑證與非對稱金鑰。The SERVER CERTIFICATE and SERVER ASYMMETRIC KEY are a certificate and an asymmetric key created in master database. 如需詳細資訊,請各別參閱 CREATE CERTIFICATE 以及 CREATE ASYMMETRIC KEYFor more information see CREATE CERTIFICATE and CREATE ASYMMETRIC KEY respectively.

警告

搭配 FILE_SNAPSHOT 引數使用加密時,中繼資料檔案本身會使用指定的加密演算法進行加密,而系統會確認已針對資料庫完成透明資料加密 (TDE)When encryption is used in conjunction with the FILE_SNAPSHOT argument, the metadata file itself is encrypted using the specified encryption algorithm and the system verifies that Transparent Data Encryption (TDE) was completed for the database. 對於資料本身則不會進行任何其他加密。No additional encryption happens for the data itself. 如果未加密資料庫,或者發出備份陳述式之前未完成加密,備份就會失敗。The backup fails if the database was not encrypted or if the encryption was not completed before the backup statement was issued.

備份組選項Backup Set Options

這些選項會處理這個備份作業所建立的備份組。These options operate on the backup set that is created by this backup operation.

注意

若要指定還原作業的備份組,請使用 FILE = <backup_set_file_number> 選項。To specify a backup set for a restore operation, use the FILE = <backup_set_file_number> option. 如需如何指定備份組的詳細資訊,請參閱 RESTORE 引數中的<指定備份組>。For more information about how to specify a backup set, see "Specifying a Backup Set" in RESTORE Arguments.

COPY_ONLY:指定備份為「僅複製備份」 ,這不會影響正常的備份順序。COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. 僅複製備份的建立與定期排程的傳統備份無關。A copy-only backup is created independently of your regularly scheduled, conventional backups. 僅複製備份並不會影響資料庫的整體備份和還原程序。A copy-only backup does not affect your overall backup and restore procedures for the database.

僅複製備份應該用於需要執行備份來達成特定用途的情況 (例如,在線上檔案還原之前備份記錄檔)。Copy-only backups should be used in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. 通常,僅複製記錄備份用過一次後便會刪除。Typically, a copy-only log backup is used once and then deleted.

  • 搭配 BACKUP DATABASE 使用時,COPY_ONLY 選項會建立無法作為差異基底使用的完整備份。When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. 差異點陣圖不會更新,而且差異備份的行為會如同僅複製備份並不存在。The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. 後續的差異備份會使用最新的傳統完整備份做為其基底。Subsequent differential backups use the most recent conventional full backup as their base.

    重要

    如果同時使用 DIFFERENTIALCOPY_ONLY,即會忽略 COPY_ONLY 並建立差異備份。If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created.

  • 搭配 BACKUP LOG 使用時,COPY_ONLY 選項會建立「僅複製記錄備份」 ,這不會截斷交易記錄。When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. 僅複製記錄備份不會影響記錄檔鏈結,而且其他記錄備份的行為會如同僅複製備份並不存在。The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.

如需詳細資訊,請參閱只複製備份For more information, see Copy-Only Backups.

{ COMPRESSION | NO_COMPRESSION } 只有在 SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更新版本中,才指定是否要在此備份上執行備份壓縮,以覆寫伺服器層級的預設值。{ COMPRESSION | NO_COMPRESSION } In SQL Server 2008 EnterpriseSQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default.

進行安裝時,預設行為是不壓縮備份。At installation, the default behavior is no backup compression. 但是,您可以設定 backup compression default 伺服器設定選項來變更此預設值。But this default can be changed by setting the backup compression default server configuration option. 如需檢視此選項目前值的資訊,請參閱檢視或變更伺服器屬性For information about viewing the current value of this option, see View or Change Server Properties.

如需搭配已啟用透明資料加密 (TDE) 之資料庫使用備份壓縮的相關資訊,請參閱備註一節。For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.

COMPRESSION 明確啟用備份壓縮。COMPRESSION Explicitly enables backup compression.

NO_COMPRESSION 明確停用備份壓縮。NO_COMPRESSION Explicitly disables backup compression.

DESCRIPTION = { ' text ' | @ text_variable } 指定描述備份組的自由格式文字。DESCRIPTION = { 'text' | @text_variable } Specifies the free-form text describing the backup set. 這個字串最多可有 255 個字元。The string can have a maximum of 255 characters.

NAME = { backup_set_name | @ backup_set_var } 指定備份組的名稱。NAME = { backup_set_name | @backup_set_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.

{ EXPIREDATE =' date ' | RETAINDAYS = days } 指定何時可以覆寫這個備份的備份組。{ EXPIREDATE ='date' | RETAINDAYS = days } Specifies when the backup set for this backup can be overwritten. 如果同時使用這兩個選項,RETAINDAYS 會優先於 EXPIREDATE。If these options are both used, RETAINDAYS takes precedence over EXPIREDATE.

如果沒有指定任何選項,便會由 mediaretention 組態設定來決定到期日。If neither option is specified, the expiration date is determined by the mediaretention configuration setting. 如需詳細資訊,請參閱伺服器設定選項For more information, see Server Configuration Options.

重要

這些選項只會防止 SQL ServerSQL Server 覆寫檔案。These options only prevent SQL ServerSQL Server from overwriting a file. 您可以利用其他方法來清除磁帶,並利用作業系統來刪除磁碟檔案。Tapes can be erased using other methods, and disk files can be deleted through the operating system. 如需有關期限驗證的詳細資訊,請參閱這個主題中的 SKIP 和 FORMAT。For more information about expiration verification, see SKIP and FORMAT in this topic.

EXPIREDATE = { ' date ' | @ date_var } 指定備份組到期且可加以覆寫的時間。EXPIREDATE = { 'date' | @date_var } Specifies when the backup set expires and can be overwritten. 如果這個日期是以變數 (@date_var) 提供,則它必須遵照所設定的系統 datetime 格式,且必須指定為下列其中一項:If supplied as a variable (@date_var), this date must follow the configured system datetime format and be specified as one of the following:

  • 字串常數 (@date_var = date)A string constant (@date_var = date)
  • 字元字串資料類型的變數 (ntexttext 資料類型除外)A variable of character string data type (except for the ntext or text data types)
  • smalldatetimeA smalldatetime
  • datetime 變數A datetime variable

例如:For example:

  • 'Dec 31, 2020 11:59 PM'
  • '1/1/2021'

如需如何指定 datetime 值的相關資訊,請參閱日期和時間類型For information about how to specify datetime values, see Date and Time Types.

注意

若要忽略到期日,請使用 SKIP 選項。To ignore the expiration date, use the SKIP option.

RETAINDAYS = { days | @ days_var } 指定必須經過多少天之後,才能覆寫這個備份媒體集。RETAINDAYS = { days | @days_var } Specifies the number of days that must elapse before this backup media set can be overwritten. 如果是以變數 ( @ days_var) 提供,就必須將它指定為整數。If supplied as a variable (@days_var), it must be specified as an integer.

媒體集選項Media Set Options

這些選項會處理整個媒體集。These options operate on the media set as a whole.

{ NOINIT | INIT } 控制備份作業要附加至還是覆寫備份媒體上的現有備份組。{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. 預設是附加至媒體上的最新備份組 (NOINIT)。The default is to append to the most recent backup set on the media (NOINIT).

注意

如需 { NOINIT | INIT } 與 { NOSKIP | SKIP } 間之互動的相關資訊,請參閱本主題稍後的備註For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see Remarks later in this topic.

NOINIT 指出將備份組附加至指定的媒體集,以保留現有的備份組。NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. 如果定義了媒體集的媒體密碼,您就必須提供密碼。If a media password is defined for the media set, the password must be supplied. NOINIT 是預設值。NOINIT is the default.

如需詳細資訊,請參閱媒體集、媒體家族與備份組For more information, see Media Sets, Media Families, and Backup Sets.

INIT 指定應該覆寫所有備份組,但保留媒體標頭。INIT Specifies that all backup sets should be overwritten, but preserves the media header. 如果指定 INIT,就會覆寫這個裝置中任何現有的備份組 (如果條件允許)。If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. 依預設,BACKUP 會檢查下列狀況,如果任何一種狀況存在,就不會覆寫備份媒體:By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

  • 有尚未到期的備份組。Any backup set has not yet expired. 如需詳細資訊,請參閱 EXPIREDATERETAINDAYS 選項。For more information, see the EXPIREDATE and RETAINDAYS options.
  • BACKUP 陳述式所提供的備份組名稱 (如果有提供) 不符合備份媒體中的名稱。The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. 如需詳細資訊,請參閱本節前面的 NAME 選項。For more information, see the NAME option, earlier in this section.

若要覆寫這些檢查,請使用 SKIP 選項。To override these checks, use the SKIP option.

如需詳細資訊,請參閱媒體集、媒體家族與備份組For more information, see Media Sets, Media Families, and Backup Sets.

{ NOSKIP | SKIP } 控制備份作業在覆寫媒體上備份組之前是否要先檢查備份組的到期日和時間。{ NOSKIP | SKIP } Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

注意

如需 { NOINIT | INIT } 與 { NOSKIP | SKIP } 間之互動的相關資訊,請參閱本主題稍後的<備註>。For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

NOSKIP 指示 BACKUP 陳述式先檢查媒體中所有備份組的到期日,才允許覆寫它們。NOSKIP Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. 這是預設行為。This is the default behavior.

SKIP 停用通常是由 BACKUP 陳述式所執行備份組期限和名稱的檢查,以防止覆寫備份組。SKIP Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. 如需有關 { INIT | NOINIT } 和 { NOSKIP | SKIP } 之間互動的詳細資訊,請參閱本主題稍後的<備註>一節。For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic. 若要檢視備份組的到期日,請查詢 backupset 記錄資料表的 expiration_date 資料行。To view the expiration dates of backup sets, query the expiration_date column of the backupset history table.

{ NOFORMAT | FORMAT } 指定是否要將媒體標頭寫入這項備份作業所使用的磁碟區,以覆寫任何現有的媒體標頭和備份組。{ NOFORMAT | FORMAT } Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.

NOFORMAT 指定備份作業保留這項備份作業所使用媒體磁碟區上的現有媒體標頭和備份組。NOFORMAT Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. 這是預設行為。This is the default behavior.

FORMAT 指定建立新的媒體集。FORMAT Specifies that a new media set be created. FORMAT 會導致備份作業在備份作業使用的所有媒體磁碟區中寫入新的媒體標頭。FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. 磁碟區的現有內容會變成無效,因為任何現有的媒體標頭和備份組都會遭到覆寫。The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

重要

請謹慎使用 FORMATUse FORMAT carefully. 格式化媒體集的任何磁碟區,會使得整個媒體集無法使用。Formatting any volume of a media set renders the entire media set unusable. 例如,如果您初始化屬於現有等量媒體集的單一磁帶,整個媒體集都會變成無法使用。For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless.

指定 FORMAT 意味著 SKIP;您不需要明確指示 SKIPSpecifying FORMAT implies SKIP; SKIP does not need to be explicitly stated.

MEDIADESCRIPTION = { text | @ text_variable } 指定媒體集的自由格式文字描述,最多 255 個字元。MEDIADESCRIPTION = { text | @text_variable } Specifies the free-form text description, maximum of 255 characters, of the media set.

MEDIANAME = { media_name | @ media_name_variable } 指定整個備份媒體集的媒體名稱。MEDIANAME = { media_name | @media_name_variable } Specifies the media name for the entire backup media set. 媒體名稱不能超出 128 個字元,如果指定 MEDIANAME,它必須符合先前所指定且已存在備份磁碟區的媒體名稱。The media name must be no longer than 128 characters, If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volumes. 如果未指定或指定了 SKIP 選項,就不會進行媒體名稱的驗證檢查。If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.

BLOCKSIZE = { blocksize | @ blocksize_variable } 指定實體區塊大小 (以位元組為單位)。BLOCKSIZE = { blocksize | @blocksize_variable } Specifies the physical block size, in bytes. 支援的大小為 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 位元組。The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. 磁帶裝置的預設值為 65536,其他裝置則為 512。The default is 65536 for tape devices and 512 otherwise. 一般而言這個選項是不必要的,因為 BACKUP 會自動選取裝置適用的區塊大小。Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. 明確指出區塊大小會覆寫自動選取的區塊大小。Explicitly stating a block size overrides the automatic selection of block size.

如果採用的備份是要複製到 CD-ROM 然後再從中還原,請指定 BLOCKSIZE=2048。If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048.

注意

一般而言,只有在寫入磁帶裝置時,這個選項才會對效能造成影響。This option typically affects performance only when writing to tape devices.

資料轉送選項Data Transfer Options

BUFFERCOUNT = { buffercount | @ buffercount_variable } 指定要用於備份作業的 I/O 緩衝區總數。BUFFERCOUNT = { buffercount | @buffercount_variable } Specifies the total number of I/O buffers to be used for the backup operation. 您可以指定任何正整數,不過,緩衝區的數目很大時,可能會因為 Sqlservr.exe 處理序中的虛擬位址空間不足而造成「記憶體不足」錯誤。You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

緩衝區使用的總空間可由下列公式判斷:BUFFERCOUNT * MAXTRANSFERSIZEThe total space used by the buffers is determined by: BUFFERCOUNT * MAXTRANSFERSIZE.

注意

如需使用 BUFFERCOUNT 選項的重要資訊,請參閱不正確的 BufferCount 資料傳輸選項可能導致 OOM 狀況 (英文) 部落格文章。For important information about using the BUFFERCOUNT option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } 以位元組為單位,指定要用於 SQL ServerSQL Server 與備份媒體之間的最大傳輸單位。MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } Specifies the largest unit of transfer in bytes to be used between SQL ServerSQL Server and the backup media. 可能的值是 65536 位元組 (64 KB) 的倍數,最大可達 4194304 位元組 (4 MB)。The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

注意

使用 SQL 寫入器服務建立備份時,如果已為資料庫設定 FILESTREAM,或該資料庫包含記憶體最佳化檔案群組,則在還原期間的 MAXTRANSFERSIZE 應該大於或等於建立備份時所使用的 MAXTRANSFERSIZEWhen creating backups by using the SQL Writer Service, if the database has configured FILESTREAM, or includes memory optimized filegroups, then the MAXTRANSFERSIZE at the time of a restore should be greater than or equal to the MAXTRANSFERSIZE that was used when the backup was created.

注意

針對含有單一資料檔案且已啟用透明資料加密 (TDE) 的資料庫,預設的 MAXTRANSFERSIZE 為 65536 (64 KB)。For Transparent Data Encryption (TDE) enabled databases with a single data file, the default MAXTRANSFERSIZE is 65536 (64 KB). 針對非 TDE 加密的資料庫,使用備份至 DISK 時,預設的 MAXTRANSFERSIZE 為 1048576 (1 MB),而使用 VDI 或 TAPE 時為 65536 (64 KB)。For non-TDE encrypted databases the default MAXTRANSFERSIZE is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE. 如需搭配 TDE 加密的資料庫使用備份壓縮的詳細資訊,請參閱備註一節。For more information about using backup compression with TDE encrypted databases, see the Remarks section.

錯誤管理選項Error Management Options

這些選項可讓您決定是否要針對備份作業啟用備份總和檢查碼,以及作業是否會在發生錯誤時停止。These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.

{ NO_CHECKSUM | CHECKSUM } 控制是否要啟用備份總和檢查碼。{ NO_CHECKSUM | CHECKSUM } Controls whether backup checksums are enabled.

NO_CHECKSUM 明確地停用產生備份總和檢查碼 (以及驗證頁面總和檢查碼)。NO_CHECKSUM Explicitly disables the generation of backup checksums (and the validation of page checksums). 這是預設行為。This is the default behavior.

CHECKSUM 如果備份作業已啟用且可供使用,則指定備份作業要驗證每個頁面的總和檢查碼及損毀頁,並產生整個備份的總和檢查碼。CHECKSUM Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

使用備份總和檢查碼,可能會影響工作負載和備份的輸送量。Using backup checksums may affect workload and backup throughput.

如需詳細資訊,請參閱在備份和還原期間可能的媒體錯誤For more information, see Possible Media Errors During Backup and Restore.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 控制備份作業在發生頁面總和檢查碼錯誤之後要停止或繼續。{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } Controls whether a backup operation stops or continues after encountering a page checksum error.

STOP_ON_ERROR 指示 BACKUP 在頁面總和檢查碼未驗證時便失敗。STOP_ON_ERROR Instructs BACKUP to fail if a page checksum does not verify. 這是預設行為。This is the default behavior.

CONTINUE_AFTER_ERROR 指示儘管發生總和檢查碼無效或損毀頁之類的錯誤,BACKUP 仍繼續作業。CONTINUE_AFTER_ERROR Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

如果您在資料庫損毀時無法使用 NO_TRUNCATE 選項來備份記錄的結尾,您可以指定 CONTINUE_AFTER_ERROR 取代 NO_TRUNCATE 來嘗試進行結尾記錄備份If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

如需詳細資訊,請參閱在備份和還原期間可能的媒體錯誤For more information, see Possible Media Errors During Backup and Restore.

相容性選項Compatibility Options

RESTART 從 SQL Server 2008SQL Server 2008 開始,沒有任何作用。RESTART Beginning with SQL Server 2008SQL Server 2008, has no effect. 這個版本接受這個選項的目的,是為了與舊版的 SQL ServerSQL Server 相容。This option is accepted by the version for compatibility with previous versions of SQL ServerSQL Server.

監視選項Monitoring Options

STATS [ = percentage ] 每次另一個 percentage 完成時就會顯示一則訊息,用來量測進度。STATS [ = percentage ] Displays a message each time another percentage completes, and is used to gauge progress. 如果省略 percentage,每完成 10%,SQL ServerSQL Server 都會顯示一則訊息。If percentage is omitted, SQL ServerSQL Server displays a message after each 10 percent is completed.

STATS 選項報告到達下一個間隔之報告臨界值的完成百分比。The STATS option reports the percentage complete as of the threshold for reporting the next interval. 大約會以指定的百分比為間隔;例如,當 STATS=10,如果完成的量是 40%,這個選項可能顯示 43%。This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. 對大型備份組而言,這不成問題,因為在已完成的 I/O 呼叫之間,百分比完成的移動非常緩慢。For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

磁帶選項Tape Options

這些選項僅適用於「磁帶」裝置。These options are used only for TAPE devices. 如果所使用的不是磁帶裝置,將忽略這些選項。If a nontape device is being used, these options are ignored.

{ REWIND | NOREWIND } REWIND{ REWIND | NOREWIND } REWIND

指定 SQL ServerSQL Server 會釋放和倒轉磁帶。Specifies that SQL ServerSQL Server releases and rewinds the tape. REWIND 是預設值。REWIND is the default.

NOREWINDNOREWIND

指定 SQL ServerSQL Server 將會在備份作業之後,讓磁帶維持在開啟狀態。Specifies that SQL ServerSQL Server will keep the tape open after the backup operation. 對磁帶執行多次備份作業時,可以使用這個選項來改善效能。You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND 隱含 NOUNLOAD,而這些選項在單一的 BACKUP 陳述式內不相容。NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

注意

如果您使用 NOREWIND,則 SQL ServerSQL Server 的執行個體會保有磁帶機的擁有權,直到在相同處理序中執行的 BACKUP 或 RESTORE 陳述式使用 REWINDUNLOAD 選項,或是伺服器執行個體關閉為止。If you use NOREWIND, the instance of SQL ServerSQL Server retains ownership of the tape drive until a BACKUP or RESTORE statement that is running in the same process uses either the REWIND or UNLOAD option, or the server instance is shut down. 保留磁帶的開啟狀態可以防止其他處理序存取這個磁帶。Keeping the tape open prevents other processes from accessing the tape. 如需如何顯示已開啟磁帶清單及關閉已開啟磁帶的資訊,請參閱備份裝置For information about how to display a list of open tapes and to close an open tape, see Backup Devices.

{ UNLOAD | NOUNLOAD }{ UNLOAD | NOUNLOAD }

注意

UNLOADNOUNLOAD 是工作階段設定,會在工作階段的存留期間保持不變,直到指定其他設定進行重設為止。UNLOAD and NOUNLOAD are session settings that persist for the life of the session or until it is reset by specifying the alternative.

UNLOADUNLOAD

指定在備份完成之後,便自動倒轉和卸載磁帶。Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD 是在工作階段開始時的預設值。UNLOAD is the default when a session begins.

NOUNLOADNOUNLOAD

指定在 BACKUP 作業之後,磁帶仍會在磁帶機上保持載入。Specifies that after the BACKUP operation the tape remains loaded on the tape drive.

注意

如果要備份到磁帶備份裝置,BLOCKSIZE 選項會影響備份作業的效能。For a backup to a tape backup device, the BLOCKSIZE option to affect the performance of the backup operation. 一般而言,只有在寫入磁帶裝置時,這個選項才會對效能造成影響。This option typically affects performance only when writing to tape devices.

記錄特定選項Log-specific options

這些選項只能搭配 BACKUP LOG 使用。These options are only used with BACKUP LOG.

注意

如果您不想要取得記錄備份,請使用簡單復原模式。If you do not want to take log backups, use the simple recovery model. 如需詳細資訊,請參閱復原模式For more information, see Recovery Models.

{ NORECOVERY | STANDBY = undo_file_name }{ NORECOVERY | STANDBY = undo_file_name }

NORECOVERYNORECOVERY

它會備份記錄的結尾,並將資料庫保留在 RESTORING 狀態。Backs up the tail of the log and leaves the database in the RESTORING state. 當進行容錯移轉,將工作交給次要資料庫時,或在 RESTORE 作業之前儲存記錄結尾時,NORECOVERY 非常有用。NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.

若要執行略過記錄截斷的最大速率記錄備份,然後使資料庫自動進入 RESTORING 狀態,請同時使用 NO_TRUNCATENORECOVERY 選項。To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

STANDBY = standby_file_nameSTANDBY = standby_file_name

備份記錄的結尾,並將資料庫保留在唯讀和 STANDBY 狀態。Backs up the tail of the log and leaves the database in a read-only and STANDBY state. STANDBY 子句會寫入待命資料 (執行回復,但使用進一步還原的選項)。The STANDBY clause writes standby data (performing rollback, but with the option of further restores). 使用 STANDBY 選項相當於使用 BACKUP LOG WITH NORECOVERY,後面接著 RESTORE WITH STANDBY。Using the STANDBY option is equivalent to BACKUP LOG WITH NORECOVERY followed by a RESTORE WITH STANDBY.

使用待命模式需要 standby_file_name 所指定的待命資料庫檔案,它的位置儲存在資料庫記錄中。Using standby mode requires a standby file, specified by standby_file_name, whose location is stored in the log of the database. 如果指定的檔案已存在,Database EngineDatabase Engine 會覆寫它;如果檔案不存在,Database EngineDatabase Engine 會建立它。If the specified file already exists, the Database EngineDatabase Engine overwrites it; if the file does not exist, the Database EngineDatabase Engine creates it. 待命檔案會成為資料庫的一部分。The standby file becomes part of the database.

這個檔案會保留已回復的變更,如果之後要套用 RESTORE LOG 作業,就必須保留這些變更。This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied. 您必須有足以供待命檔案成長的磁碟空間,它才能夠包含資料庫中,因回復未認可的交易而修改過的所有相異頁面。There must be enough disk space for the standby file to grow so that it can contain all the distinct pages from the database that were modified by rolling back uncommitted transactions.

NO_TRUNCATENO_TRUNCATE

指定不截斷記錄,並使 Database EngineDatabase Engine 嘗試進行備份,而不論資料庫狀態為何。Specifies that the is log not truncated and causes the Database EngineDatabase Engine to attempt the backup regardless of the state of the database. 因此,利用 NO_TRUNCATE 建立的備份可能會有不完整的中繼資料。Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. 在資料庫已損毀的情況下,您可以利用這個選項來進行記錄的備份。This option allows backing up the log in situations where the database is damaged.

BACKUP LOG 的 NO_TRUNCATE 選項相當於同時指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.

未使用 NO_TRUNCATE 選項時,資料庫必須處於 ONLINE 狀態。Without the NO_TRUNCATE option, the database must be in the ONLINE state. 如果資料庫處於 SUSPENDED 狀態,您就能透過指定 NO_TRUNCATE 來建立備份。If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. 但是,如果資料庫處於 OFFLINE 或 EMERGENCY 狀態,即使設定了 NO_TRUNCATE,也不允許 BACKUP。But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. 如需資料庫狀態的相關資訊,請參閱資料庫狀態For information about database states, see Database States.

關於使用 SQL Server 備份About working with SQL Server backups

本節介紹下列必要的備份概念:This section introduces the following essential backup concepts:

備份類型 交易記錄截斷 將備份媒體格式化 使用備份裝置和媒體集 還原 SQL Server 備份Backup Types Transaction Log Truncation Formatting Backup Media Working with Backup Devices and Media Sets Restoring SQL Server Backups

注意

如需 SQL ServerSQL Server 中備份的簡介,請參閱備份概觀For an introduction to backup in SQL ServerSQL Server, see Backup Overview.

備份類型Backup types

支援的備份類型需視資料庫的復原模式而定,如下所示:The supported backup types depend on the recovery model of the database, as follows

  • 所有復原模式都支援完整和差異的資料備份。All recovery models support full and differential backups of data.

    備份範圍Scope of backup 備份類型Backup types
    整個資料庫Whole database 資料庫備份會包含整個資料庫。Database backups cover the whole database.

    或者,每個資料庫備份都可作為一系列一或多個差異資料庫備份的基底。Optionally, each database backup can serve as the base of a series of one or more differential database backups.
    部分資料庫Partial database 部份備份包含讀取/寫入檔案群組,可能的話,還會包含一或多個唯讀檔案或檔案群組。Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.

    或者,每個部份備份都可作為一系列一或多個差異部份備份的基底。Optionally, each partial backup can serve as the base of a series of one or more differential partial backups.
    檔案或檔案群組File or filegroup 檔案備份包含一或多個檔案或檔案群組,而且只會與包含多個檔案群組的資料庫有關。File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. 在簡單復原模式下,檔案備份基本上會限制用於唯讀的次要檔案群組。Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.
    或者,每個檔案備份都可作為一系列一或多個差異檔案備份的基底。Optionally, each file backup can serve as the base of a series of one or more differential file backups.
  • 在完整復原模式或大量記錄復原模式下,傳統備份也必須包含循序的「交易記錄備份」 (或「記錄備份」 )。Under the full recovery model or bulk-logged recovery model, conventional backups also include sequential transaction log backups (or log backups), which are required. 每個記錄備份都包含建立備份時為使用中的交易記錄部分,而且會包含上一次記錄備份沒有備份的所有記錄檔記錄。Each log backup covers the portion of the transaction log that was active when the backup was created, and it includes all log records not backed up in a previous log backup.

    若要將遺失工作的風險降到最低 (但會耗用管理負擔成本),您應該排定經常性的記錄備份。To minimize work-loss exposure, at the cost of administrative overhead, you should schedule frequent log backups. 在完整備份之間排定差異備份,可減少您在還原資料後必須還原的記錄備份數目,從而減少還原時間。Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

    我們建議您將記錄備份放在個別的磁碟區上,而不是進行資料庫備份。We recommend that you put log backups on a separate volume than the database backups.

    注意

    您必須先建立完整備份,才能建立第一個記錄備份。Before you can create the first log backup, you must create a full backup.

  • 「僅複製備份」 是特殊用途的完整備份或記錄備份,與傳統備份的正常順序無關。A copy-only backup is a special-purpose full backup or log backup that is independent of the normal sequence of conventional backups. 若要建立僅複製備份,請在 BACKUP 陳述式中指定 COPY_ONLY 選項。To create a copy-only backup, specify the COPY_ONLY option in your BACKUP statement. 如需詳細資訊,請參閱只複製備份For more information, see Copy-Only Backups.

交易記錄截斷Transaction Log Truncation

為避免填滿資料庫的交易記錄,例行備份相當重要。To avoid filling up the transaction log of a database, routine backups are essential. 在簡單復原模式下,記錄截斷會自動在備份資料庫後發生,而在完整復原模式下,則會自動在備份交易記錄後發生。Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log. 不過,有時候您可以延遲截斷處理作業。However, sometimes the truncation process can be delayed. 如需延遲記錄截斷可能因素的資訊,請參閱交易記錄For information about factors that can delay log truncation, see The Transaction Log.

注意

BACKUP LOG WITH NO_LOGWITH TRUNCATE_ONLY 選項已中止。The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. 如果您要使用完整復原模式或大量記錄復原模式,而且您必須從資料庫移除記錄備份鏈結,請切換到簡單復原模式。If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. 如需詳細資訊,請參閱檢視或變更資料庫的復原模式For more information, see View or Change the Recovery Model of a Database.

將備份媒體格式化Formatting Backup Media

只有下列其中一種情況成立,BACKUP 陳述式才會將備份媒體格式化:Backup media is formatted by a BACKUP statement if and only if any of the following is true:

  • 已指定 FORMAT 選項。The FORMAT option is specified.
  • 媒體是空的。The media is empty.
  • 作業正在寫入接續磁帶。The operation is writing a continuation tape.

使用備份裝置和媒體集Working with backup devices and media sets

等量媒體集 (等量集) 中的備份裝置Backup devices in a striped media set (a stripe set)

「等量集」 是一組磁碟檔案,其中的資料會分成幾個區塊,並依照固定順序散發。A stripe set is a set of disk files on which data is divided into blocks and distributed in a fixed order. 等量集中所使用的備份裝置數目必須維持相同 (除非使用 FORMAT 來將媒體重新初始化)。The number of backup devices used in a stripe set must stay the same (unless the media is reinitialized with FORMAT).

下列範例會將 AdventureWorks2012AdventureWorks2012 資料庫的備份寫入使用三個磁碟檔案的新等量媒體集。The following example writes a backup of the AdventureWorks2012AdventureWorks2012 database to a new striped media set that uses three disk files.

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
  MEDIANAME = 'AdventureWorksStripedSet0',
  MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database;
GO

在備份裝置定義成等量集的一部分之後,除非指定 FORMAT,否則,單一裝置備份便無法使用它。After a backup device is defined as part of a stripe set, it cannot be used for a single-device backup unless FORMAT is specified. 同樣地,除非指定 FORMAT,否則,等量集也無法使用包含非等量備份的備份裝置。Similarly, a backup device that contains nonstriped backups cannot be used in a stripe set unless FORMAT is specified. 若要分割等量備份組,請使用 FORMAT。To split a striped backup set, use FORMAT.

寫入媒體標頭時,如果既未指定 MEDIANAME,也未指定 MEDIADESCRIPTION,對應至空白項目的媒體標頭欄位就是空的。If neither MEDIANAME or MEDIADESCRIPTION is specified when a media header is written, the media header field corresponding to the blank item is empty.

使用鏡像媒體集Working with a mirrored media set

一般而言,備份並無鏡像,而且 BACKUP 陳述式只會包含 TO 子句。Typically, backups are unmirrored, and BACKUP statements simply include a TO clause. 但是,每個媒體集總共可以包含四個鏡像。However, a total of four mirrors is possible per media set. 如果是鏡像媒體集,備份作業會寫入多個備份裝置群組。For a mirrored media set, the backup operation writes to multiple groups of backup devices. 每個備份裝置群組都會在鏡像媒體集中包含單一鏡像。Each group of backup devices comprises a single mirror within the mirrored media set. 每個鏡像都必須使用相同數量和類型的實體備份裝置,而且必須全部具備相同的屬性。Every mirror must use the same quantity and type of physical backup devices, which must all have the same properties.

若要備份鏡像媒體集,所有鏡像都必須存在。To back up to a mirrored media set, all of the mirrors must be present. 若要備份到鏡像媒體集,請指定 TO 子句來指定第一個鏡像,並為每個其他鏡像指定 MIRROR TO 子句。To back up to a mirrored media set, specify the TO clause to specify the first mirror, and specify a MIRROR TO clause for each additional mirror.

針對鏡像媒體集,每個 MIRROR TO 子句都必須列出與 TO 子句相同的裝置數目和類型。For a mirrored media set, each MIRROR TO clause must list the same number and type of devices as the TO clause. 下列範例會寫入含有兩個鏡像,且每個鏡像都使用三個裝置的鏡像媒體集中:The following example writes to a mirrored media set that contains two mirrors and uses three devices per mirror:

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1a.bak',
  DISK='Y:\SQLServerBackups\AdventureWorks2a.bak',
  DISK='Z:\SQLServerBackups\AdventureWorks3a.bak'
MIRROR TO DISK='X:\SQLServerBackups\AdventureWorks1b.bak',
  DISK='Y:\SQLServerBackups\AdventureWorks2b.bak',
  DISK='Z:\SQLServerBackups\AdventureWorks3b.bak';
GO

重要

這個範例的設計,是為了讓您在本機系統中進行測試。This example is designed to allow you to test it on your local system. 實際上,在相同磁碟機上備份多個裝置可能會降低效能,而且可能會減損鏡像媒體集原先設計的備援性。In practice, backing up to multiple devices on the same drive would hurt performance and would eliminate the redundancy for which mirrored media sets are designed.

鏡像媒體集中的媒體家族Media families in mirrored media sets

在 BACKUP 陳述式之 TO 子句中指定的每個備份裝置都對應到一個媒體家族。Each backup device specified in the TO clause of a BACKUP statement corresponds to a media family. 例如,如果 TO 子句列出三個裝置,BACKUP 就會將資料寫入三個媒體家族。For example, if the TO clauses lists three devices, BACKUP writes data to three media families. 在鏡像媒體集中,每個鏡像都必須包含每個媒體家族的複本。In a mirrored media set, every mirror must contain a copy of every media family. 這就是為什麼每個鏡像必須具有相同的裝置數目。This is why the number of devices must be identical in every mirror.

當各個鏡像分別列出多個裝置時,裝置順序會決定要將哪個媒體家族寫入特定裝置。When multiple devices are listed for each mirror, the order of the devices determines which media family is written to a particular device. 例如,在各份裝置清單中,第二個裝置都會對應到第二個媒體家族。For example, in each of the device lists, the second device corresponds to the second media family. 對於上面範例中的裝置,下表會說明這些裝置和媒體家族間的對應關係。For the devices in the above example, the correspondence between devices and media families is shown in the following table.

鏡像Mirror 媒體家族 1Media family 1 媒體家族 2Media family 2 媒體家族 3Media family 3
00 Z:\AdventureWorks1a.bak Z:\AdventureWorks2a.bak Z:\AdventureWorks3a.bak
11 Z:\AdventureWorks1b.bak Z:\AdventureWorks2b.bak Z:\AdventureWorks3b.bak

媒體家族必須永遠備份到特定鏡像中的相同裝置。A media family must always be backed up onto the same device within a specific mirror. 因此,您每次使用現有媒體集時,都必須依照建立該媒體集時所指定的相同順序來列出每一個鏡像的裝置。Therefore, each time you use an existing media set, list the devices of each mirror in the same order as they were specified when the media set was created.

如需鏡像媒體集的詳細資訊,請參閱鏡像備份媒體集For more information about mirrored media sets, see Mirrored Backup Media Sets. 如需媒體集和媒體家族的一般詳細資訊,請參閱媒體集、媒體家族與備份組For more information about media sets and media families in general, see Media Sets, Media Families, and Backup Sets.

還原 SQL Server 備份Restoring SQL Server backups

若要還原資料庫,並選擇性地復原它以使其上線,或是還原檔案或檔案群組,請使用 Transact-SQLTransact-SQL RESTORE 陳述式或 SQL Server Management StudioSQL Server Management Studio 還原工作。To restore a database and, optionally, recover it to bring it online, or to restore a file or filegroup, use either the Transact-SQLTransact-SQL RESTORE statement or the SQL Server Management StudioSQL Server Management Studio Restore tasks. 如需詳細資訊,請參閱還原和復原概觀For more information see Restore and Recovery Overview.

關於 BACKUP 選項的其他考量Additional considerations about BACKUP options

SKIP、NOSKIP、INIT 和 NOINIT 的互動Interaction of SKIP, NOSKIP, INIT, and NOINIT

下列表格描述 { NOINIT | INIT } 與 { NOSKIP | SKIP } 選項之間的互動。This table describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.

注意

如果磁帶媒體是空的,或磁碟備份檔案不存在,所有這些互動都會寫入媒體標頭,並繼續作業。If the tape media is empty or the disk backup file does not exist, all these interactions write a media header and proceed. 如果媒體不是空的,但缺少有效媒體標頭,這些作業會回應指出這不是有效的 MTF 媒體,而且備份作業將會中止。If the media is not empty and lacks a valid media header, these operations give feedback stating that this is not valid MTF media, and they terminate the backup operation.

NOINITNOINIT INITINIT
NOSKIPNOSKIP 如果磁碟區包含有效的媒體標頭,確認媒體名稱符合指定的 MEDIANAME (如果有的話)。If the volume contains a valid media header, verifies that the media name matches the given MEDIANAME, if any. 如果相符,則附加備份組,保留所有現有的備份組。If it matches, appends the backup set, preserving all existing backup sets.
如果磁碟區並未包含有效的媒體標頭,便會發生錯誤。If the volume does not contain a valid media header, an error occurs.
如果磁碟區包含有效的媒體標頭,則執行下列檢查:If the volume contains a valid media header, performs the following checks:
  • 如果指定了 MEDIANAME,確認指定的媒體名稱符合媒體標頭的媒體名稱。1If MEDIANAME was specified, verifies that the given media name matches the media header's media name.1
  • 確認媒體中沒有出現任何非預期的備份組。Verifies that there are no unexpired backup sets already on the media. 如果有,則結束備份。If there are, terminates the backup.

如果通過這些檢查,則覆寫媒體中的任何備份組,只保留媒體標頭。If these checks pass, overwrites any backup sets on the media, preserving only the media header.
如果磁碟區未包含有效的媒體標頭,使用指定的 MEDIANAMEMEDIADESCRIPTION (如果有的話) 產生一個。If the volume does not contain a valid media header, generates one with using specified MEDIANAME and MEDIADESCRIPTION, if any.
SKIPSKIP 如果磁碟區包含有效的媒體標頭,則附加備份組,保留所有現有的備份組。If the volume contains a valid media header, appends the backup set, preserving all existing backup sets. 如果磁碟區包含有效2的媒體標頭,則覆寫媒體上的任何備份組,只保留媒體標頭。If the volume contains a valid2 media header, overwrites any backup sets on the media, preserving only the media header.
若媒體是空的,就使用指定的 MEDIANAMEMEDIADESCRIPTION (如果有的話) 來產生媒體標頭。If the media is empty, generates a media header using the specified MEDIANAME and MEDIADESCRIPTION, if any.

1 使用者必須屬於適當的固定資料庫或伺服器角色,才能執行備份作業。1 The user must belong to the appropriate fixed database or server roles to perform a backup operation.

2 有效性包括 MTF 版本號碼和其他標頭資訊。2 Validity includes the MTF version number and other header information. 如果不支援指定的版本,或它不是預期的值,就會發生錯誤。If the version specified is unsupported or an unexpected value, an error occurs.

相容性Compatibility

警告

在舊版 SQL ServerSQL Server 中,無法還原較新的 SQL ServerSQL Server版本所建立的備份。Backups that are created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

BACKUP 支援 RESTART 選項,以提供與舊版 SQL ServerSQL Server 之間的回溯相容性。BACKUP supports the RESTART option to provide backward compatibility with earlier versions of SQL ServerSQL Server. 但 RESTART 卻沒有任何作用。But RESTART has no effect.

一般備註General remarks

您可以將資料庫或記錄備份附加至任何磁碟或磁帶裝置,以便將資料庫及其交易記錄保留在單一實體位置中。Database or log backups can be appended to any disk or tape device, allowing a database and its transaction logs to be kept within one physical location.

在明確或隱含的交易中,並不允許使用 BACKUP 陳述式。The BACKUP statement is not allowed in an explicit or implicit transaction.

只要作業系統支援資料庫的定序,便可以執行跨平台的備份作業,即使在不同類型的處理器之間,也是如此。Cross-platform backup operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system.

搭配含有單一資料檔案且已啟用透明資料加密 (TDE) 的資料庫使用備份壓縮時,建議使用大於 65536 (64 KB)MAXTRANSFERSIZE 設定。When using backup compression with Transparent Data Encryption (TDE) enabled databases with a single data file, it is recommended to use a MAXTRANSFERSIZE setting larger than 65536 (64 KB). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,這會針對 TDE 加密的資料庫啟用最佳化壓縮演算法,此演算法會先將頁面解密、將其壓縮,然後再次加密。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again. 如果使用 MAXTRANSFERSIZE = 65536 (64 KB),搭配 TDE 加密資料庫的備份壓縮就會直接壓縮加密的頁面,可能不會產生良好的壓縮率。If using MAXTRANSFERSIZE = 65536 (64 KB), backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios. 如需詳細資訊,請參閱適用於已啟用 TDE 之資料庫的備份壓縮 (英文)。For more information, see Backup Compression for TDE-enabled Databases.

注意

某些情況下,預設值 MAXTRANSFERSIZE 大於 64K:There are some cases where the default MAXTRANSFERSIZE is greater than 64K:

  • 當資料庫已建立多個資料檔案時,它會使用 MAXTRANSFERSIZE > 64KWhen the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K
  • 執行備份至 URL 時,預設 MAXTRANSFERSIZE = 1048576 (1MB)When performing backup to URL, the default MAXTRANSFERSIZE = 1048576 (1MB)

即使其中一個條件適用,您也必須在備份命令中明確設定 MAXTRANSFERSIZE 大於 64K,以取得新的備份壓縮演算法。Even if one of these conditions applies, you must explicitly set MAXTRANSFERSIZE greater than 64K in your backup command in order to get the new backup compression algorithm.

根據預設,每項成功的備份作業都會在 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 back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. 在這類情況下,如果沒有任何指令碼相依於這些記錄項目,您就可以使用追蹤旗標 3226 來隱藏這些記錄項目。In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. 如需詳細資訊,請參閱追蹤旗標For more information, see Trace Flags.

互通性Interoperability

SQL ServerSQL Server 會利用線上備份程序,使您能夠在使用資料庫時,備份資料庫。uses an online backup process to allow a database backup while the database is still in use. 在備份期間,您可以執行大部分的作業;例如,在備份作業期間,您可以執行 INSERT、UPDATE 或 DELETE 陳述式。During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.

資料庫或交易記錄備份期間所無法執行的作業包括:Operations that cannot run during a database or transaction log backup include:

  • 檔案管理作業,例如,搭配 ADD FILEREMOVE FILE 選項的 ALTER DATABASE 陳述式。File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

  • 壓縮資料庫或壓縮檔案的作業。Shrink database or shrink file operations. 其中包括自動壓縮作業。This includes auto-shrink operations.

如果備份作業與檔案管理或壓縮作業重疊,便會發生衝突。If a backup operation overlaps with a file-management or shrink operation, a conflict arises. 不論是哪一項衝突作業在前面,第二項作業都會等待第一項作業所設定的鎖定逾時 (逾時期間由工作階段逾時設定來控制)。Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out (the time-out period is controlled by a session timeout setting). 如果在逾時期間解除鎖定,第二項作業就會繼續下去。If the lock is released during the time-out period, the second operation continues. 如果鎖定逾時,第二項作業就會失敗。If the lock times out, the second operation fails.

中繼資料Metadata

SQL ServerSQL Server 包括下列備份記錄資料表,其會追蹤備份活動:includes the following backup history tables that track backup activity:

執行還原時,如果備份組尚未記錄於 msdb 資料庫,就表示備份記錄資料表可能已修改過。When a restore is performed, if the backup set was not already recorded in the msdb database, the backup history tables might be modified.

SecuritySecurity

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. 仍然可以還原以密碼建立的備份。It is still possible to restore backups created with passwords.

權限Permissions

BACKUP DATABASE 和 BACKUP LOG 權限預設為 sysadmin 固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。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.

範例Examples

本節包含下列範例:This section contains the following examples:

注意

備份的使用說明主題包含了其他的範例。The backup how-to topics contain additional examples. 如需詳細資訊,請參閱備份概觀For more information, see Backup Overview.

A.A. 備份完整資料庫Backing up a complete database

下列範例會將 AdventureWorks2012AdventureWorks2012 資料庫備份到磁碟檔案。The following example backs up the AdventureWorks2012AdventureWorks2012 database to a disk file.

BACKUP DATABASE AdventureWorks2012
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
    WITH FORMAT;
GO

B.B. 備份資料庫和記錄Backing up the database and log

下列範例會備份 AdventureWorks2012AdventureWorks2012 範例資料庫,依預設採用簡單復原模式。The following example backups up the AdventureWorks2012AdventureWorks2012 sample database, which uses the simple recovery model by default. 為了支援記錄備份,AdventureWorks2012AdventureWorks2012 資料庫會修改成使用完整復原模式。To support log backups, the AdventureWorks2012AdventureWorks2012 database is modified to use the full recovery model.

接著,範例會使用 sp_addumpdevice,建立邏輯備份裝置來備份資料 (AdvWorksData),並建立另一個邏輯備份裝置來備份記錄 (AdvWorksLog)。Next, the example uses sp_addumpdevice to create a logical backup device for backing up data, AdvWorksData, and creates another logical backup device for backing up the log, AdvWorksLog.

這個範例接著會建立 AdvWorksData 的完整資料庫備份,並且在更新活動一段時間之後,將記錄備份到 AdvWorksLogThe example then creates a full database backup to AdvWorksData, and after a period of update activity, backs up the log to AdvWorksLog.

-- To permit log backups, before the full database backup, modify the database
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2012
    SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices.
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog',
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012 TO AdvWorksData;
GO
-- Back up the AdventureWorks2012 log.
BACKUP LOG AdventureWorks2012
    TO AdvWorksLog;
GO

注意

如果是實際執行的資料庫,請定期備份記錄。For a production database, back up the log regularly. 記錄的備份頻率必須足以保護資料不會遺失。Log backups should be frequent enough to provide sufficient protection against data loss.

C.C. 建立次要檔案群組的完整檔案備份Creating a full file backup of the secondary filegroups

下列範例會為兩個次要檔案群組中的每個檔案建立完整檔案備份。The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck';
GO

D.D. 建立次要檔案群組的差異檔案備份Creating a differential file backup of the secondary filegroups

下列範例會為兩個次要檔案群組中的每個檔案建立差異檔案備份。The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
    WITH
      DIFFERENTIAL;
GO

E.E. 建立和備份至單一家族的鏡像媒體集中Creating and backing up to a single-family mirrored media set

下列範例會建立一個鏡像媒體集,其中包含單一媒體家族和四個鏡像,且會將 AdventureWorks2012AdventureWorks2012 資料庫備份至其中。The following example creates a mirrored media set containing a single media family and four mirrors and backs up the AdventureWorks2012AdventureWorks2012 database to them.

BACKUP DATABASE AdventureWorks2012
TO TAPE = '\\.\tape0'
MIRROR TO TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2'
MIRROR TO TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet0';

F.F. 建立和備份至多重家族的鏡像媒體集中Creating and backing up to a multifamily mirrored media set

下列範例會建立一個鏡像媒體集,其中的每個鏡像都由兩個媒體家族組成。The following example creates a mirrored media set in which each mirror consists of two media families. 之後,這個範例會將 AdventureWorks2012AdventureWorks2012 資料庫備份在這兩個鏡像中。The example then backs up the AdventureWorks2012AdventureWorks2012 database to both mirrors.

BACKUP DATABASE AdventureWorks2012
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet1';

G.G. 備份至現有的鏡像媒體集中Backing up to an existing mirrored media set

下列範例會將備份組附加至先前範例所建立的媒體集中。The following example appends a backup set to the media set created in the preceding example.

BACKUP LOG AdventureWorks2012
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    NOINIT,
    MEDIANAME = 'AdventureWorksSet1';

注意

NOINIT 是預設值,這裡顯示它是為了更加清楚。NOINIT, which is the default, is shown here for clarity.

H.H. 在新的媒體集中建立壓縮備份Creating a compressed backup in a new media set

下列範例會將媒體格式化、建立新的媒體集,並執行 AdventureWorks2012AdventureWorks2012 資料庫的完整壓縮備份。The following example formats the media, creating a new media set, and perform a compressed full backup of the AdventureWorks2012AdventureWorks2012 database.

BACKUP DATABASE AdventureWorks2012 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'
WITH
    FORMAT,
    COMPRESSION;

I.I. 備份至 Microsoft Azure Blob 儲存體服務Backing up to the Microsoft Azure Blob storage service

下列範例會為 Sales 執行完整資料庫備份以將它備份到 Microsoft Azure Blob 儲存體服務。The example performs a full database backup of Sales to the Microsoft Azure Blob storage service. 儲存體帳戶名稱為 mystorageaccountThe storage Account name is mystorageaccount. 容器名稱為 myfirstcontainerThe container is called myfirstcontainer. 已建立具有讀取、寫入、刪除和列出權限的預存存取原則。A stored access policy has been created with read, write, delete, and list rights. 已使用與預存存取原則相關聯的共用存取簽章來建立 SQL ServerSQL Server 認證 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL ServerSQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. 如需將 SQL ServerSQL Server 備份至 Microsoft Azure Blob 儲存體服務的資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原SQL Server 備份至 URLFor information on SQL ServerSQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_20160726.bak'
WITH STATS = 5;

J.J. 追蹤備份陳述式的進度Track the progress of backup statement

下列查詢會傳回目前正在執行備份陳述式的相關資訊:The following query returns information about the currently running backup statements:

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

另請參閱See Also

SQL ServerSQL Server * SQL Database
受控執行個體 *
 
* SQL Database
managed instance *
 
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL Database 受控執行個體Azure SQL Database managed instance

備份置於/裝載於 Azure SQL Databae 受控執行個體的 SQL 資料庫。Backs up a SQL database placed/hosted in an Azure SQL Databae managed instance. SQL Database 受控執行個體具有自動備份,可讓使用者建立完整的資料庫 COPY_ONLY 備份。SQL Database managed instance has automatic backups, and enables users to create full database COPY_ONLY backups. 不支援差異、記錄和檔案快照備份。Differential, log, and file snapshot backups are not supported.

語法Syntax

BACKUP DATABASE { database_name | @database_name_var }
  TO URL = { 'physical_device_name' | @physical_device_name_var }[ ,...n ]
  WITH COPY_ONLY [, { <general_WITH_options> } ]
[;]

<general_WITH_options> [ ,...n ]::=

--Media Set Options
   MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

引數Arguments

DATABASE 指定完整的資料庫備份。DATABASE Specifies a complete database backup. 在資料庫備份期間,受控執行個體會備份足夠的交易記錄,以便在還原備份時,產生一致的資料庫。During a database backup, the managed instance backs up enough of the transaction log to produce a consistent database when the backup is restored.

重要

在受控執行個體上建立的資料庫備份,只能在其他受控執行個體上還原。A database backup created on a managed instance can only be restored on another managed instance. 它無法還原至 SQL Server 內部部署執行個體 (如同 SQL Server 2016 資料庫的備份無法還原至 SQL Server 2012 執行個體)。It cannot be restored to a SQL Server on-premises instance (similar to the way that a backup of a SQL Server 2016 database cannot be restored to a SQL Server 2012 instance).

當您還原 BACKUP DATABASE 所建立的備份 (「資料備份」 ) 時,就會還原整個備份。When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. 若要從 Azure SQL Database 受控執行個體自動備份進行還原,請參閱將資料庫還原到受控執行個體To restore from Azure SQL Database managed instance automatic backups, see Restore a database to a Managed Instance.

{ database_name | @ database_name_var } 這是要備份完整資料庫的來源資料庫。{ database_name | @database_name_var } Is the database from which the complete database is backed up. 如果這個名稱是以變數 ( @ database_name_var) 的形式提供,您還可以將這個名稱指定為字串常數 ( @ database_name_var = database name),或指定為字元字串資料類型的變數,但 ntexttext 資料類型除外。If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.

如需詳細資訊,請參閱完整檔案備份備份檔案和檔案群組For more information, see Full File Backups and Back Up Files and Filegroups.

TO URLTO URL

指定要用於備份作業的 URL。Specifies the URL to use for the backup operation. URL 格式可用來建立備份至 Microsoft Azure 儲存體服務。The URL format is used for creating backups to the Microsoft Azure storage service.

重要

為了在備份到 URL 時能夠備份到多部裝置,您必須使用共用存取簽章 (SAS) 權杖。In order to backup to multiple devices when backing up to URL, you must use Shared Access Signature (SAS) tokens. 如需建立共用存取簽章的範例,請參閱 SQL Server 備份至 URL在 Azure 儲存體上使用 Powershell 搭配共用存取簽章 (SAS) 權杖來簡化 SQL 認證的建立 (英文)。For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.

n 這是一個預留位置,表示可以在逗號分隔清單中指定最多達 64 個備份裝置。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.

WITH Options 指定要搭配備份作業使用的選項WITH OptionsSpecifies options to be used with a backup operation

CREDENTIAL 只有當建立 Microsoft Azure Blob 儲存體服務備份時才使用。CREDENTIAL Used only when creating a backup to the Microsoft Azure Blob storage service.

ENCRYPTION 用來指定備份的加密。ENCRYPTION Used to specify encryption for a backup. 您可以指定加密演算法來加密備份,或指定 NO_ENCRYPTION 不加密備份。You can specify an encryption algorithm to encrypt the backup with or specify NO_ENCRYPTION to not have the backup encrypted. 加密是有助於保護備份檔案的建議作法。Encryption is recommended practice to help secure backup files. 您可以指定的演算法清單包括:The list of algorithms you can specify are:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果您選擇加密,則也需要使用加密程式選項指定加密程式:If you choose to encrypt you will also have to specify the encryptor using the encryptor options:

  • SERVER CERTIFICATE = <Encryptor_Name>
  • SERVER ASYMMETRIC KEY = <Encryptor_Name>

備份組選項Backup Set Options

COPY_ONLY:指定備份為「僅複製備份」 ,這不會影響正常的備份順序。COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. 僅複製備份的建立與 Azure SQL Database 自動備份無關。A copy-only backup is created independently of the Azure SQL Database automatic backups. 如需詳細資訊,請參閱只複製備份For more information, see Copy-Only Backups.

{ COMPRESSION | NO_COMPRESSION } 指定是否要在此備份上執行備份壓縮,以覆寫伺服器層級的預設值。{ COMPRESSION | NO_COMPRESSION } Specifies whether backup compression is performed on this backup, overriding the server-level default.

預設行為是不壓縮備份。The default behavior is no backup compression. 但是,您可以設定 backup compression default 伺服器設定選項來變更此預設值。But this default can be changed by setting the backup compression default server configuration option. 如需檢視此選項目前值的資訊,請參閱檢視或變更伺服器屬性For information about viewing the current value of this option, see View or Change Server Properties.

如需搭配已啟用透明資料加密 (TDE) 之資料庫使用備份壓縮的相關資訊,請參閱備註一節。For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.

COMPRESSION 明確啟用備份壓縮。COMPRESSION Explicitly enables backup compression.

NO_COMPRESSION 明確停用備份壓縮。NO_COMPRESSION Explicitly disables backup compression.

DESCRIPTION = { ' text ' | @ text_variable } 指定描述備份組的自由格式文字。DESCRIPTION = { 'text' | @text_variable } Specifies the free-form text describing the backup set. 這個字串最多可有 255 個字元。The string can have a maximum of 255 characters.

NAME = { backup_set_name | @ backup_set_var } 指定備份組的名稱。NAME = { backup_set_name | @backup_set_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.

MEDIADESCRIPTION = { text | @ text_variable } 指定媒體集的自由格式文字描述,最多 255 個字元。MEDIADESCRIPTION = { text | @text_variable } Specifies the free-form text description, maximum of 255 characters, of the media set.

MEDIANAME = { media_name | @ media_name_variable } 指定整個備份媒體集的媒體名稱。MEDIANAME = { media_name | @media_name_variable } Specifies the media name for the entire backup media set. 媒體名稱不能超出 128 個字元,如果指定 MEDIANAME,它必須符合先前所指定且已存在備份磁碟區的媒體名稱。The media name must be no longer than 128 characters, If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volumes. 如果未指定或指定了 SKIP 選項,就不會進行媒體名稱的驗證檢查。If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.

BLOCKSIZE = { blocksize | @ blocksize_variable } 指定實體區塊大小 (以位元組為單位)。BLOCKSIZE = { blocksize | @blocksize_variable } Specifies the physical block size, in bytes. 支援的大小為 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 位元組。The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. 磁帶裝置的預設值為 65536,其他裝置則為 512。The default is 65536 for tape devices and 512 otherwise. 一般而言這個選項是不必要的,因為 BACKUP 會自動選取裝置適用的區塊大小。Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. 明確指出區塊大小會覆寫自動選取的區塊大小。Explicitly stating a block size overrides the automatic selection of block size.

資料轉送選項Data Transfer Options

BUFFERCOUNT = { buffercount | @ buffercount_variable } 指定要用於備份作業的 I/O 緩衝區總數。BUFFERCOUNT = { buffercount | @buffercount_variable } Specifies the total number of I/O buffers to be used for the backup operation. 您可以指定任何正整數,不過,緩衝區的數目很大時,可能會因為 Sqlservr.exe 處理序中的虛擬位址空間不足而造成「記憶體不足」錯誤。You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

緩衝區使用的總空間可由下列公式判斷:BUFFERCOUNT * MAXTRANSFERSIZEThe total space used by the buffers is determined by: BUFFERCOUNT * MAXTRANSFERSIZE.

注意

如需使用 BUFFERCOUNT 選項的重要資訊,請參閱不正確的 BufferCount 資料傳輸選項可能導致 OOM 狀況 (英文) 部落格文章。For important information about using the BUFFERCOUNT option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } 以位元組為單位,指定要用於 SQL ServerSQL Server 與備份媒體之間的最大傳輸單位。MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } Specifies the largest unit of transfer in bytes to be used between SQL ServerSQL Server and the backup media. 可能的值是 65536 位元組 (64 KB) 的倍數,最大可達 4194304 位元組 (4 MB)。The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

注意

針對含有單一資料檔案且已啟用透明資料加密 (TDE) 的資料庫,預設的 MAXTRANSFERSIZE 為 65536 (64 KB)。For Transparent Data Encryption (TDE) enabled databases with a single data file, the default MAXTRANSFERSIZE is 65536 (64 KB). 針對非 TDE 加密的資料庫,使用備份至 DISK 時,預設的 MAXTRANSFERSIZE 為 1048576 (1 MB),而使用 VDI 或 TAPE 時為 65536 (64 KB)。For non-TDE encrypted databases the default MAXTRANSFERSIZE is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE. 如需搭配 TDE 加密的資料庫使用備份壓縮的詳細資訊,請參閱備註一節。For more information about using backup compression with TDE encrypted databases, see the Remarks section.

錯誤管理選項Error Management Options

這些選項可讓您決定是否要針對備份作業啟用備份總和檢查碼,以及作業是否會在發生錯誤時停止。These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.

{ NO_CHECKSUM | CHECKSUM } 控制是否要啟用備份總和檢查碼。{ NO_CHECKSUM | CHECKSUM } Controls whether backup checksums are enabled.

NO_CHECKSUM 明確地停用產生備份總和檢查碼 (以及驗證頁面總和檢查碼)。NO_CHECKSUM Explicitly disables the generation of backup checksums (and the validation of page checksums). 這是預設行為。This is the default behavior.

CHECKSUM 如果備份作業已啟用且可供使用,則指定備份作業要驗證每個頁面的總和檢查碼及損毀頁,並產生整個備份的總和檢查碼。CHECKSUM Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

使用備份總和檢查碼,可能會影響工作負載和備份的輸送量。Using backup checksums may affect workload and backup throughput.

如需詳細資訊,請參閱在備份和還原期間可能的媒體錯誤For more information, see Possible Media Errors During Backup and Restore.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 控制備份作業在發生頁面總和檢查碼錯誤之後要停止或繼續。{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } Controls whether a backup operation stops or continues after encountering a page checksum error.

STOP_ON_ERROR 指示 BACKUP 在頁面總和檢查碼未驗證時便失敗。STOP_ON_ERROR Instructs BACKUP to fail if a page checksum does not verify. 這是預設行為。This is the default behavior.

CONTINUE_AFTER_ERROR 指示儘管發生總和檢查碼無效或損毀頁之類的錯誤,BACKUP 仍繼續作業。CONTINUE_AFTER_ERROR Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

如果您在資料庫損毀時無法使用 NO_TRUNCATE 選項來備份記錄的結尾,您可以指定 CONTINUE_AFTER_ERROR 取代 NO_TRUNCATE 來嘗試進行結尾記錄備份If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

如需詳細資訊,請參閱在備份和還原期間可能的媒體錯誤For more information, see Possible Media Errors During Backup and Restore.

相容性選項Compatibility Options

RESTART 沒有任何作用。RESTART Has no effect. 這個版本接受這個選項的目的,是為了與舊版的 SQL Server 相容。This option is accepted by the version for compatibility with previous versions of SQL Server.

監視選項Monitoring Options

STATS [ = percentage ] 每次另一個 percentage 完成時就會顯示一則訊息,用來量測進度。STATS [ = percentage ] Displays a message each time another percentage completes, and is used to gauge progress. 如果省略 percentage,每完成 10%,SQL ServerSQL Server 都會顯示一則訊息。If percentage is omitted, SQL ServerSQL Server displays a message after each 10 percent is completed.

STATS 選項報告到達下一個間隔之報告臨界值的完成百分比。The STATS option reports the percentage complete as of the threshold for reporting the next interval. 大約會以指定的百分比為間隔;例如,當 STATS=10,如果完成的量是 40%,這個選項可能顯示 43%。This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. 對大型備份組而言,這不成問題,因為在已完成的 I/O 呼叫之間,百分比完成的移動非常緩慢。For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

SQL Database 受控執行個體的限制Limitations for SQL Database managed instance

備份等量磁碟區大小上限為 195 GB (最大 Blob 大小)。Max backup stripe size is 195 GB (maximum blob size). 在備份命令中增加等量磁碟區的數目,以減少個別的等量磁碟區大小並維持在這項限制內。Increase the number of stripes in the backup command to reduce individual stripe size and stay within this limit.

SecuritySecurity

權限Permissions

BACKUP DATABASE 權限預設為系統管理員固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。BACKUP DATABASE permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

URL 的擁有權和權限問題可能會干擾備份作業。Ownership and permission problems on the URL 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.

範例Examples

下列範例會為 Sales 執行 COPY_ONLY 備份以將它備份到 Microsoft Azure Blob 儲存體服務。The example performs a COPY_ONLY backup of Sales to the Microsoft Azure Blob storage service. 儲存體帳戶名稱為 mystorageaccountThe storage Account name is mystorageaccount. 容器名稱為 myfirstcontainerThe container is called myfirstcontainer. 已建立具有讀取、寫入、刪除和列出權限的預存存取原則。A stored access policy has been created with read, write, delete, and list rights. 已使用與預存存取原則相關聯的共用存取簽章來建立 SQL ServerSQL Server 認證 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL ServerSQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. 如需將 SQL ServerSQL Server 備份至 Microsoft Azure Blob 儲存體服務的資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原SQL Server 備份至 URLFor information on SQL ServerSQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_20160726.bak'
WITH STATS = 5, COPY_ONLY;

另請參閱See Also

還原資料庫Restore database

SQL ServerSQL Server SQL Database
受控執行個體
SQL Database
managed instance
* Analytics
Platform System (PDW) *
 
* Analytics
Platform System (PDW) *
 

 

分析平台系統Analytics Platform System

建立平行處理資料倉儲Parallel Data Warehouse資料庫的備份,並將備份儲存在應用裝置外、使用者指定的網路位置中。Creates a backup of a 平行處理資料倉儲Parallel Data Warehouse database and stores the backup off the appliance in a user-specified network location. 請搭配 RESTORE DATABASE - Analytics Platform System 使用此陳述式來進行災害復原,或將資料庫從一個設備複製到另一個設備。Use this statement with RESTORE DATABASE - Analytics Platform System for disaster recovery, or to copy a database from one appliance to another.

在您開始前,請先參閱平行處理資料倉儲產品文件Parallel Data Warehouse product documentation中的<Acquire and Configure a Backup Server>(取得並設定備份伺服器)。Before you begin, see "Acquire and Configure a Backup Server" in the 平行處理資料倉儲產品文件Parallel Data Warehouse product documentation.

平行處理資料倉儲Parallel Data Warehouse中有兩種類型的備份。There are two types of backups in 平行處理資料倉儲Parallel Data Warehouse. 「完整資料庫備份」 會備份整個平行處理資料倉儲Parallel Data Warehouse資料庫。A full database backup is a backup of an entire 平行處理資料倉儲Parallel Data Warehouse database. 「差異資料庫備份」 僅包含自上次進行完整備份之後所做的變更。A differential database backup only includes changes made since the last full backup. 使用者資料庫的備份會包含資料庫使用者及資料庫角色。A backup of a user database includes database users, and database roles. master 資料庫的備份會包含登入。A backup of the master database includes logins.

如需有關平行處理資料倉儲Parallel Data Warehouse資料庫備份的詳細資訊,請參閱平行處理資料倉儲產品文件Parallel Data Warehouse product documentation中的<Backup and Restore>(備份和還原)。For more information about 平行處理資料倉儲Parallel Data Warehouse database backups, see "Backup and Restore" in the 平行處理資料倉儲產品文件Parallel Data Warehouse product documentation.

語法Syntax

--Create a full backup of a user database or the master database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    [ WITH [ ( ]<with_options> [ ,...n ][ ) ] ]
[;]

--Create a differential backup of a user database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    WITH [ ( ] DIFFERENTIAL
    [ , <with_options> [ ,...n ] [ ) ]
[;]

<with_options> ::=
    DESCRIPTION = 'text'
    | NAME = 'backup_name'

引數Arguments

database_name 要建立備份的資料庫名稱。database_name The name of the database on which to create a backup. 此資料庫可以是 master 資料庫或使用者資料庫。The database can be the master database or a user database.

TO DISK = '\\UNC_path\backup_directory'平行處理資料倉儲Parallel Data Warehouse 將寫入備份檔案的目的地網路路徑和目錄。TO DISK = '\\UNC_path\backup_directory' The network path and directory to which 平行處理資料倉儲Parallel Data Warehouse will write the backup files. 例如 '\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup'。For example, '\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup'.

  • 備份目錄名稱的路徑必須已經存在,且必須以完整通用命名慣例 (UNC) 路徑的形式指定。The path to the backup directory name must already exist and must be specified as a fully qualified universal naming convention (UNC) path.
  • 備份目錄 backup_directory 必須是在執行備份命令之前不存在的目錄。The backup directory, backup_directory, must not exist before running the backup command. 平行處理資料倉儲Parallel Data Warehouse會建立備份目錄。will create the backup directory.
  • 備份目錄的路徑不可以是本機路徑,也不可以是任何平行處理資料倉儲Parallel Data Warehouse應用裝置節點上的位置。The path to the backup directory cannot be a local path and it cannot be a location on any of the 平行處理資料倉儲Parallel Data Warehouse appliance nodes.
  • UNC 路徑和備份目錄名稱的長度上限是 200 個字元。The maximum length of the UNC path and backup directory name is 200 characters.
  • 伺服器或主機必須以 IP 位址的形式指定。The server or host must be specified as an IP address. 您不能以主機或伺服器名稱的形式指定它。You cannot specify it as the host or server name.

DESCRIPTION = ' text ' 指定備份的文字描述。DESCRIPTION = 'text' Specifies a textual description of the backup. 文字的長度上限為 255 個字元。The maximum length of the text is 255 characters.

此描述會儲存在中繼資料中,並且會在使用 RESTORE HEADERONLY 來還原備份標頭時顯示。The description is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.

NAME = ' backup _name ' 指定備份的名稱。NAME = 'backup _name' Specifies the name of the backup. 備份名稱可以與資料庫名稱不同。The backup name can be different from the database name.

  • 名稱最多可有 128 個字元。Names can have a maximum of 128 characters.
  • 不可包含路徑。Cannot include a path.
  • 開頭必須是字母、數字字元或底線 ()。Must begin with a letter or number character or an underscore (). 允許的特殊字元是底線 (_)、連字號 (-) 或空格 space ( )。Special characters permitted are the underscore (_), hyphen (-), or space ( ). 備份名稱的結尾不可以是空格字元。Backup names cannot end with a space character.
  • 如果 backup_name已經存在於指定的位置中,陳述式將會失敗。The statement will fail if backup_name already exists in the specified location.

此名稱會儲存在中繼資料中,並且會在使用 RESTORE HEADERONLY 來還原備份標頭時顯示。This name is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.

DIFFERENTIAL 指定執行使用者資料庫的差異備份。DIFFERENTIAL Specifies to perform a differential backup of a user database. 如果省略,預設就會是完整資料庫備份。If omitted, the default is a full database backup. 差異備份的名稱不一定要與完整備份的名稱相符。The name of the differential backup does not need to match the name of the full backup. 為了追蹤差異備份及其對應的完整備份,請考慮使用相同名稱再附加 'full' 或 'diff'。For keeping track of the differential and its corresponding full backup, consider using the same name with 'full' or 'diff' appended.

例如:For example:

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerFull';

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerDiff' WITH DIFFERENTIAL;

權限Permissions

需要 BACKUP DATABASE 權限或 db_backupoperator 固定資料庫角色的成員資格。Requires the BACKUP DATABASE permission or membership in the db_backupoperator fixed database role. 新增至 db_backupoperator 固定資料庫角色的一般使用者無法備份 master 資料庫。The master database cannot be backed up but by a regular user that was added to the db_backupoperator fixed database role. 只有 sa、網狀架構系統管理員或 sysadmin 固定伺服器角色的成員才能備份 master 資料庫。The master database can only be backed up by sa, the fabric administrator, or members of the sysadmin fixed server role.

需要具備備份目錄之存取、建立及寫入權限的 Windows 帳戶。Requires a Windows account that has permission to access, create, and write to the backup directory. 您也必須將 Windows 帳戶名稱和密碼儲存在平行處理資料倉儲Parallel Data Warehouse中。You must also store the Windows account name and password in 平行處理資料倉儲Parallel Data Warehouse. 若要將這些網路認證新增至平行處理資料倉儲Parallel Data Warehouse,請使用 sp_pdw_add_network_credentials - SQL 資料倉儲預存程序。To add these network credentials to 平行處理資料倉儲Parallel Data Warehouse, use the sp_pdw_add_network_credentials - SQL Data Warehous stored procedure.

如需有關管理平行處理資料倉儲Parallel Data Warehouse中之認證的詳細資訊,請參閱安全性一節。For more information about managing credentials in 平行處理資料倉儲Parallel Data Warehouse, see the Security section.

錯誤處理Error Handling

在下列情況下會發生 BACKUP DATABASE 錯誤:BACKUP DATABASE errors under the following conditions:

  • 使用者權限不足以執行備份。User permissions are not sufficient to perform a backup.
  • 平行處理資料倉儲Parallel Data Warehouse沒有將儲存備份之網路位置的正確權限。does not have the correct permissions to the network location where the backup will be stored.
  • 資料庫不存在。The database does not exist.
  • 目標目錄已經存在於網路共用上。The target directory already exists on the network share.
  • 目標網路共用無法使用。The target network share is not available.
  • 目標網路共用沒有足夠的空間來進行備份。The target network share does not have enough space for the backup. BACKUP DATABASE 命令在起始備份之前,未先確認是否有足夠的空間存在,導致在執行 BACKUP DATABASE 時,可能產生磁碟空間不足錯誤。The BACKUP DATABASE command does not confirm that sufficient disk space exists prior to initiating the backup, making it possible to generate an out-of-disk-space error while running BACKUP DATABASE. 發生磁碟空間不足問題時,平行處理資料倉儲Parallel Data Warehouse會復原 BACKUP DATABASE 命令。When insufficient disk space occurs, 平行處理資料倉儲Parallel Data Warehouse rolls back the BACKUP DATABASE command. 若要縮減資料庫大小,請執行 DBCC SHRINKLOG (Azure SQL 資料倉儲)To decrease the size of your database, run DBCC SHRINKLOG (Azure SQL Data Warehouse)
  • 嘗試在交易內啟動備份。Attempt to start a backup within a transaction.

一般備註General Remarks

在您執行資料庫之前,請使用 DBCC SHRINKLOG (Azure SQL 資料倉儲) 來縮減資料庫的大小。Before you perform a database backup, use DBCC SHRINKLOG (Azure SQL Data Warehouse) to decrease the size of your database.

平行處理資料倉儲Parallel Data Warehouse備份會以多檔案集合的形式儲存在相同的目錄中。A 平行處理資料倉儲Parallel Data Warehouse backup is stored as a set of multiple files within the same directory.

差異備份所花費的時間通常比完整備份少,因此可以較頻繁地執行。A differential backup usually takes less time than a full backup and can be performed more frequently. 當多個差異備份都根據相同的完整備份時,每個差異備份都會包含前一個差異備份的所有變更。When multiple differential backups are based on the same full backup, each differential includes all of the changes in the previous differential backup.

如果您取消 BACKUP 命令,平行處理資料倉儲Parallel Data Warehouse將會移除目標目錄及為備份建立的所有檔案。If you cancel a BACKUP command, 平行處理資料倉儲Parallel Data Warehouse will remove the target directory and any files created for the backup. 如果平行處理資料倉儲Parallel Data Warehouse與共用的網路連線中斷,便無法完成復原。If 平行處理資料倉儲Parallel Data Warehouse loses network connectivity to the share, the rollback cannot complete.

完整備份和差異備份儲存在個別的目錄中。Full backups and differential backups are stored in separate directories. 系統並未強制執行命名慣例來要求指定完整備份和差異備份彼此互屬。Naming conventions are not enforced for specifying that a full backup and differential backup belong together. 您可以透過自己的命名慣例來進行此追蹤。You can track this through your own naming conventions. 或者,您也可以藉由使用 WITH DESCRIPTION 選項來新增描述,然後使用 RESTORE HEADERONLY 陳述式來擷取描述,以進行此追蹤。Alternatively, you can track this by using the WITH DESCRIPTION option to add a description, and then by using the RESTORE HEADERONLY statement to retrieve the description.

限制事項Limitations and Restrictions

您無法對 master 資料庫執行差異備份。You cannot perform a differential backup of the master database. 僅支援對 master 資料庫執行完整備份。Only full backups of the master database are supported.

備份檔案會以僅適合使用 RESTORE DATABASE - Analytics Platform System 陳述式將備份還原至平行處理資料倉儲Parallel Data Warehouse設備的格式儲存。The backup files are stored in a format suitable only for restoring the backup to a 平行處理資料倉儲Parallel Data Warehouse appliance by using the RESTORE DATABASE - Analytics Platform System statement.

搭配 BACKUP DATABASE 陳述式的備份無法用來將檔案或使用者資訊傳輸給 SMP SQL ServerSQL Server 資料庫。The backup with the BACKUP DATABASE statement cannot be used to transfer data or user information to SMP SQL ServerSQL Server databases. 如需該功能,您可以使用遠端資料表複製功能。For that functionality, you can use the remote table copy feature. 如需詳細資訊,請參閱平行處理資料倉儲產品文件Parallel Data Warehouse product documentation中的<Remote Table Copy>(遠端資料表複製)。For more information, see "Remote Table Copy" in the 平行處理資料倉儲產品文件Parallel Data Warehouse product documentation.

平行處理資料倉儲Parallel Data Warehouse使用 SQL ServerSQL Server 備份技術來備份和還原資料庫。uses SQL ServerSQL Server backup technology to backup and restore databases. SQL ServerSQL Server 備份選項已預先設定為使用備份壓縮。backup options are preconfigured to use backup compression. 您無法設定壓縮、總和檢查碼、區塊大小及緩衝區計數等備份選項。You cannot set backup options such as compression, checksum, block size, and buffer count.

在任何指定時間,都只能在應用裝置上執行一個資料庫備份或還原作業。Only one database backup or restore can run on the appliance at any given time. 平行處理資料倉儲Parallel Data Warehouse會將備份或還原命令排入佇列,直到目前的備份或還原命令完成為止。will queue backup or restore commands until the current backup or restore command has completed.

用來還原備份的目標應用裝置所擁有的計算節點數目必須至少與來源應用裝置相同。The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. 目標所擁有的計算節點數目可以比來源應用裝置多,但不能比來源應用裝置少。The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.

平行處理資料倉儲Parallel Data Warehouse不會追蹤備份的位置和名稱,因為備份會儲存在應用裝置外。does not track the location and names of backups since the backups are stored off the appliance.

平行處理資料倉儲Parallel Data Warehouse不會追蹤資料庫備份是成功還是失敗。does track the success or failure of database backups.

只有在上一個完整備份已成功完成的情況下,才會允許執行差異備份。A differential backup is only allowed if the last full backup completed successfully. 例如,假設您在星期一建立銷售資料庫的完整備份,且該備份成功完成。For example, suppose that on Monday you create a full backup of the Sales database and the backup finishes successfully. 然後,您在星期二建立銷售資料庫的完整備份,但卻失敗。Then on Tuesday you create a full backup of the Sales database and it fails. 在發生此失敗之後,您便無法根據星期一的完整備份來建立差異備份。After this failure, you cannot then create a differential backup based on Monday's full backup. 您必須先建立成功的完整備份,才能建立差異備份。You must first create a successful full backup before creating a differential backup.

中繼資料Metadata

這些動態管理檢視包含所有備份、還原及載入作業的相關資訊。These dynamic management views contain information about all backup, restore, and load operations. 此資訊在系統重新啟動之後會持續存留。The information persists across system restarts.

效能Performance

若要執行備份,平行處理資料倉儲Parallel Data Warehouse會先備份中繼資料,然後對儲存在計算節點上的資料庫資料執行平行備份。To perform a backup, 平行處理資料倉儲Parallel Data Warehouse first backs up the metadata, and then it performs a parallel backup of the database data stored on the Compute nodes. 這會將資料直接從每個計算節點複製到備份目錄。Data is copied directly from each Compute nodes to the backup directory. 為了在將資料從計算節點移至備份目錄時達到最佳效能,平行處理資料倉儲Parallel Data Warehouse會控制同時複製資料的計算節點數目。To achieve the best performance for moving data from the Compute nodes to the backup directory, 平行處理資料倉儲Parallel Data Warehouse controls the number of Compute nodes that are copying data concurrently.

鎖定Locking

在 DATABASE 物件上採用 ExclusiveUpdate 鎖定。Takes an ExclusiveUpdate lock on the DATABASE object.

安全性Security

平行處理資料倉儲Parallel Data Warehouse備份不會儲存在應用裝置上。backups are not stored on the appliance. 因此,您的 IT 小組需負責管理備份安全性的所有層面。Therefore, your IT team is responsible for managing all aspects of the backup security. 例如,這包括管理備份資料的安全性、用來儲存備份之伺服器的安全性、以及將備份伺服器連線至平行處理資料倉儲Parallel Data Warehouse之網路基礎結構的安全性。For example, this includes managing the security of the backup data, the security of the server used to store backups, and the security of the networking infrastructure that connects the backup server to the 平行處理資料倉儲Parallel Data Warehouse appliance.

管理網路認證Manage Network Credentials

對備份目錄的網路存取權是根據標準 Windows 檔案共用安全性。Network access to the backup directory is based on standard Windows file sharing security. 在執行備份之前,您必須建立或指定一個將用來向備份目錄驗證平行處理資料倉儲Parallel Data Warehouse的 Windows 帳戶。Before performing a backup, you need to create or designate a Windows account that will be used for authenticating 平行處理資料倉儲Parallel Data Warehouse to the backup directory. 此 Windows 帳戶必須具備備份目錄之存取、建立及寫入權限。This windows account must have permission to access, create, and write to the backup directory.

重要

為了降低您資料的安全性風險,建議您指定一個專門用來執行備份和還原作業的 Windows 帳戶。To reduce security risks with your data, we advise that you designate one Windows account solely for the purpose of performing backup and restore operations. 請讓此帳戶僅擁有備份位置的權限。Allow this account to have permissions to the backup location and nowhere else.

您必須藉由執行 sp_pdw_add_network_credentials - SQL 資料倉儲預存程序,將使用者名稱和密碼儲存在平行處理資料倉儲Parallel Data Warehouse中。You need to store the user name and password in 平行處理資料倉儲Parallel Data Warehouse by running the sp_pdw_add_network_credentials - SQL Data Warehouse stored procedure. 平行處理資料倉儲Parallel Data Warehouse會使用「Windows 認證管理員」在控制節點及計算節點上,儲存並加密使用者名稱和密碼。uses Windows Credential Manager to store and encrypt user names and passwords on the Control node and Compute nodes. 備份認證時,不會使用 BACKUP DATABASE 命令來備份。The credentials are not backed up with the BACKUP DATABASE command.

若要從平行處理資料倉儲Parallel Data Warehouse移除網路認證,請參閱 sp_pdw_remove_network_credentials - SQL 資料倉儲To remove network credentials from 平行處理資料倉儲Parallel Data Warehouse, see sp_pdw_remove_network_credentials - SQL Data Warehouse.

若要列出儲存在平行處理資料倉儲Parallel Data Warehouse中的所有網路認證,請使用 sys.dm_pdw_network_credentials 動態管理檢視。To list all of the network credentials stored in 平行處理資料倉儲Parallel Data Warehouse, use the sys.dm_pdw_network_credentials dynamic management view.

範例Examples

A.A. 新增備份位置的網路認證Add network credentials for the backup location

若要建立備份,平行處理資料倉儲Parallel Data Warehouse必須具備備份目錄的讀取/寫入權限。To create a backup, 平行處理資料倉儲Parallel Data Warehouse must have read/write permission to the backup directory. 下列範例示範如何新增使用者的認證。The following example shows how to add the credentials for a user. 平行處理資料倉儲Parallel Data Warehouse會儲存這些認證,並使用它們來進行備份和還原作業。will store these credentials and use them to for backup and restore operations.

重要

基於安全性考量,建議您建立一個專門用來執行備份的網域帳戶。For security reasons, we recommend creating one domain account solely for the purpose of performing backups.

EXEC sp_pdw_add_network_credentials 'xxx.xxx.xxx.xxx', 'domain1\backupuser', '*****';

B.B. 移除備份位置的網路認證Remove network credentials for the backup location

下列範例示範如何從平行處理資料倉儲Parallel Data Warehouse中移除網域使用者的認證。The following example shows how to remove the credentials for a domain user from 平行處理資料倉儲Parallel Data Warehouse.

EXEC sp_pdw_remove_network_credentials 'xxx.xxx.xxx.xxx';

C.C. 建立使用者資料庫的完整備份Create a full backup of a user database

下列範例會建立 Invoices 使用者資料庫的完整備份。The following example creates a full backup of the Invoices user database. 平行處理資料倉儲Parallel Data Warehouse會建立 [Invoices2013] 目錄,並將備份檔案儲存至 \\10.192.63.147\backups\yearly\Invoices2013Full 目錄。will create the Invoices2013 directory and will save the backup files to the \\10.192.63.147\backups\yearly\Invoices2013Full directory.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full';

D.D. 建立使用者資料庫的差異備份Create a differential backup of a user database

下列範例會建立差異備份,其中包括自上次完整備份 Invoices 資料庫之後所做的所有變更。The following example creates a differential backup, which includes all changes made since the last full backup of the Invoices database. 平行處理資料倉儲Parallel Data Warehouse會建立 \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff 目錄來儲存檔案。will create the \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff directory to which it will store the files. 'Invoices 2013 differential backup' 描述將會與備份的標頭資訊儲存在一起。The description 'Invoices 2013 differential backup' will be stored with the header information for the backup.

只有在 Invoices 的上一個完整備份已成功完成的情況下,差異備份才會執行成功。The differential backup will only run successfully if the last full backup of Invoices completed successfully.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff'
    WITH DIFFERENTIAL,
    DESCRIPTION = 'Invoices 2013 differential backup';

E.E. 建立 master 資料庫的完整備份Create a full backup of the master database

下列範例會建立 master 資料庫的完整備份,並將其儲存在 '\\10.192.63.147\backups\2013\daily\20130722\master' 目錄中。The following example creates a full backup of the master database and stores it in the directory '\\10.192.63.147\backups\2013\daily\20130722\master'.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master';

F.F. 建立設備登入資訊的備份Create a backup of appliance login information

master 資料庫會儲存應用裝置登入資訊。The master database stores the appliance login information. 若要備份應用裝置登入資訊,您必須備份 master。To backup the appliance login information you need to backup master.

下列範例會建立 master 資料庫的完整備份。The following example creates a full backup of the master database.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master'
WITH (
    DESCRIPTION = 'Master Backup 20130722',
    NAME = 'login-backup'
)
;

另請參閱See Also

RESTORE DATABASE - 平行處理資料倉儲RESTORE DATABASE - Parallel Data Warehous