共用方式為

BACKUP (Transact-SQL)

  • 發行項

備份完整 SQL Server 資料庫，以建立資料庫備份，或備份資料庫的一個或多個檔案或檔案群組，以建立檔案備份 (BACKUP DATABASE)。 同時，可在完整復原模式或大量記錄復原模式下備份資料庫的交易記錄，以建立記錄備份 (BACKUP LOG)。

適用於：SQL Server (SQL Server 2008 至目前版本)。

主題連結圖示 Transact-SQL 語法慣例

語法

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 }
 } 
Note: URL is the format used to specify the location and the file name for the Windows Azure Blob. Although Windows Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seemless backup experince for all the three devices. This option requires WITH CREDENTIAL argument. 

<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 } 
 | { 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

引數

  • DATABASE
    指定完整的資料庫備份。 如果指定了檔案和檔案群組清單，就只會備份這些檔案和檔案群組。 在完整或差異資料庫備份期間，SQL Server 會備份足夠的交易記錄，以便在還原備份時，產生一致的資料庫。

    當您還原 BACKUP DATABASE 所建立的備份 (「資料備份」(Data Backup)) 時，就會還原整個備份。 只有記錄備份可以還原至備份內的特定時間或交易。

    注意

    master 資料庫只能執行完整資料庫備份。

  • LOG
    指定只備份交易記錄。 記錄的備份是從最後執行成功的記錄備份至目前的記錄結尾。 您必須先建立完整備份，才能建立第一個記錄備份。

    您可以在 RESTORE LOG 陳述式中指定 WITH STOPAT、STOPATMARK 或 STOPBEFOREMARK，藉以將記錄備份還原至備份內的特定時間或交易。

    注意

    在建立典型的記錄備份之後，除非您指定 WITH NO_TRUNCATE 或 COPY_ONLY，否則有些交易記錄檔記錄會變成非使用中狀態。當一個或多個虛擬記錄檔案中的所有記錄變成非使用中狀態之後，記錄會發生截斷。如果記錄在例行的記錄備份之後並未截斷，可能會發生延遲記錄截斷。如需詳細資訊，請參閱

  • { database_name| **@database_name_var }
    這是要備份交易記錄、部分資料庫或完整資料庫的來源資料庫。 如果這個名稱是以變數 (    @database_name_var) 的形式提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@**database_name_var = database name)，或指定為字元字串資料類型的變數。

    注意

    資料庫鏡像合作關係中的鏡像資料庫無法備份。

  • <file_or_filegroup> [ ,...n ]
    只能搭配 BACKUP DATABASE 使用，可用來指定要包含在檔案備份中的資料庫檔案或檔案群組，或是指定要包含在部分備份中的唯讀檔案或檔案群組。

    • FILE = { logical_file_name| **@**logical_file_name_var }
      這是指要包含在備份中的檔案邏輯名稱，或是其值等於該檔案邏輯名稱的變數。

    • FILEGROUP = { logical_filegroup_name| **@**logical_filegroup_name_var }
      這是指要包含在備份中的檔案群組邏輯名稱，或是其值等於該檔案群組邏輯名稱的變數。 在簡單復原模式之下，只允許唯讀檔案群組使用檔案群組備份。

      注意

      當資料庫備份因資料庫大小和效能需求而不可行時，請考慮使用檔案備份。

    • n
      這是一個預留位置，表示可以在逗號分隔清單中指定多個檔案和檔案群組。 數目沒有限制。

    如需詳細資訊，請參閱＜完整檔案備份 (SQL Server)＞和＜備份檔案和檔案群組 (SQL Server)＞。

  • READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name| **@**logical_filegroup_name_var } [ ,...n ]]
    指定部分備份。 部分備份包含資料庫中所有的讀取/寫入檔案：主要檔案群組和任何一種讀取/寫入次要檔案群組，以及任何指定的唯讀檔案或檔案群組。

    • READ_WRITE_FILEGROUPS
      指定要在部分備份進行備份的所有讀取/寫入檔案群組。 如果資料庫是唯讀的，READ_WRITE_FILEGROUPS 只會包括主要檔案群組。

      重要事項重要事項

      使用 FILEGROUP 取代 READ_WRITE_FILEGROUPS 來明確列出讀取/寫入檔案群組，以建立檔案備份。

    • FILEGROUP = { logical_filegroup_name| **@**logical_filegroup_name_var }
      這是指要包含在部分備份中的唯讀檔案群組邏輯名稱，或是其值等於該唯讀檔案群組邏輯名稱的變數。 如需詳細資訊，請參閱本主題前面的「<file_or_filegroup>」。

    • n
      這是一個預留位置，表示可以在逗號分隔清單中指定多個唯讀檔案群組。

    如需有關部分備份的詳細資訊，請參閱＜部分備份 (SQL Server)＞。

  • TO <backup_device> [ ,...n]
    指出隨附的備份裝置集合是未鏡像的媒體集，或是鏡像媒體集內的第一個鏡像 (如果宣告了一個或多個 MIRROR TO 子句)。

    • <backup_device>
      指定備份作業要使用的邏輯或實體備份裝置。

      • { logical_device_name | **@**logical_device_name_var }
        這是用來備份資料庫之備份裝置的邏輯名稱。 邏輯名稱必須遵照識別碼的規則。 如果備份裝置名稱是以變數 (@logical_device_name_var) 的方式來提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@logical_device_name_var = 邏輯備份裝置名稱)，或指定為字元字串資料類型的變數。

      • { DISK | TAPE | URL} = { 'physical_device_name' | **@**physical_device_name_var }
        指定磁碟檔案或磁帶裝置，或是 Windows Azure Blob 儲存體服務。 URL 格式是用於建立 Windows Azure Blob 儲存體服務的備份。 如需詳細資訊，請參閱＜SQL Server 備份及還原與 Windows Azure Blob 儲存體服務＞。

        URL：適用於：SQL Server (SQL Server 2012 SP1 CU2 至目前版本)。

        在 BACKUP 陳述式內指定磁碟裝置之前，該裝置不需要存在。 如果實體裝置存在，且 BACKUP 陳述式並未指定 INIT 選項，就會將備份附加至裝置中。

        如需詳細資訊，請參閱＜備份裝置 (SQL Server)＞。

        注意

        未來的 SQL Server 版本將移除 TAPE 選項。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

    • n
      這是一個預留位置，表示可以在逗號分隔清單中指定最多達 64 個備份裝置。

  • MIRROR TO <backup_device> [ ,...n]
    指定一組最多三個的次要備份裝置，其中每個裝置都會鏡像處理 TO 子句中所指定的備份裝置。 MIRROR TO 子句和 TO 子句必須指定相同類型和數量的備份裝置。 最大 MIRROR TO 子句數目是 3。

    只有 SQL Server 的 Enterprise 版本才提供這個選項。

    注意

    如果 MIRROR TO = DISK，BACKUP 會自動判斷磁碟裝置的適當區塊大小。如需有關區塊大小的詳細資訊，請參閱這份資料表稍後的 "BLOCKSIZE"。

    • <backup_device>
      請參閱本節前面的「<backup_device>」。

    • n
      這是一個預留位置，表示可以在逗號分隔清單中指定最多達 64 個備份裝置。 MIRROR TO 子句中的裝置數目必須等於 TO 子句中的裝置數目。

    如需詳細資訊，請參閱本主題稍後＜備註＞一節中的「鏡像媒體集中的媒體家族」。

  • [ next-mirror-to ]
    這是一個預留位置，表示單一 BACKUP 陳述式除了可以包含單一 TO 子句，還可以包含最多 3 個 MIRROR TO 子句。

