復原模式 (SQL Server)Recovery Models (SQL Server)

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

SQL ServerSQL Server 備份和還原作業是在資料庫之復原模式的內容中進行。backup and restore operations occur within the context of the recovery model of the database. 復原模式的設計目的是要控制交易記錄維護。Recovery models are designed to control transaction log maintenance. 「復原模式」 是一項資料庫屬性,可控制交易的記錄方式、是否需要 (及允許) 備份交易記錄,以及可用的還原作業類型。A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. 復原模式共有三種:簡單、完整和大量記錄。Three recovery models exist: simple, full, and bulk-logged. 一般而言,資料庫會使用完整復原模式或簡單復原模式。Typically, a database uses the full recovery model or simple recovery model. 資料庫可以隨時切換到另一個復原模式。A database can be switched to another recovery model at any time.

本主題內容:In this Topic:

復原模式概觀Recovery Model Overview

下表摘要說明三種復原模式。The following table summarizes the three recovery models.

復原模式Recovery model DescriptionDescription 工作損失風險Work loss exposure 復原至時間點?Recover to point in time?
SimpleSimple 無記錄備份。No log backups.

自動收回記錄空間,使空間需求保持在最低,實際消弭管理交易記錄空間的需求。Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. 如需簡單復原模式下之資料庫備份的相關資訊,請參閱完整資料庫備份 (SQL Server)For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).

簡單復原模式不支援需要交易記錄備份的作業。Operations that require transaction log backups are not supported by the simple recovery model. 在簡單復原模式中,不能使用下列功能:The following features cannot be used in simple recovery mode:

-記錄傳送-Log shipping

-AlwaysOn 或資料庫鏡像-Always On or Database mirroring

-無資料遺失的媒體復原-Media recovery without data loss

-時間點還原-Point-in-time restores
最近一次備份之後所做的變更並未受到保護。Changes since the most recent backup are unprotected. 如果發生損毀事件,則必須重做這些變更。In the event of a disaster, those changes must be redone. 只能復原至備份結束時。Can recover only to the end of a backup. 如需詳細資訊,請參閱完整資料庫還原 (簡單復原模式)For more information, see Complete Database Restores (Simple Recovery Model).

如需簡單復原模式的更深入說明,請參閱 SQL Server Simple Recovery Model 人員所提供的 SQL Server Simple Recovery Model(SQL Server 簡單復原模式)。For a more in depth explanation of the Simple recovery model, see SQL Server Simple Recovery Model provided by the folks at MSSQLTips!
FullFull 需要記錄備份。Requires log backups.

不因損失或損毀資料檔案而失去任何工作。No work is lost due to a lost or damaged data file.

可復原至任意時間點 (例如,應用程式或使用者錯誤前)。Can recover to an arbitrary point in time (for example, prior to application or user error). 如需完整復原模式下之資料庫備份的相關資訊,請參閱完整資料庫備份 (SQL Server)完整資料庫還原 (完整復原模式)For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).
通常沒有。Normally none.

如果記錄結尾損毀,必須重做最近一次記錄備份後的變更。If the tail of the log is damaged, changes since the most recent log backup must be redone.
可以復原至特定時間點 (假設您已完成至該時間點的備份)。Can recover to a specific point in time, assuming that your backups are complete up to that point in time. 如需使用記錄備份還原至失敗點的相關資訊,請參閱將 SQL Server 資料庫還原至某個時間點 (完整復原模式)For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

注意:如果您有超過兩個邏輯必須一致的完整復原模式資料庫,您可能需要實作特殊的程序,確保這些資料庫能夠復原。Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. 如需詳細資訊,請參閱 復原包含標示之交易的相關資料庫For more information, see Recovery of Related Databases That Contain Marked Transaction.
大量記錄Bulk logged 需要記錄備份。Requires log backups.

完整復原模式的輔助,允許執行高效能的大量複製作業。An adjunct of the full recovery model that permits high-performance bulk copy operations.

針對大多數的大量作業使用最少記錄,以減少記錄空間的使用量。Reduces log space usage by using minimal logging for most bulk operations. 如需只記錄基本資訊之作業的相關資訊,請參閱交易記錄 (SQL Server)For information about operations that can be minimally logged, see The Transaction Log (SQL Server).

如需大量記錄復原模式下之資料庫備份的相關資訊,請參閱完整資料庫備份 (SQL Server)完整資料庫還原 (完整復原模式)For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).
如果記錄損毀,或在最近一次記錄備份後進行過大量記錄作業的話,必須重做最近一次備份後的變更。If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

否則不會損失任何工作。Otherwise, no work is lost.
可復原至任何備份結束時。Can recover to the end of any backup. 不支援時間點復原。Point-in-time recovery is not supported.

相關工作Related Tasks

另請參閱See Also

backupset (Transact-SQL) backupset (Transact-SQL)
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
ALTER DATABASE SET 選項 (Transact-SQL) ALTER DATABASE SET Options (Transact-SQL)
SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
交易記錄 (SQL Server) The Transaction Log (SQL Server)
自動化管理工作 (SQL Server Agent) Automated Administration Tasks (SQL Server Agent)
還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)