Backup Overview (SQL Server)Backup Overview (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主題介紹 SQL ServerSQL Server 備份元件。This topic introduces the SQL ServerSQL Server backup component. 備份 SQL ServerSQL Server 資料庫對於保護資料非常重要。Backing up your SQL ServerSQL Server database is essential for protecting your data. 此討論涵蓋備份類型和備份限制。This discussion covers backup types, and backup restrictions. 本主題同時介紹 SQL ServerSQL Server 備份裝置和備份媒體。The topic also introduces SQL ServerSQL Server backup devices and backup media.

詞彙Terms

備份 (back up) [動詞]back up [verb]
將資料或記錄檔記錄從 SQL ServerSQL Server 資料庫或其交易記錄複製至備份裝置 (例如磁碟),以建立資料備份或記錄備份。Copies the data or log records from a SQL ServerSQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.

備份 (backup) [名詞]backup [noun]
失敗後可用來還原和復原資料的 SQL ServerSQL Server 資料副本。A copy of SQL ServerSQL Server data that can be used to restore and recover the data after a failure. SQL ServerSQL Server 資料備份會在資料庫層級或者一個或多個資料庫檔案或檔案群組層級建立。A backup of SQL ServerSQL Server data is created at the level of a database or one or more of its files or filegroups. 無法建立資料表層級備份。Table-level backups cannot be created. 除了資料備份之外,完整復原模式也需要建立交易記錄備份。In addition to data backups, the full recovery model requires creating backups of the transaction log.

復原模式recovery model
控制資料庫上交易記錄維護的資料庫屬性。A database property that controls transaction log maintenance on a database. 復原模式共有三種:簡單、完整和大量記錄。Three recovery models exist: simple, full, and bulk-logged. 資料庫的復原模式決定其備份和還原需求。The recovery model of database determines its backup and restore requirements.

還原restore
一個多階段的程序,它會將指定之 SQL ServerSQL Server 備份中的所有資料和記錄頁面複製到指定的資料庫中,然後藉由套用所記錄的變更取得前面時段的資料,向前復原備份中記錄的所有交易。A multi-phase process that copies all the data and log pages from a specified SQL ServerSQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

備份類型Types of backups

僅複製備份copy-only backup
不受 SQL ServerSQL Server 一般備份順序影響的特殊用途備份。A special-use backup that is independent of the regular sequence of SQL ServerSQL Server backups.

資料備份 data backup
整個資料庫 (資料庫備份)、部分資料庫 (部分備份) 或是一組資料檔案或檔案群組 (檔案備份) 中資料的備份。A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

資料庫備份database backup
資料庫的備份。A backup of a database. 完整資料庫備份代表備份完成時的整個資料庫。Full database backups represent the whole database at the time the backup finished. 差異資料庫備份僅包含自其最近的完整資料庫備份以來,對資料庫所做的變更。Differential database backups contain only changes made to the database since its most recent full database backup.

差異備份differential backup
一種資料備份,是以整個或部分資料庫或一組資料檔案或檔案群組 (「差異基底」) 的最新完整備份為基礎,而且只包含自差異基底以來變更的資料範圍。A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base.

差異部分備份僅記錄自上一次部分備份後在檔案群組中變更過的資料範圍,稱為差異基底。A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

完整備份full backup
一種資料備份,包含特定資料庫或一組檔案群組或檔案中的所有資料,也包含足以讓這個資料復原的記錄。A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

記錄備份log backup
交易記錄的備份,包含先前的記錄備份中未備份的所有記錄。A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (完整復原模式)(full recovery model)

檔案備份file backup
一個或多個資料庫檔案或檔案群組的備份。A backup of one or more database files or filegroups.

部分備份partial backup
僅包含資料庫中某些檔案群組中的資料,包括主要檔案群組、每個讀取/寫入檔案群組,以及任何選擇性指定之唯讀檔案中的資料。Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

備份媒體詞彙和定義Backup media terms and definitions

備份裝置backup device
寫入 SQL ServerSQL Server 備份並從中進行還原的磁碟或磁帶裝置。A disk or tape device to which SQL ServerSQL Server backups are written and from which they can be restored. SQL Server 備份也可以寫入 Azure Blob 儲存體服務,而且會使用 URL 格式來指定備份檔案的目的地和名稱。SQL Server backups can also be written to an Azure Blob storage service, and URL format is used to specify the destination and the name of the backup file.. 如需詳細資訊,請參閱 使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

備份媒體backup media
已寫入一個或多個備份的一個或多個磁帶或磁碟檔案。One or more tapes or disk files to which one or more backup have been written.

備份組backup set
透過成功的備份作業,加入至媒體集的備份內容。The backup content that is added to a media set by a successful backup operation.

媒體家族media family
在單一非鏡像裝置上或媒體集的一組鏡像裝置上所建立的備份。Backups created on a single nonmirrored device or a set of mirrored devices in a media set

媒體集media set
按順序排列的備份媒體集合 (磁帶或磁碟檔案),由一個或多個備份作業使用固定的備份裝置類型與數量寫入。An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.

鏡像媒體集mirrored media set
多份媒體集副本 (鏡像)。Multiple copies (mirrors) of a media set.

備份壓縮Backup compression

SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 和更新版本支援壓縮備份,而 SQL Server 2008SQL Server 2008 和更新版本可以還原壓縮的備份。and later versions support compressing backups, and SQL Server 2008SQL Server 2008 and later versions can restore a compressed backup. 如需詳細資訊,請參閱備份壓縮 (SQL Server)For more information, see Backup Compression (SQL Server).

備份作業限制Backup operations restrictions

可在資料庫仍在線上運作以及正在使用中的時候進行備份。Backup can occur while the database is online and being used. 不過,會有下列限制:However, the following restrictions exist.

無法備份離線資料Cannot back up offline data

隱含或明確參考離線資料的任何備份作業都會失敗。Any backup operation that implicitly or explicitly references data that is offline fails. 一些典型的例子如下:Some typical examples include the following:

  • 要求進行完整資料庫備份,但資料庫的一個檔案群組為離線狀態。You request a full database backup, but one filegroup of the database is offline. 因為所有檔案群組是明確納入在完整資料庫備份中,所以此作業會失敗。Because all filegroups are implicitly included in a full database backup, this operation fails.

    若要備份這個資料庫,您可以使用檔案備份,並且指定只限在線上的檔案群組。To back up this database, you can use a file backup and specify only the filegroups that are online.

  • 您要求進行部分備份,但讀取/寫入檔案群組處於離線狀態。You request a partial backup, but a read/write filegroup is offline. 因為部分備份需要所有的讀取/寫入檔案群組,所以此作業會失敗。Because all read/write filegroups are required for a partial backup, the operation fails.

  • 要求進行特定檔案的檔案備份,但其中一個檔案不在線上。You request a file backup of specific files, but one of the files is not online. 該作業會失敗。The operation fails. 若要備份線上檔案,您可以省略檔案清單中的離線檔案,然後重複該作業。To back up the online files, you can omit the offline file from the file list and repeat the operation.

一般而言,即使有一個或多個資料檔案無法使用,記錄備份都會成功。Typically, a log backup succeeds even if one or more data files are unavailable. 不過,如果在大量記錄復原模式下變更任何包含大量記錄的檔案,則必須所有檔案都在線上,才能讓備份成功。However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.

並行限制Concurrency restrictions

SQL ServerSQL Server 利用線上備份處理序,使您能夠在資料庫處於使用狀態時備份資料庫。uses an online backup process to allow for a database backup while the database is still being used. 在備份期間,您可以執行大部分的作業;例如,在備份作業期間,您可以執行 INSERT、UPDATE 或 DELETE 陳述式。During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation. 不過,如果試圖在建立或刪除資料庫檔案過程中啟動備份作業,則備份作業會等候到建立或刪除作業完成,或備份逾時為止。However, if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.

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

  • 檔案管理作業,例如,含有 ADD FILE 或 REMOVE 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 you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

如果備份作業與檔案管理或壓縮作業重疊,便會發生衝突。If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. 不論哪一個衝突的作業先開始,第二項作業都會等候第一項作業設定的鎖定逾時(逾時期間由工作階段逾時設定控制)。如果在逾時期間解除鎖定,第二項作業就會繼續下去。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 time-out setting.) If the lock is released during the time-out period, the second operation continues. 如果鎖定逾時,第二項作業就會失敗。If the lock times out, the second operation fails.

相關工作Related tasks

備份裝置和備份媒體Backup devices and backup media

建立備份Create a backup

注意

如果是部分備份或只複製備份,就必須分別搭配 PARTIAL 或 COPY_ONLY 選項來使用 Transact-SQLTransact-SQLBACKUP 陳述式。For partial or copy-only backups, you must use the Transact-SQLTransact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

更多!And more!

SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
還原和復原概觀 (SQL Server) Restore and Recovery Overview (SQL Server)
維護計畫 Maintenance Plans
交易記錄 (SQL Server) The Transaction Log (SQL Server)
復原模式 (SQL Server)Recovery Models (SQL Server)