WITH 選項

指定要搭配備份作業使用的選項。

  • CREDENTIAL
    只有當建立備份到 Windows Azure Blob 儲存服務時才使用。

    適用於：SQL Server (SQL Server 2012 SP1 CU2 到目前版本)。

  • DIFFERENTIAL
    只能搭配 BACKUP DATABASE 使用，可用來指定資料庫或檔案備份應該只含有資料庫或檔案在前次完整備份之後又變更過的部分。 差異備份所用的空間通常會比完整備份少。 使用這個選項，便不需要套用自前次完整備份之後所執行的所有個別記錄備份。

    注意

    根據預設，BACKUP DATABASE 會建立完整備份。

    如需詳細資訊，請參閱＜差異備份 (SQL Server)＞。

  • ENCRYPTION
    用來指定備份的加密。 您可以指定加密演算法來加密備份，與指定 "NO_ENCRYPION" 不加密備份。 加密是有助於保護備份檔案的建議作法。 您可以指定的演算法清單包括：

    • AES_128

    • AES_192

    • AES_256

    • TRIPLE_DES_3KEY

    • NO_ENCRYPTION

    如果您選擇加密，則也需要使用加密程式選項指定加密程式：

    • SERVER CERTIFICATE = Encryptor_Name

    • SERVER ASYMMETRIC KEY = Encryptor_Name

備份組選項

這些選項會處理這個備份作業所建立的備份組。

注意

