完整資料庫還原 (簡單復原模式)Complete Database Restores (Simple Recovery Model)

適用於: 是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

在完整資料庫還原中,目標是還原整個資料庫。In a complete database restore, the goal is to restore the whole database. 在還原期間,整個資料庫為離線狀態。The whole database is offline for the duration of the restore. 在讓資料庫的任何部分上線之前,所有的資料都必須復原到一致的位置;此時資料庫的所有部分都會回到相同的時間點,而且沒有未認可的交易存在。Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.

在簡單復原模式下,無法將資料庫還原到特定備份中的特定時間點。Under the simple recovery model, the database cannot be restored to a specific point in time within a specific backup.

重要

建議您不要附加或還原來源不明或來源不受信任的資料庫。We recommend that you do not attach or restore databases from unknown or untrusted sources. 這些資料庫可能包含惡意程式碼,因此可能執行非預期的 Transact-SQLTransact-SQL 程式碼,或是修改結構描述或實體資料庫結構而造成錯誤。These databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 使用來源不明或來源不受信任的資料庫之前,請先在非實際執行伺服器的資料庫上執行 DBCC CHECKDB ,同時檢查資料庫中的程式碼,例如預存程序或其他使用者定義程式碼。Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

本主題內容:In this Topic:

注意

如需舊版 SQL ServerSQL Server之備份支援的相關資訊,請參閱 RESTORE (Transact-SQL)的<相容性支援>一節。For information about support for backups from earlier versions of SQL ServerSQL Server, see the "Compatibility Support" section of RESTORE (Transact-SQL).

簡單復原模式下的資料庫備份概觀Overview of Database Restore Under the Simple Recovery Model

在簡單復原模式下進行完整資料庫還原只需要一個或兩個 RESTORE 陳述式,視是否想要還原差異資料庫備份而定。A full database restore under the simple recovery model involves one or two RESTORE statements, depending on whether you want to restore a differential database backup. 如果您只使用完整資料庫備份,則只需要還原最近的備份,如下圖所示。If you are using only a full database backup, just restore the most recent backup, as shown in the following illustration.

只還原完整資料庫備份Restoring only a full database backup

如果也要使用差異資料庫備份,請還原最近一次完整資料庫備份,但不要復原資料庫,然後才還原最近一次差異資料庫備份,並復原資料庫。If you are also using a differential database backup, restore the most recent full database backup without recovering the database, and then restore the most recent differential database backup and recover the database. 下圖顯示這項程序。The following illustration shows this process.

還原完整和差異資料庫備份Restoring full and differential database backups

注意

若您想將資料庫備份還原至不同的伺服器執行個體,請參閱 使用備份與還原複製資料庫If you plan to restore a database backup onto a different server instance, see Copy Databases with Backup and Restore.

基本 Transact-SQL RESTORE 語法Basic Transact-SQL RESTORE Syntax

用於還原完整資料庫備份的基本 Transact-SQLTransact-SQLRESTORE 語法為:The basic Transact-SQLTransact-SQLRESTORE syntax for restoring a full database backup is:

RESTORE DATABASE database_name FROM backup_device [ WITH NORECOVERY ]RESTORE DATABASE database_name FROM backup_device [ WITH NORECOVERY ]

注意

若您也想還原差異資料庫備份,請使用 WITH NORECOVERY。Use WITH NORECOVERY if you plan to also restore a differential database backup.

用於還原資料庫備份的基本 RESTORE 語法為:The basic RESTORE syntax for restoring a database backup is:

RESTORE DATABASE database_name FROM backup_device WITH RECOVERYRESTORE DATABASE database_name FROM backup_device WITH RECOVERY

範例 (Transact-SQL)Example (Transact-SQL)

下列範例首先顯示如何使用 BACKUP 陳述式來建立 AdventureWorks2012AdventureWorks2012 資料庫的完整資料庫備份及差異資料庫備份。The following example first shows how to use the BACKUP statement to create a full database backup and a differential database backup of the AdventureWorks2012AdventureWorks2012 database. 此範例接著依序還原這些備份。The example then restores these backups in sequence. 資料庫會還原到差異資料庫備份完成時的狀態。The database is restored to its state as of the time that the differential database backup finished.

這個範例顯示在完整資料庫還原實例中,還原順序的一些關鍵選項。The example shows the critical options in a restore sequence for the complete database restore scenario. 「還原順序」 (Restore sequence) 包含一個或多個還原作業,會在一個或多個還原階段中移動資料。A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. 會省略與這個檔案還原無關的語法和詳細資料。Syntax and details that are not relevant to this purpose are omitted. 為了清楚起見,建議您在復原資料庫時明確指定 RECOVERY 選項,即使它是預設的。When you recover a database, we recommend explicitly specifying the RECOVERY option for clarity, even though it is the default.

注意

此範例會從設定復原模式為 ALTER DATABASE SIMPLE陳述式開始進行。The example starts with an ALTER DATABASE statement that sets the recovery model to SIMPLE.

USE master;  
--Make sure the database is using the simple recovery model.  
ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE;  
GO  
-- Back up the full AdventureWorks2012 database.  
BACKUP DATABASE AdventureWorks2012   
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'   
  WITH FORMAT;  
GO  
--Create a differential database backup.  
BACKUP DATABASE AdventureWorks2012   
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'  
   WITH DIFFERENTIAL;  
GO  
--Restore the full database backup (from backup set 1).  
RESTORE DATABASE AdventureWorks2012   
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'   
   WITH FILE=1, NORECOVERY;  
--Restore the differential backup (from backup set 2).  
RESTORE DATABASE AdventureWorks2012   
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'   
   WITH FILE=2, RECOVERY;  
GO  

相關工作Related Tasks

還原完整資料庫備份To restore a full database backup

還原差異資料庫備份To restore a differential database backup

使用 SQL Server 管理物件 (SMO) 還原備份To restore a backup by using SQL Server Management Objects (SMO)

另請參閱See Also

RESTORE (Transact-SQL) RESTORE (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
sp_addumpdevice (Transact-SQL) sp_addumpdevice (Transact-SQL)
完整資料庫備份 (SQL Server) Full Database Backups (SQL Server)
差異備份 (SQL Server) Differential Backups (SQL Server)
備份概觀 (SQL Server) Backup Overview (SQL Server)
還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)