如果要指定還原作業的備份組，請使用 FILE = <backup_set_file_number> 選項。如需有關如何指定備份組的詳細資訊，請參閱＜RESTORE 引數 (Transact-SQL)＞中的＜指定備份組＞。

  • COPY_ONLY
    指定備份為「僅複製備份」(Copy-Only Backup)，這不會影響正常的備份順序。 僅複製備份的建立與定期排程的傳統備份無關。 僅複製備份並不會影響資料庫的整體備份和還原程序。

    僅複製備份應該用於需要執行備份來達成特定用途的情況 (例如，在線上檔案還原之前備份記錄檔)。 通常，僅複製記錄備份用過一次後便會刪除。

    • 當搭配 BACKUP DATABASE 使用時，COPY_ONLY 選項會建立無法做為差異基底的完整備份。 差異點陣圖不會更新，而且差異備份的行為會如同僅複製備份並不存在。 後續的差異備份會使用最新的傳統完整備份做為其基底。

      重要事項重要事項

      如果 DIFFERENTIAL 和 COPY_ONLY 一起使用，則會忽略 COPY_ONLY，而建立差異備份。

    • 當搭配 BACKUP LOG 使用時，COPY_ONLY 選項會建立「僅複製記錄備份」(Copy-Only Log Backup)，這不會截斷交易記錄。 僅複製記錄備份不會影響記錄檔鏈結，而且其他記錄備份的行為會如同僅複製備份並不存在。

    如需詳細資訊，請參閱＜只複製備份 (SQL Server)＞。

  • { COMPRESSION | NO_COMPRESSION }
    只有在 SQL Server 2008 Enterprise 及更新版本中，才會指定是否要在此備份上執行備份壓縮，以覆寫伺服器層級的預設值。

    進行安裝時，預設行為是不壓縮備份。 但是，您可以設定 backup compression default 伺服器組態選項來變更此預設值。 如需有關檢視此選項之目前值的詳細資訊，請參閱＜檢視或變更伺服器屬性 (SQL Server)＞。

    • COMPRESSION
      明確啟用備份壓縮。

    • NO_COMPRESSION
      明確停用備份壓縮。

  • DESCRIPTION = { 'text' | **@**text_variable }
    指定描述備份組的自由形式文字。 這個字串最多可有 255 個字元。

  • NAME = { backup_set_name| **@**backup_set_var }
    指定備份組的名稱。 名稱最多可有 128 個字元。 如果未指定 NAME，它就是空白。

  • { EXPIREDATE = 'date'| RETAINDAYS = days }
    指定何時可以覆寫這個備份的備份組。 如果同時使用這兩個選項，RETAINDAYS 會優先於 EXPIREDATE。

    如果沒有指定任何選項，便會由 media retention 組態設定來決定到期日。 如需詳細資訊，請參閱＜伺服器組態選項 (SQL Server)＞。

    重要事項重要事項

    這些選項只會防止 SQL Server 覆寫檔案。您可以利用其他方法來清除磁帶，並利用作業系統來刪除磁碟檔案。如需有關期限驗證的詳細資訊，請參閱這個主題中的 SKIP 和 FORMAT。

    • EXPIREDATE = { 'date'| **@**date_var }
      指定備份組到期且可加以覆寫的時間。 如果這個日期是以變數 (@date_var) 的方式來提供，這個日期必須遵照設定的系統 datetime 格式，且必須指定成下列其中一項：

      • 字串常數 (@date_var = 日期)

      • 字元字串資料類型的變數 (ntext 或 text 資料類型除外)

      • smalldatetime。

      • datetime 變數

      例如：

      • 'Dec 31, 2020 11:59 PM'

      • '1/1/2021'

      如需有關如何指定 datetime 值的詳細資訊，請參閱＜日期和時間類型＞。

      注意

      若要忽略到期日，請使用 SKIP 選項。

    • RETAINDAYS = { days| **@days_var }
      指定必須經過多少天之後，才能覆寫這個備份媒體集。 如果是以變數 (      @**days_var) 的方式來提供，您必須將它指定成一個整數。

媒體集選項

這些選項會處理整個媒體集。

  • { NOINIT | INIT }
    控制備份作業要附加還是覆寫至備份媒體上的現有備份組。 預設是附加至媒體上的最新備份組 (NOINIT)。

    注意

    如需有關 { NOINIT | INIT } 和 { NOSKIP | SKIP } 之間互動的詳細資訊，請參閱本主題稍後的＜備註＞一節。

    • NOINIT
      指出將備份組附加至指定的媒體集，以保留現有的備份組。 如果定義了媒體集的媒體密碼，您就必須提供密碼。 NOINIT 是預設值。

      如需詳細資訊，請參閱＜媒體集、媒體家族與備份組 (SQL Server)＞。

    • INIT
      指定應該覆寫所有備份組，但保留媒體標頭。 如果指定 INIT，就會覆寫這個裝置中任何現有的備份組 (如果條件允許)。 依預設，BACKUP 會檢查下列狀況，如果任何一種狀況存在，就不會覆寫備份媒體：

      • 有尚未到期的備份組。 如需詳細資訊，請參閱 EXPIREDATE 和 RETAINDAYS 選項。

      • BACKUP 陳述式所提供的備份組名稱 (如果有提供) 不符合備份媒體中的名稱。 如需詳細資訊，請參閱本節前面的 NAME 選項。

      若要覆寫這些檢查，請使用 SKIP 選項。

      如需詳細資訊，請參閱＜媒體集、媒體家族與備份組 (SQL Server)＞。

  • { NOSKIP | SKIP }
    控制備份作業在覆寫媒體上的備份組之前是否要先檢查備份組的到期日和時間。

    注意

    如需有關 { NOINIT | INIT } 和 { NOSKIP | SKIP } 之間互動的詳細資訊，請參閱本主題稍後的＜備註＞一節。

    • NOSKIP
      指示 BACKUP 陳述式先檢查媒體中所有備份組的到期日，才允許覆寫它們。 這是預設行為。

    • SKIP
      停用通常是由 BACKUP 陳述式所執行的備份組期限和名稱的檢查，以防止覆寫備份組。 如需有關 { INIT | NOINIT } 和 { NOSKIP | SKIP } 之間互動的詳細資訊，請參閱本主題稍後的＜備註＞一節。

      若要檢視備份組的到期日，請查詢 backupset 記錄資料表的 expiration_date 資料行。

  • { NOFORMAT | FORMAT }
    指定是否要將媒體標頭寫入這項備份作業所使用的磁碟區，以覆寫任何現有的媒體標頭和備份組。

    • NOFORMAT
      指定備份作業保留這項備份作業所使用之媒體磁碟區上的現有媒體標頭和備份組。 這是預設行為。

    • FORMAT
      指定建立新的媒體集。 FORMAT 會導致備份作業在備份作業使用的所有媒體磁碟區中寫入新的媒體標頭。 磁碟區的現有內容會變成無效，因為任何現有的媒體標頭和備份組都會遭到覆寫。

      重要事項重要事項

      請小心使用 FORMAT。格式化媒體集的任何磁碟區，會使得整個媒體集無法使用。例如，如果您初始化屬於現有等量媒體集的單一磁帶，整個媒體集都會變成無法使用。

      指定 FORMAT 隱含 SKIP；您不需要明確指示 SKIP。

  • MEDIADESCRIPTION = { text | **@**text_variable }
    指定媒體集自由形式的文字描述，最多 255 個字元。

  • MEDIANAME = { media_name | **@**media_name_variable }
    指定整個備份媒體集的媒體名稱。 媒體名稱不能超出 128 個字元，如果指定 MEDIANAME，它必須符合已在備份磁碟區中，先前所指定的媒體名稱。 如果未指定或指定了 SKIP 選項，就不會進行媒體名稱的驗證檢查。

  • BLOCKSIZE = { blocksize | **@**blocksize_variable }
    指定實體區塊大小 (以位元組為單位)。 支援的大小為 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 位元組。 磁帶裝置的預設值為 65536，其他裝置則為 512。 一般而言這個選項是不必要的，因為 BACKUP 會自動選取裝置適用的區塊大小。 明確指出區塊大小會覆寫自動選取的區塊大小。

    如果採用的備份是要複製到 CD-ROM 然後再從中還原，請指定 BLOCKSIZE=2048。

    注意

    一般而言，只有在寫入磁帶裝置時，這個選項才會對效能造成影響。

資料傳送選項

  • BUFFERCOUNT = { buffercount | **@**buffercount_variable }
    指定要用於備份作業的 I/O 緩衝區總數。 您可以指定任何正整數，不過，緩衝區的數目很大時，可能會因為 Sqlservr.exe 處理序中的虛擬位址空間不足而造成「記憶體不足」錯誤。

    緩衝區使用的總空間可由下列公式判斷：buffercount * maxtransfersize。

    注意

    如需有關使用 BUFFERCOUNT 選項的重要資訊，請參閱＜不正確的 BufferCount 資料傳輸選項可能導致 OOM 狀況＞部落格文章。

  • MAXTRANSFERSIZE = { maxtransfersize | **@**maxtransfersize_variable }
    以位元組為單位，指定要用於 SQL Server 和備份媒體之間的最大傳送單位。 可能的值是 65536 位元組 (64 KB) 的倍數，最大可達 4194304 位元組 (4 MB)。

錯誤管理選項

這些選項可讓您決定備份作業是否要啟用備份總和檢查碼，以及作業在發生錯誤時是否要停止。

  • { NO_CHECKSUM | CHECKSUM }
    控制是否要啟用備份總和檢查碼。

    • NO_CHECKSUM
      明確地停用產生備份總和檢查碼 (以及驗證頁面總和檢查碼)。 這是預設行為。

    • CHECKSUM
      如果啟用備份作業並可使用，指定備份作業將會驗證每個分頁的總和檢查碼及損毀頁，並產生整個備份的總和檢查碼。

      使用備份總和檢查碼，可能會影響工作負載和備份的輸送量。

    如需詳細資訊，請參閱＜在備份和還原期間可能的媒體錯誤 (SQL Server)＞。

  • { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
    控制備份作業在發生頁面總和檢查碼錯誤之後要停止或繼續。

    • STOP_ON_ERROR
      指示 BACKUP 在頁面總和檢查碼未驗證時便失敗。 這是預設行為。

    • CONTINUE_AFTER_ERROR
      指示儘管發生總和檢查碼無效或損毀頁之類的錯誤，BACKUP 仍繼續作業。

      如果您在資料庫損毀時無法使用 NO_TRUNCATE 選項備份記錄檔的結尾，您可以指定 CONTINUE_AFTER_ERROR 取代 NO_TRUNCATE 來嘗試進行結尾記錄備份。

    如需詳細資訊，請參閱＜在備份和還原期間可能的媒體錯誤 (SQL Server)＞。

相容性選項

  • RESTART
    從 SQL Server 2008 開始，沒有任何作用。 這個版本接受這個選項的目的，是為了與舊版的 SQL Server 相容。

監視選項

  • STATS [ **=**percentage ]
    每當另一個 percentage 完成時，即顯示一則訊息用於測量進度。 如果省略 percentage，每完成 10%，SQL Server 都會顯示一則訊息。

    STATS 選項報告到達下一個間隔之報告臨界值的完成百分比。 大約會以指定的百分比為間隔；例如，當 STATS=10，如果完成的量是 40%，這個選項可能顯示 43%。 對大型備份組而言，這不成問題，因為在已完成的 I/O 呼叫之間，百分比完成的移動非常緩慢。

磁帶選項

這些選項僅適用於「磁帶」裝置。 如果所使用的不是磁帶裝置，將忽略這些選項。

  • { REWIND | NOREWIND }

    • REWIND
      指定 SQL Server 將釋放和倒轉磁帶。 REWIND 是預設值。

    • NOREWIND
      指定 SQL Server 將會在備份作業之後，讓磁帶維持在開啟狀態。 對磁帶執行多次備份作業時，可以使用這個選項來改善效能。

      NOREWIND 隱含 NOUNLOAD，而這些選項在單一的 BACKUP 陳述式內不相容。

      注意

      如果您使用 NOREWIND，則 SQL Server 的執行個體會保有磁帶機的擁有權，直到在相同處理序中執行的 BACKUP 或 RESTORE 命令使用 REWIND 或 UNLOAD 選項，或是伺服器執行個體關閉為止。保留磁帶的開啟狀態可以防止其他處理序存取這個磁帶。如需有關如何顯示開啟的磁帶清單及關閉開啟的磁帶之詳細資訊，請參閱＜備份裝置 (SQL Server)＞。

  • { UNLOAD | NOUNLOAD }

    注意

    UNLOAD/NOUNLOAD 是工作階段設定，在工作階段的存留期間會一直保持不變，直到指定其他設定來進行重設為止。

    • UNLOAD
      指定在備份完成之後，便自動倒轉和卸載磁帶。 UNLOAD 是在工作階段開始時的預設值。

    • NOUNLOAD
      指定在 BACKUP 作業之後，磁帶仍會在磁帶機上保持載入。

注意

如果是使用磁帶備份裝置的備份作業，BLOCKSIZE 選項會影響備份作業的效能。一般而言，只有在寫入磁帶裝置時，這個選項才會對效能造成影響。

記錄特定選項

這些選項僅能搭配 BACKUP LOG 使用。

注意

如果您不想要取得記錄備份，請使用簡單復原模式。如需詳細資訊，請參閱＜復原模式 (SQL Server)＞。

  • { NORECOVERY | STANDBY **=**undo_file_name }

    • NORECOVERY
      它會備份記錄的結尾，並將資料庫保留在 RESTORING 狀態。 當進行容錯移轉，將工作交給次要資料庫時，或在 RESTORE 作業之前儲存記錄結尾時，NORECOVERY 非常有用。

      若要執行略過記錄截斷的最大速率記錄備份，再使資料庫自動進入 RESTORING 狀態，請同時使用 NO_TRUNCATE 和 NORECOVERY 選項。

    • STANDBY **=**standby_file_name
      備份記錄的結尾，並將資料庫保留在唯讀和 STANDBY 狀態。 STANDBY 子句會寫入待命資料 (執行回復，但使用進一步還原的選項)。 使用 STANDBY 選項相當於使用 BACKUP LOG WITH NORECOVERY，後面接著 RESTORE WITH STANDBY。

      使用待命模式需要 standby_file_name 所指定的待命資料庫檔案，它的位置儲存在資料庫記錄中。 如果指定的檔案已存在，Database Engine 會覆寫它；如果檔案不存在，Database Engine 會建立它。 待命檔案會成為資料庫的一部分。

      這個檔案會保留已回復的變更，如果之後要套用 RESTORE LOG 作業，就必須保留這些變更。 您必須有足以供待命檔案成長的磁碟空間，它才能夠包含資料庫中，因回復未認可的交易而修改過的所有相異頁面。

  • NO_TRUNCATE
    它指定不截斷記錄，且使 Database Engine 不論資料庫狀態為何，都一律嘗試進行備份。 因此，利用 NO_TRUNCATE 取得的備份可能會有不完整的中繼資料。 在資料庫已損毀的情況下，您可以利用這個選項來進行記錄的備份。

    BACKUP LOG 的 NO_TRUNCATE 選項相當於同時指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。

    當沒有 NO_TRUNCATE 選項時，資料庫必須處於 ONLINE 狀態。 如果資料庫處於 SUSPENDED 狀態，您可以指定 NO_TRUNCATE 來建立備份。 但是如果資料庫處於 OFFLINE 或 EMERGENCY 狀態，即使設定了 NO_TRUNCATE，也不允許 BACKUP。 如需有關資料庫狀態的詳細資訊，請參閱＜資料庫狀態＞。

關於使用 SQL Server 備份

本節介紹下列必要的備份概念：

備份類型

交易記錄截斷

格式化備份媒體

使用備份裝置和媒體集

還原 SQL Server 備份

注意

如需 SQL Server 中備份的簡介，請參閱＜備份概觀 (SQL Server)＞。

備份類型

支援的備份類型需視資料庫的復原模式而定，如下所示：

  • 所有復原模式都支援完整和差異的資料備份。

    備份範圍

    備份類型

    整個資料庫

    資料庫備份會包含整個資料庫。

    或者，每個資料庫備份都可做為一系列一個或多個差異資料庫備份的基底。

    部分資料庫

    部分備份包含讀取/寫入檔案群組，可能的話，還會包含一個或多個唯讀檔案或檔案群組。

    或者，每個部分備份都可做為一系列一個或多個差異部分備份的基底。

    檔案或檔案群組

    檔案備份會包含一個或多個檔案或檔案群組，而且只會用於包含多個檔案群組的資料庫。 在簡單復原模式下，檔案備份基本上會限制用於唯讀的次要檔案群組。

    或者，每個檔案備份都可做為一系列一個或多個差異檔案備份的基底。

  • 在完整復原模式或大量記錄復原模式下，傳統備份也必須包含循序的「交易記錄備份」(Transaction Log Backup) (或「記錄備份」(Log Backup))。 每個記錄備份都包含建立備份時為使用中的交易記錄部分，而且會包含上一次記錄備份沒有備份的所有記錄檔記錄。

    若要將遺失工作的風險降到最低 (但會耗用管理負擔成本)，您應該排定經常性的記錄備份。 在完整備份之間排定差異備份，可減少您在還原資料後必須還原的記錄備份數目，從而減少還原時間。

    我們建議您將記錄備份放在個別的磁碟區上，而不是進行資料庫備份。

    注意

    您必須先建立完整備份，才能建立第一個記錄備份。

  • 「僅複製備份」(Copy-Only Backup) 是特殊用途的完整備份或記錄備份，與傳統備份的正常順序無關。 若要建立僅複製備份，請在 BACKUP 陳述式中指定 COPY_ONLY 選項。 如需詳細資訊，請參閱＜只複製備份 (SQL Server)＞。

交易記錄截斷

為避免填滿資料庫的交易記錄，例行備份相當重要。 在簡單復原模式下，記錄截斷會自動在備份資料庫後發生，而在完整復原模式下，則會自動在備份交易記錄後發生。 不過，有時候您可以延遲截斷處理作業。 如需有關可能延遲記錄截斷之因素的詳細資訊，請參閱＜交易記錄 (SQL Server)＞。

注意

BACKUP LOG WITH NO_LOG 和 WITH TRUNCATE_ONLY 選項已遭到停用。如果您要使用完整復原模式或大量記錄復原模式，而且您必須從資料庫移除記錄備份鏈結，請切換到簡單復原模式。如需詳細資訊，請參閱＜檢視或變更資料庫的復原模式 (SQL Server)＞。

格式化備份媒體

只有下列其中一種情況成立，BACKUP 陳述式才會將備份媒體格式化：

  • 指定了 FORMAT 選項。

  • 媒體是空的。

  • 作業正在寫入接續磁帶。

使用備份裝置和媒體集

等量媒體集 (等量集) 中的備份裝置

「等量集」(Stripe Set) 是指磁碟檔案的集合，在此集合中，資料會分成幾個區塊，並依照固定順序散發。 等量集中所使用的備份裝置數目必須維持相同 (除非您使用 FORMAT 重新初始化媒體)。

下列範例會將 AdventureWorks2012 資料庫的備份寫入使用三個磁碟檔案的新等量媒體集。

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，否則，單一裝置備份便無法使用它。 同樣地，除非指定 FORMAT，否則，等量集也無法使用包含非等量備份的備份裝置。 若要分割等量備份組，請使用 FORMAT。

當寫入媒體標頭時，如果既未指定 MEDIANAME，也未指定 MEDIADESCRIPTION，對應於空白項目的媒體標頭欄位就是空的。

使用鏡像媒體集

一般而言，備份並無鏡像，而且 BACKUP 陳述式只會包含 TO 子句。 但是，每個媒體集總共可以包含四個鏡像。 如果是鏡像媒體集，備份作業會寫入多個備份裝置群組。 每個備份裝置群組都會在鏡像媒體集中包含單一鏡像。 每個鏡像都必須使用相同數量和類型的實體備份裝置，而且必須全部具備相同的屬性。

若要備份鏡像媒體集，所有鏡像都必須存在。 若要備份到鏡像媒體集，請指定 TO 子句來指定第一個鏡像，並且為每個其他鏡像指定 MIRROR TO 子句。

對於鏡像媒體集，每個 MIRROR TO 子句都必須列出與 TO 子句相同的裝置數目和類型。 下列範例會寫入含有兩個鏡像，且每個鏡像都使用三個裝置的鏡像媒體集中：

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
重要事項重要事項

這個範例的設計，是為了讓您在本機系統中進行測試。實際上，在相同磁碟機上備份多個裝置可能會降低效能，而且可能會減損鏡像媒體集原先設計的備援性。

鏡像媒體集中的媒體家族

在 BACKUP 陳述式之 TO 子句中指定的每個備份裝置都會對應到媒體家族。 例如，如果 TO 子句列出三個裝置，BACKUP 就會將資料寫入這三個媒體家族中。 在鏡像媒體集中，每個鏡像都必須包含每個媒體家族的複本。 這就是為什麼每個鏡像必須具有相同的裝置數目。

當各個鏡像分別列出多個裝置時，裝置順序會決定要將哪個媒體家族寫入特定裝置。 例如，在各份裝置清單中，第二個裝置都會對應到第二個媒體家族。 對於上面範例中的裝置，下表會說明這些裝置和媒體家族間的對應關係。

鏡像

媒體家族 1

媒體家族 2

媒體家族 3

0

Z:\AdventureWorks1a.bak

Z:\AdventureWorks2a.bak

Z:\AdventureWorks3a.bak

1

Z:\AdventureWorks1b.bak

Z:\AdventureWorks2b.bak

Z:\AdventureWorks3b.bak

媒體家族必須永遠備份到特定鏡像中的相同裝置。 因此，您每次使用現有媒體集時，都必須依照建立該媒體集時所指定的相同順序來列出每一個鏡像的裝置。

如需有關鏡像媒體集的詳細資訊，請參閱＜鏡像備份媒體集 (SQL Server)＞。 如需有關一般媒體集和媒體家族的詳細資訊，請參閱＜媒體集、媒體家族與備份組 (SQL Server)＞。

還原 SQL Server 備份

若要還原資料庫，並選擇性地復原它使其上線，或還原檔案或檔案群組，請使用 Transact-SQL RESTORE 陳述式或 SQL Server Management Studio [還原] 工作。 如需詳細資訊，請參閱＜還原和復原概觀 (SQL Server)＞。

關於 BACKUP 選項的其他考量

SKIP、NOSKIP、INIT 和 NOINIT 的互動

這個資料表描述 { NOINIT | INIT } 和 { NOSKIP | SKIP } 選項之間的互動方式。

注意

如果磁帶媒體是空的，或磁碟備份檔案不存在，所有這些互動都會寫入媒體標頭，並繼續作業。如果媒體不是空的，但缺少有效媒體標頭，這些作業會回應指出這不是有效的 MTF 媒體，而且備份作業將會中止。

 

NOINIT

INIT

NOSKIP

如果磁碟區包含有效的媒體標頭，則驗證如果有給定的 MEDIANAME，媒體名稱與其相符。 如果相符，則附加備份組，保留所有現有的備份組。

如果磁碟區並未包含有效的媒體標頭，便會發生錯誤。

如果磁碟區包含有效的媒體標頭，則執行下列檢查：

  • 如果指定了 MEDIANAME，則確認給定的媒體名稱符合媒體標頭的媒體名稱。 2

  • 確認媒體中沒有出現任何非預期的備份組。

    如果有，則結束備份。

如果通過這些檢查，則覆寫媒體中的任何備份組，只保留媒體標頭。

當磁碟區並未包含有效媒體標頭時，如果有指定的 MEDIANAME 和 MEDIADESCRIPTION，則產生含有它們的媒體標頭。

SKIP

如果磁碟區包含有效的媒體標頭，則附加備份組，保留所有現有的備份組。

如果磁碟區包含有效 1 的媒體標頭，則覆寫媒體中的任何備份組，只保留媒體標頭。

當媒體是空的時，如果有指定 MEDIANAME 和 MEDIADESCRIPTION，則產生含有它們的媒體標頭。

1 有效性包括 MTF 版本號碼和其他標頭資訊。 如果不支援指定的版本，或它不是預期的值，就會發生錯誤。

2 使用者必須屬於適當的固定資料庫或伺服器角色，來執行備份作業。

相容性

警告

在舊版 SQL Server 中，無法還原較新的 SQL Server 版本所建立的備份。

BACKUP 支援 RESTART 選項，以便提供與舊版 SQL Server 之間的回溯相容性。 但 RESTART 卻沒有任何作用。

一般備註

您可以將資料庫或記錄備份附加至任何磁碟或磁帶裝置，以便將資料庫及其交易記錄保留在單一實體位置中。

在明確或隱含的交易中，並不允許使用 BACKUP 陳述式。

只要作業系統支援資料庫的定序，便可以執行跨平台的備份作業，即使在不同類型的處理器之間，也是如此。

注意

根據預設，每項成功的備份作業都會在 SQL Server 錯誤記錄檔與系統事件記錄檔中加入一個項目。如果您經常備份記錄檔，這些成功訊息可能會快速累積，因而產生龐大的錯誤記錄檔，讓您難以尋找其他訊息。在這類情況下，如果沒有任何指令碼相依於這些記錄項目，您就可以使用追蹤旗標 3226 來隱藏這些記錄項目。如需詳細資訊，請參閱＜追蹤旗標 (Transact-SQL)＞。

互通性

SQL Server 會利用線上備份程序，使您能夠在使用資料庫時，備份資料庫。 在備份期間，您可以執行大部分的作業；例如，在備份作業期間，您可以執行 INSERT、UPDATE 或 DELETE 陳述式。

資料庫或交易記錄備份期間所無法執行的作業包括：

  • 檔案管理作業，例如，包含 ADD FILE 或 REMOVE FILE 選項的 ALTER DATABASE 陳述式。

  • 壓縮資料庫或壓縮檔案的作業。 其中包括自動壓縮作業。

如果備份作業與檔案管理或壓縮作業重疊，便會發生衝突。 不論是哪一項衝突作業在前面，第二項作業都會等待第一項作業所設定的鎖定逾時 (逾時期間由工作階段逾時設定來控制)。 如果在逾時期間解除鎖定，第二項作業就會繼續下去。 如果鎖定逾時，第二項作業就會失敗。

中繼資料

SQL Server 包括下列備份記錄資料表，其會追蹤備份活動：

當執行還原時，如果備份組尚未記錄在 msdb 資料庫中，就表示備份記錄資料表可能已修改過。

安全性

從 SQL Server 2012 開始，建立備份的 PASSWORD 和 MEDIAPASSWORD 選項已遭到停用。 仍然可以還原以密碼建立的備份。

權限

BACKUP DATABASE 和 BACKUP LOG 權限預設為系統管理員 (sysadmin) 固定伺服器角色以及 db_owner 和 db_backupoperator 固定資料庫角色的成員。

備份裝置實體檔案的擁有權和權限問題可能會干擾備份作業。 SQL Server 必須能夠讀取和寫入裝置；執行 SQL Server 服務的帳戶必須具備寫入權限。 不過，在系統資料表中加入備份裝置項目的 sp_addumpdevice 並不會檢查檔案存取權限。 當您嘗試備份或還原時，存取實體資源之前不一定會出現備份裝置實體檔案的這些問題。

範例

本節包含下列範例：

  • A. 備份完整資料庫

  • B. 備份資料庫和記錄

  • C. 建立次要檔案群組的完整檔案備份

  • D. 建立次要檔案群組的差異檔案備份

  • E. 建立和備份至單一家族的鏡像媒體集中

  • F. 建立和備份至多重家族的鏡像媒體集中

  • G. 備份至現有的鏡像媒體集

  • H. 在新的媒體集中建立壓縮備份

注意

備份的使用說明主題包含了其他的範例。如需詳細資訊，請參閱＜備份概觀 (SQL Server)＞。

A.備份完整資料庫

下列範例會將 AdventureWorks2012 資料庫備份到磁碟檔案。

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

B.備份資料庫和記錄

下列範例會備份 AdventureWorks2012 範例資料庫，依預設採用簡單復原模式。 為了支援記錄備份， AdventureWorks2012 資料庫會修改成使用完整復原模式。

接下來，範例會使用 sp_addumpdevice 建立邏輯備份裝置來備份資料 (AdvWorksData)，以及建立另一個邏輯備份裝置來備份記錄 (AdvWorksLog)。

這個範例接著會建立 AdvWorksData 的完整資料庫備份，並且在更新活動一段時間之後，將記錄備份到 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

注意

如果是實際執行的資料庫，請定期備份記錄。記錄的備份頻率必須足以保護資料不會遺失。

C.建立次要檔案群組的完整檔案備份

下列範例會為兩個次要檔案群組中的每個檔案建立完整檔案備份。

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

D.建立次要檔案群組的差異檔案備份

下列範例會為兩個次要檔案群組中的每個檔案建立差異檔案備份。

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

E.建立和備份至單一家族的鏡像媒體集中

下列範例會建立一個鏡像媒體集，其中包含單一媒體家族和四個鏡像，且會將 AdventureWorks2012 資料庫備份至其中。

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

F.建立和備份至多重家族的鏡像媒體集中

下列範例會建立一個鏡像媒體集，其中的每個鏡像都由兩個媒體家族組成。 之後，這個範例會將 AdventureWorks2012 資料庫備份在這兩個鏡像中。

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

G.備份至現有的鏡像媒體集中

下列範例會將備份組附加至先前範例所建立的媒體集中。

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

注意

NOINIT 是預設值，這裡顯示它是為了更加清楚。

[精選範例]

H.在新的媒體集中建立壓縮備份

下列範例會將媒體格式化、建立新的媒體集，並執行 AdventureWorks2012 資料庫的完整壓縮備份。

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

[精選範例]

請參閱

參考

ALTER DATABASE (Transact-SQL)

DBCC SQLPERF (Transact-SQL)

RESTORE (Transact-SQL)

RESTORE FILELISTONLY (Transact-SQL)

RESTORE HEADERONLY (Transact-SQL)

RESTORE LABELONLY (Transact-SQL)

RESTORE VERIFYONLY (Transact-SQL)

sp_addumpdevice (Transact-SQL)

sp_configure (Transact-SQL)

sp_helpfile (Transact-SQL)

sp_helpfilegroup (Transact-SQL)

概念

備份裝置 (SQL Server)

媒體集、媒體家族與備份組 (SQL Server)

結尾記錄備份 (SQL Server)

伺服器組態選項 (SQL Server)

分次還原具有記憶體最佳化資料表的資料庫