RESTORE 陳述式 (Transact-SQL)RESTORE Statements (Transact-SQL)

還原利用 BACKUP 命令取得的 SQL 資料庫備份。Restores SQL database backups taken using the BACKUP command.

按一下下列其中一個索引標籤,以查看您所使用特定 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

此命令可讓您執行以下還原案例:This command enables you to perform the following restore scenarios:

  • 從完整資料庫備份還原整個資料庫 (完整還原)。Restore an entire database from a full database backup (a complete restore).
  • 還原部分資料庫 (部分還原)。Restore part of a database (a partial restore).
  • 將特定檔案或檔案群組還原到資料庫 (檔案還原)。Restore specific files or filegroups to a database (a file restore).
  • 將特定頁面還原到資料庫 (分頁還原)。Restore specific pages to a database (a page restore).
  • 將交易記錄還原到資料庫 (交易記錄還原)。Restore a transaction log onto a database (a transaction log restore).
  • 將資料庫還原到資料庫快照集所擷取的時間點。Revert a database to the point in time captured by a database snapshot.

如需 SQL ServerSQL Server 還原案例的詳細資訊,請參閱還原和復原概觀For more information about SQL ServerSQL Server restore scenarios, see Restore and Recovery Overview. 如需引數描述的詳細資訊,請參閱 RESTORE 引數For more information about descriptions of the arguments, see RESTORE Arguments. 從另一個執行個體還原資料庫時,請考慮 在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料 (SQL Server)中的資訊。When restoring a database from another instance, consider the information from Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

注意

如需從 Microsoft Azure Blob 儲存體服務進行還原的詳細資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For more information about restoring from the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

語法Syntax

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
 [ FROM <backup_device> [ ,...n ] ]
 [ WITH
   {
    [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
   | ,  <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
   | , <change_data_capture_WITH_option>
   | , <FILESTREAM_WITH_option>
   | , <service_broker_WITH options>
   | , \<point_in_time_WITH_options-RESTORE_DATABASE>
   } [ ,...n ]
 ]
[;]

--To perform the first step of the initial restore sequence of a piecemeal restore:
RESTORE DATABASE { database_name | @database_name_var }
   <files_or_filegroups> [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ]
   WITH
      PARTIAL, NORECOVERY
      [  , <general_WITH_options> [ ,...n ]
       | , \<point_in_time_WITH_options-RESTORE_DATABASE>
      ] [ ,...n ]
[;]  

--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name | @database_name_var }
   <file_or_filegroup> [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ]
   WITH
   {
      [ RECOVERY | NORECOVERY ]
      [ , <general_WITH_options> [ ,...n ] ]
   } [ ,...n ]
[;]  

--To Restore Specific Pages:
RESTORE DATABASE { database_name | @database_name_var }
   PAGE = 'file:page [ ,...n ]'
 [ , <file_or_filegroups> ] [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ]
   WITH
       NORECOVERY
      [ , <general_WITH_options> [ ,...n ] ]
[;]

--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var }
 [ <file_or_filegroup_or_pages> [ ,...n ] ]
 [ FROM <backup_device> [ ,...n ] ]
 [ WITH
   {
     [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
    | , <general_WITH_options> [ ,...n ]
    | , <replication_WITH_option>
    | , \<point_in_time_WITH_options-RESTORE_LOG>
   } [ ,...n ]
 ]
[;]

--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name

<backup_device>::=
{
   { logical_backup_device_name |
      @logical_backup_device_name_var }
 | { DISK
     | TAPE
     | URL
   } = { 'physical_backup_device_name' |
      @physical_backup_device_name_var }
}
Note: URL is the format used to specify the location and the file name for the Microsoft Azure Blob. Although Microsoft Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all the three devices.
<files_or_filegroups>::=
{
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
 | READ_WRITE_FILEGROUPS
}

<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
          [ ,...n ]
 | REPLACE
 | RESTART
 | RESTRICTED_USER | CREDENTIAL

--Backup Set Options
 | FILE = { backup_set_file_number | @backup_set_file_number }
 | PASSWORD = { password | @password_variable }

--Media Set Options
 | MEDIANAME = { media_name | @media_name_variable }
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

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

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

--Monitoring Options
 | STATS [ = percentage ]

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

<replication_WITH_option>::=
 | KEEP_REPLICATION

<change_data_capture_WITH_option>::=
 | KEEP_CDC

<FILESTREAM_WITH_option>::=
 | FILESTREAM ( DIRECTORY_NAME = directory_name )

<service_broker_WITH_options>::=
 | ENABLE_BROKER
 | ERROR_BROKER_CONVERSATIONS
 | NEW_BROKER

\<point_in_time_WITH_options-RESTORE_DATABASE>::=
 | {
   STOPAT = { 'datetime'| @datetime_var }
 | STOPATMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']
 | STOPBEFOREMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']
   }

\<point_in_time_WITH_options-RESTORE_LOG>::=
 | {
   STOPAT = { 'datetime'| @datetime_var }
 | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
 | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
   }

引數Arguments

如需引數的描述,請參閱 RESTORE 引數For descriptions of the arguments, see RESTORE Arguments.

關於還原狀況About Restore Scenarios

SQL ServerSQL Server 支援各種還原狀況:supports a variety of restore scenarios:

當支援線上還原時,如果資料庫在線上,檔案還原和分頁還原會自動成為線上還原,另外,也會在分次還原的初始階段之後,還原次要檔案群組。Where online restore is supported, if the database is online, file restores and page restores are automatically online restores and, also, restores of secondary filegroup after the initial stage of a piecemeal restore.

注意

線上還原可能涉及延遲交易Online restores can involve deferred transactions.

如需詳細資訊,請參閱線上還原For more information, see Online Restore.

關於 RESTORE 選項的其他考量Additional Considerations About RESTORE Options

已停止的 RESTORE 關鍵字Discontinued RESTORE Keywords

下列關鍵字在 SQL Server 2008SQL Server 2008 中已停止:The following keywords were discontinued in SQL Server 2008SQL Server 2008:

已停止的關鍵字Discontinued keyword 取代為...Replaced by... 取代關鍵字的範例Example of replacement keyword
LOADLOAD RESTORERESTORE RESTORE DATABASE
TRANSACTIONTRANSACTION LOGLOG RESTORE LOG
DBO_ONLYDBO_ONLY RESTRICTED_USERRESTRICTED_USER RESTORE DATABASE ... WITH RESTRICTED_USER

RESTORE LOGRESTORE LOG

RESTORE LOG 可以包括一份檔案清單,讓您在向前復原期間建立檔案。RESTORE LOG can include a file list to allow for creation of files during roll forward. 如果記錄備份包含檔案加入資料庫時所撰寫的記錄檔記錄,便使用這個項目。This is used when the log backup contains log records written when a file was added to the database.

注意

如果是使用完整或大量記錄復原模式的資料庫,在大部分情況下,您必須先備份記錄結尾,再還原資料庫。For a database using the full or bulk-logged recovery model, in most cases you must back up the tail of the log before restoring the database. 除非 RESTORE DATABASE 陳述式包含 WITH REPLACE 或 WITH STOPAT 子句 (必須指定在資料備份結束之後發生的時間或交易),否則如果沒有先備份記錄結尾便還原資料庫,就會產生錯誤。Restoring a database without first backing up the tail of the log results in an error, unless the RESTORE DATABASE statement contains either the WITH REPLACE or the WITH STOPAT clause, which must specify a time or transaction that occurred after the end of the data backup. 如需結尾記錄備份的詳細資訊,請參閱結尾記錄備份For more information about tail-log backups, see Tail-Log Backups.

比較 RECOVERY 和 NORECOVERYComparison of RECOVERY and NORECOVERY

RESTORE 陳述式利用 [ RECOVERY | NORECOVERY ] 選項來控制回復:Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

  • NORECOVERY 指定不進行回復。NORECOVERY specifies that roll back not occur. 這使向前復原能夠繼續循序執行下一個陳述式。This allows roll forward to continue with the next statement in the sequence.

    在這個情況下,還原順序可以還原其他備份,並將它們向前復原。In this case, the restore sequence can restore other backups and roll them forward.

  • RECOVERY (預設值) 表示在完成目前備份的向前復原之後,應該執行回復。RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.

    復原資料庫時,會要求要還原的整組資料 (向前復原集 ) 與資料庫一致。Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. 如果向前復原集尚未向前復原到足以與資料庫一致的範圍,且指定了 RECOVERY,Database EngineDatabase Engine 就會發出錯誤。If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database EngineDatabase Engine issues an error.

相容性支援Compatibility Support

SQL Server 2017SQL Server 2017 無法還原使用舊版 SQL ServerSQL Server 來建立的 master modelmsdb 備份。Backups of master, model and msdb that were created by using an earlier version of SQL ServerSQL Server cannot be restored by SQL Server 2017SQL Server 2017.

注意

您無法將 SQL ServerSQL Server 備份還原成比建立備份所用之版本還舊的 SQL ServerSQL Server 版本。No SQL ServerSQL Server backup be restored to an earlier version of SQL ServerSQL Server than the version on which the backup was created.

SQL ServerSQL Server 的每一個版本都會使用與舊版不同的預設路徑。Each version of SQL ServerSQL Server uses a different default path than earlier versions. 因此,若要還原在舊版備份之預設位置中所建立的資料庫,就必須使用 MOVE 選項。Therefore, to restore a database that was created in the default location for earlier version backups, you must use the MOVE option. 如需有關新預設路徑的資訊,請參閱 SQL Server 的預設和具名執行個體的檔案位置For information about the new default path, see File Locations for Default and Named Instances of SQL Server.

將舊版資料庫還原成 SQL Server 2017SQL Server 2017之後,資料庫會自動升級。After you restore an earlier version database to SQL Server 2017SQL Server 2017, the database is automatically upgraded. 通常,資料庫立即變為可用。Typically, the database becomes available immediately. 不過,如果 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料庫具有全文檢索索引,升級過程就會根據 upgrade_option 伺服器屬性的設定來匯入、重設或重建這些索引。However, if a SQL Server 2005 (9.x)SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. 如果升級選項設定為匯入 (upgrade_option = 2) 或重建 (upgrade_option = 0),則全文檢索索引在升級期間將無法使用。If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. 根據進行索引的資料數量而定,匯入可能需要數個小時,而重建可能需要十倍以上的時間。Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. 此外,請注意,當升級選項設定為 [匯入] 時,如果全文檢索目錄無法使用,系統就會重建相關聯的全文檢索索引。Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. 若要變更 upgrade_option 伺服器屬性的設定,請使用 sp_fulltext_serviceTo change the setting of the upgrade_option server property, use sp_fulltext_service.

當資料庫第一次連接或還原到新的 SQL ServerSQL Server執行個體時,資料庫主要金鑰複本 (由服務主要金鑰加密) 尚未儲存在伺服器中。When a database is first attached or restored to a new instance of SQL ServerSQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. 您必須利用 OPEN MASTER KEY 陳述式來解密資料庫主要金鑰 (DMK)。You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). DMK 解密之後,您便可以選擇利用 ALTER MASTER KEY REGENERATE 陳述式來提供服務主要金鑰 (SMK) 所加密的 DMK 複本給伺服器,以在未來啟用自動解密。Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). 當資料庫從舊版升級時,應該會重新產生 DMK 以使用較新的 AES 演算法。When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. 如需重新產生 DMK 的詳細資訊,請參閱 ALTER MASTER KEYFor more information about regenerating the DMK, see ALTER MASTER KEY. 重新產生 DMK 金鑰以升級至 AES 所需的時間是取決於 DMK 所保護的物件數目而定。The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. 重新產生 DMK 金鑰以升級至 AES 只需執行一次,且不會影響金鑰循環策略中後續的重新產生。Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

一般備註General Remarks

在離線還原期間,如果指定的資料庫在使用中,RESTORE 會在一小段延遲之後,強迫使用者結束作業。During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay. 如果是非主要檔案群組的線上還原,除非正在還原的檔案群組在離線中,否則,資料庫會保持使用中的狀態。For online restore of a non-primary filegroup, the database can stay in use except when the filegroup being restored is being taken offline. 還原的資料會取代指定之資料庫中的任何資料。Any data in the specified database is replaced by the restored data.

如需資料庫復原的詳細資訊,請參閱還原和復原概觀For more information about database recovery, see Restore and Recovery Overview.

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

RESTORE 可以在發生錯誤之後,重新啟動。RESTORE can be restarted after an error. 另外,您也可以指示 RESTORE 不論是否發生錯誤,一律繼續作業,它會盡可能還原多一點的資料 (請參閱 CONTINUE_AFTER_ERROR 選項)。In addition, you can instruct RESTORE to continue despite errors, and it restores as much data as possible (see the CONTINUE_AFTER_ERROR option).

在明確或隱含的交易中,不允許使用 RESTORE。RESTORE is not allowed in an explicit or implicit transaction.

損毀的 master資料庫必須利用特殊程序來還原。Restoring a damaged master database is performed using a special procedure. 如需詳細資訊,請參閱系統資料庫的備份與還原For more information, see Back Up and Restore of System Databases.

還原資料庫會清除 SQL ServerSQL Server 執行個體的計畫快取。Restoring a database clears the plan cache for the instance of SQL ServerSQL Server. 清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 針對每次清除計畫快取的快取存放區,SQL ServerSQL Server 錯誤記錄檔會包含下列資訊訊息:「由於某些資料庫維護或重新設定作業,SQL ServerSQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清」。For each cleared cachestore in the plan cache, the SQL ServerSQL Server error log contains the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

若要還原可用性資料庫,請先將資料庫還原至 SQL ServerSQL Server 執行個體,然後再將資料庫新增至可用性群組。To restore an availability database, first restore the database to the instance of SQL ServerSQL Server, and then add the database to the availability group.

互通性Interoperability

資料庫設定和還原Database Settings and Restoring

在還原期間,大部分可以利用 ALTER DATABASE 來設定的資料庫選項都會重設為備份結束時的有效值。During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup.

不過,使用 WITH RESTRICTED_USER 選項會覆寫使用者存取選項設定的這個行為。Using the WITH RESTRICTED_USER option, however, overrides this behavior for the user access option setting. 這項設定一律設在 RESTORE 陳述式之後,其中包括 WITH RESTRICTED_USER 選項。This setting is always set following a RESTORE statement, which includes the WITH RESTRICTED_USER option.

還原加密資料庫Restoring an Encrypted Database

若要還原加密的資料庫,您必須能夠存取之前用來加密資料庫的憑證或非對稱金鑰。To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 如果沒有該憑證或非對稱金鑰,就無法還原資料庫。Without the certificate or asymmetric key, the database cannot be restored. 因此,只要需要備份,就必須保留用來加密資料庫加密金鑰的憑證。As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. 如需詳細資訊,請參閱 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

還原啟用 Vardecimal 儲存的資料庫Restoring a Database Enabled for vardecimal Storage

備份與還原可以搭配 vardecimal 儲存格式正常運作。Backup and restore work correctly with the vardecimal storage format. 如需 vardecimal 儲存格式的詳細資訊,請參閱 sp_db_vardecimal_storage_formatFor more information about vardecimal storage format, see sp_db_vardecimal_storage_format.

還原全文檢索資料Restore Full-Text Data

在完整還原期間,全文檢索資料會與其他資料庫資料一起還原。Full-text data is restored together with other database data during a complete restore. 當使用正規 RESTORE DATABASE database_name FROM backup_device 語法時,還原資料庫檔也會還原全文檢索檔案。Using the regular RESTORE DATABASE database_name FROM backup_device syntax, the full-text files are restored as part of the database file restore.

您也可以利用 RESTORE 陳述式,將全文檢索資料還原到替代位置,以及執行全文檢索資料的差異還原、檔案和檔案群組還原及差異檔案和檔案群組還原。The RESTORE statement also can be used to perform restores to alternate locations, differential restores, file and filegroup restores, and differential file and filegroup restores of full-text data. 另外,RESTORE 只能連同資料庫資料一起還原全文檢索檔案。In addition, RESTORE can restore full-text files only, as well as with database data.

注意

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 匯入的全文檢索目錄仍然會視為資料庫檔案。Full-text catalogs imported from SQL Server 2005 (9.x)SQL Server 2005 (9.x) are still treated as database files. 對於這些檔案而言,備份全文檢索目錄的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 程序會維持適用狀態,不過不再需要於備份作業期間暫停和繼續。For these, the SQL Server 2005 (9.x)SQL Server 2005 (9.x) procedure for backing up full-text catalogs remains applicable, except that pausing and resuming during the backup operation are no longer necessary. 如需詳細資訊,請參閱備份並還原全文檢索目錄For more information, see Backing Up and Restoring Full-Text Catalogs.

中繼資料Metadata

SQL ServerSQL Server 包含備份與還原記錄資料表,以便用來為每個伺服器執行個體進行追蹤備份和還原活動。includes backup and restore history tables that track the backup and restore activity for each server instance. 當執行還原時,也會修改備份記錄資料表。When a restore is performed, the backup history tables are also modified. 如需這些資料表的資訊,請參閱備份記錄與標頭資訊For information on these tables, see Backup History and Header Information.

REPLACE 選項影響REPLACE Option Impact

REPLACE 不應經常使用,而且只應在審慎考量之後使用。REPLACE should be used rarely and only after careful consideration. 還原通常可以防止意外將資料庫覆寫成不同資料庫。Restore normally prevents accidentally overwriting a database with a different database. 如果 RESTORE 陳述式中指定的資料庫已經存在於目前伺服器,而且指定的資料庫系列 GUID 與備份組中記錄的資料庫系列 GUID 不同,將不會還原資料庫。If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. 這是重要的防護措施。This is an important safeguard.

REPLACE 選項會覆寫還原通常會執行的數項重要安全檢查。The REPLACE option overrides several important safety checks that restore normally performs. 會覆寫的檢查如下:The overridden checks are as follows:

  • 使用從其他資料庫建立的備份來還原現有資料庫。Restoring over an existing database with a backup taken of another database.

    使用 REPLACE 選項,即使指定的資料庫名稱與備份組中所記錄的資料庫名稱不同,還原仍可讓您以備份組中的任何資料庫覆寫現有的資料庫。With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. 這可能會導致意外將資料庫覆寫成不同資料庫。This can result in accidentally overwriting a database by a different database.

  • 使用完整或大量記錄復原模式來還原資料庫,而這兩種模式都未取得結尾記錄備份也未使用 STOPAT 選項。Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

    使用 REPLACE 選項,您可能會遺失已認可的記錄,因為最近寫入的記錄尚未被備份。With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

  • 覆寫現有的檔案。Overwriting existing files.

    例如,作業失誤可能導致覆寫到錯誤的檔案類型 (例如 .xls 檔案),或覆寫到其他資料庫 (目前不在線上) 正在使用的檔案。For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. 如果覆寫了現有檔案,即使還原的資料庫是完整的,仍有可能遺失任意資料。Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

重做還原Redoing a Restore

您不可能恢復還原的效果;不過,您可以針對個別檔案重新開始,以消除資料複製和向前復原的效果。Undoing the effects of a restore is not possible; however, you can negate the effects of the data copy and roll forward by starting over on a per-file basis. 若要重新開始,請還原所需要的檔案,再重新執行向前復原。To start over, restore the desired file and perform the roll forward again. 例如,如果您不慎還原太多記錄備份,超出您想要的停止點,您就必須重新開始這個順序。For example, if you accidentally restored too many log backups and overshot your intended stopping point, you would have to restart the sequence.

您可以還原受影響之檔案的整個內容來中止和重新開始還原順序。A restore sequence can be aborted and restarted by restoring the entire contents of the affected files.

將資料庫還原為資料庫快照集Reverting a Database to a Database Snapshot

「還原資料庫作業」 (使用 DATABASE_SNAPSHOT 選項來指定) 會藉由將整個來源資料庫還原至資料庫快照集的時間,也就是使用在所指定資料庫快照集中維護的時間點資料來覆寫來源資料庫,讓整個來源資料庫回到過去的時間。A revert database operation (specified using the DATABASE_SNAPSHOT option) takes a full source database back in time by reverting it to the time of a database snapshot, that is, overwriting the source database with data from the point in time maintained in the specified database snapshot. 目前能存在的快照集只限於您要還原的目標快照集。Only the snapshot to which you are reverting can currently exist. 之後,還原作業會重建記錄檔 (因此,您無法稍後再將還原的資料庫向前復原到發生使用者錯誤的那個時間點)。The revert operation then rebuilds the log (therefore, you cannot later roll forward a reverted database to the point of user error).

您只會失去建立快照集之後的資料庫更新資料。Data loss is confined to updates to the database since the snapshot's creation. 還原資料庫的中繼資料與建立快照集時的中繼資料相同。The metadata of a reverted database is the same as the metadata at the time of snapshot creation. 不過,還原為快照集會卸除所有全文檢索目錄。However, reverting to a snapshot drops all the full-text catalogs.

從資料庫快照集還原的用途,並不在於復原媒體。Reverting from a database snapshot is not intended for media recovery. 資料庫快照集不像正規的備份組,它是不完整的資料庫檔案副本。Unlike a regular backup set, the database snapshot is an incomplete copy of the database files. 如果資料庫或資料庫快照集損毀,可能就無法從快照集還原。If either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. 此外,即使可以還原,但是在損毀的情況下還原也不太可能會更正問題。Furthermore, even when possible, reverting in the event of corruption is unlikely to correct the problem.

還原限制Restrictions on Reverting

在下列狀況下,不支援還原:Reverting is unsupported under the following conditions:

  • 來源資料庫包含任何唯讀或壓縮的檔案群組。The source database contains any read-only or compressed filegroups.
  • 建立快照集時原本處於線上狀態的所有檔案,現在都變成離線狀態。Any files are offline that were online when the snapshot was created.
  • 目前已經有一個以上的資料庫快照集。More than one snapshot of the database currently exists.

如需詳細資訊,請參閱將資料庫還原成資料庫快照集For more information, see Revert a Database to a Database Snapshot.

SecuritySecurity

備份作業可以選擇性地指定媒體集的密碼及 (或) 備份組的密碼。A backup operation may optionally specify passwords for a media set, a backup set, or both. 當在媒體集或備份組上定義密碼時,您必須在 RESTORE 陳述式中,指定一個或多個正確的密碼。When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. 這些密碼可以防止他人利用 SQL ServerSQL Server 工具,在未獲授權的情況下,在媒體上執行還原作業及附加備份組。These passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL ServerSQL Server tools. 不過,BACKUP 陳述式的 FORMAT 選項可以覆寫密碼所保護的媒體。However, password-protected media can be overwritten by the BACKUP statement's FORMAT option.

重要

這個密碼所提供的保護很弱。The protection provided by this password is weak. 這是為了防止已獲授權或未獲授權的使用者使用 SQL ServerSQL Server 工具進行不正確的還原。It is intended to prevent an incorrect restore using SQL ServerSQL Server tools by authorized or unauthorized users. 它無法防止透過其他方式或以取代密碼的方式來讀取備份資料。It does not prevent the reading of the backup data by other means or the replacement of the password. 這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.保護備份的最佳做法是將備份磁帶存放在安全位置,或備份至受適當存取控制清單 (ACL) 保護的磁碟檔案中。The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). ACL 應該設在備份建立所在的根目錄下。The ACLs should be set on the directory root under which backups are created.

注意

如需使用 Microsoft Azure Blob 儲存體來進行 SQL Server 備份及還原的特定資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For information specific to SQL Server backup and restore with the Microsoft Azure Blob storage, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

權限Permissions

如果還原的資料庫不存在,使用者必須有 CREATE DATABASE 權限,才能執行 RESTORE。If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. 如果資料庫存在,RESTORE 權限預設為 系統管理員 (sysadmin)資料庫建立者 (dbcreator) 固定伺服器角色的成員以及資料庫的擁有者 (dbo) (對 FROM DATABASE_SNAPSHOT 選項而言,資料庫一律存在)。If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE 權限提供給伺服器隨時可以取得其成員資格資訊的角色。RESTORE permissions are given to roles in which membership information is always readily available to the server. 由於資料庫必須是可存取且未損毀,才能夠檢查固定資料庫角色成員資格,但執行 RESTORE 時未必如此;因此, db_owner 固定資料庫角色的成員並沒有 RESTORE 權限。Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

範例Examples

所有範例都假設已執行完整資料庫備份。All the examples assume that a full database backup has been performed.

RESTORE 範例包括:The RESTORE examples include the following:

注意

如需其他範例,請參閱還原和復原概觀中所列的還原做法主題。For additional examples, see the restore how-to topics that are listed in Restore and Recovery Overview.

A.A. 還原完整資料庫Restoring a full database

下列範例會從 AdventureWorksBackups 邏輯備份裝置還原完整的資料庫備份。The following example restores a full database backup from the AdventureWorksBackups logical backup device. 如需有關建立這個裝置的範例,請參閱備份裝置For an example of creating this device, see Backup Devices.

RESTORE DATABASE AdventureWorks2012
  FROM AdventureWorks2012Backups;

注意

如果是使用完整或大量記錄復原模式的資料庫,在大部分情況下,SQL ServerSQL Server 都會要求您先備份記錄結尾,再還原資料庫。For a database using the full or bulk-logged recovery model, SQL ServerSQL Server requires in most cases that you back up the tail of the log before restoring the database. 如需詳細資訊,請參閱結尾記錄備份For more information, see Tail-Log Backups.

[範例頂端][Top of examples]

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

下列範例會還原完整資料庫備份,接著再從包含這兩種備份 Z:\SQLServerBackups\AdventureWorks2012.bak 備份裝置進行差異備份。The following example restores a full database backup followed by a differential backup from the Z:\SQLServerBackups\AdventureWorks2012.bak backup device, which contains both backups. 將進行還原的完整備份是裝置上的第六個備份組 (FILE = 6),而差異資料庫備份是裝置上的第九個備份組 (FILE = 9)。The full database backup to be restored is the sixth backup set on the device (FILE = 6), and the differential database backup is the ninth backup set on the device (FILE = 9). 只要差異備份一完成復原,資料庫就完成復原。As soon as the differential backup is recovered, the database is recovered.

RESTORE DATABASE AdventureWorks2012
    FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
    WITH FILE = 6
      NORECOVERY;
RESTORE DATABASE AdventureWorks2012
    FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
    WITH FILE = 9
      RECOVERY;

[範例頂端][Top of examples]

C.C. 使用 RESTART 語法還原資料庫Restoring a database using RESTART syntax

下列範例會利用 RESTART 選項來重新啟動因伺服器斷電而中斷的 RESTORE 作業。The following example uses the RESTART option to restart a RESTORE operation interrupted by a server power failure.

-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorksBackups;
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorksBackups WITH RESTART;

[範例頂端][Top of examples]

D.D. 還原資料庫和移動檔案Restoring a database and move files

下列範例會還原完整的資料庫和交易記錄,並將還原的資料庫移至 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data 目錄。The following example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data directory.

RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorksBackups
    WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
    FROM AdventureWorksBackups
    WITH RECOVERY;

[範例頂端][Top of examples]

E.E. 使用 BACKUP 和 RESTORE 複製資料庫Copying a database using BACKUP and RESTORE

下列範例使用 BACKUPRESTORE 陳述式建立 AdventureWorks2012AdventureWorks2012 資料庫的副本。The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks2012AdventureWorks2012 database. MOVE 陳述式會使資料和記錄檔還原到指定的位置。The MOVE statement causes the data and log file to be restored to the specified locations. RESTORE FILELISTONLY 陳述式是用來決定資料庫中所要還原的檔案數目及名稱。The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. 新資料庫複本的名稱是 TestDBThe new copy of the database is named TestDB. 如需詳細資訊,請參閱 RESTORE FILELISTONLYFor more information, see RESTORE FILELISTONLY.

BACKUP DATABASE AdventureWorks2012
    TO AdventureWorksBackups ;

RESTORE FILELISTONLY
    FROM AdventureWorksBackups ;

RESTORE DATABASE TestDB
    FROM AdventureWorksBackups
    WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
    MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

[範例頂端][Top of examples]

F.F. 使用 STOPAT 還原至時間點Restoring to a point-in-time using STOPAT

下列範例會將資料庫還原至 12:00 AM April 15, 2020 時的狀態,並顯示含有多個記錄備份的還原作業。The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple log backups. 在備份裝置 AdventureWorksBackups上,要還原的完整資料庫備份是裝置上的第三個備份組 (FILE = 3),第一個記錄備份是第四個備份組 (FILE = 4),而第二個記錄備份是第五個備份組 (FILE = 5)。On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorksBackups
    WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks2012
    FROM AdventureWorksBackups
    WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks2012
    FROM AdventureWorksBackups
    WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;

[範例頂端][Top of examples]

G.G. 將交易記錄還原到標記Restoring the transaction log to a mark

下列範例會將交易記錄還原到名為 ListPriceUpdate的標示交易中之標示。The following example restores the transaction log to the mark in the marked transaction named ListPriceUpdate.

USE AdventureWorks2012
GO
BEGIN TRANSACTION ListPriceUpdate
    WITH MARK 'UPDATE Product list prices';
GO

UPDATE Production.Product
    SET ListPrice = ListPrice * 1.10
    WHERE ProductNumber LIKE 'BK-%';
GO

COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database
-- and log backups are taken.
-- An error occurs in the database.
USE master;
GO

RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE = 3, NORECOVERY;
GO

RESTORE LOG AdventureWorks2012
  FROM AdventureWorksBackups
    WITH FILE = 4,
    RECOVERY,
    STOPATMARK = 'UPDATE Product list prices';

[範例頂端][Top of examples]

H.H. 使用 TAPE 語法進行還原Restoring using TAPE syntax

下列範例會從 TAPE 備份裝置還原完整的資料庫備份。The following example restores a full database backup from a TAPE backup device.

RESTORE DATABASE AdventureWorks2012
    FROM TAPE = '\\.\tape0';

[範例頂端][Top of examples]

I.I. 使用 FILE 與 FILEGROUP 語法進行還原Restoring using FILE and FILEGROUP syntax

下列範例會還原名為 MyDatabase 的資料庫,此資料庫擁有兩個檔案,一個次要檔案群組和一個交易記錄,The following example restores a database named MyDatabase that has two files, one secondary filegroup, and one transaction log. 而且使用完整復原模式。The database uses the full recovery model.

資料庫備份是在名為 MyDatabaseBackups 的邏輯備份裝置上,媒體集中的第九個備份組。The database backup is the ninth backup set in the media set on a logical backup device named MyDatabaseBackups. 接下來是三個記錄備份,它們分別位於 10 裝置的後三個備份組中 (1112MyDatabaseBackups),並且利用 WITH NORECOVERY 而還原。Next, three log backups, which are in the next three backup sets (10, 11, and 12) on the MyDatabaseBackups device, are restored by using WITH NORECOVERY. 在還原最後一個記錄備份之後,資料庫就可以復原。After restoring the last log backup, the database is recovered.

注意

復原是以個別步驟執行,以降低過早復原的可能性,也就是在所有記錄備份都復原之前就進行復原。Recovery is performed as a separate step to reduce the possibility of you recovering too early, before all of the log backups have been restored.

請注意,在 RESTORE DATABASE 中有兩種 FILE 選項類型。In the RESTORE DATABASE, notice that there are two types of FILE options. 在備份裝置名稱之前的 FILE 選項指定要從備份組還原之資料庫檔案的邏輯檔案名稱;例如,FILE = 'MyDatabase_data_1'The FILE options preceding the backup device name specify the logical file names of the database files that are to be restored from the backup set; for example, FILE = 'MyDatabase_data_1'. 這個備份組並非媒體集中的第一個資料庫備份;因此,它在媒體集中的位置是利用 FILE 子句中的 WITH 選項 FILE=9 指出。This backup set is not the first database backup in the media set; therefore, its position in the media set is indicated by using the FILE option in the WITH clause, FILE=9.

RESTORE DATABASE MyDatabase
    FILE = 'MyDatabase_data_1',
    FILE = 'MyDatabase_data_2',
    FILEGROUP = 'new_customers'
    FROM MyDatabaseBackups
    WITH
      FILE = 9,
      NORECOVERY;
GO  
-- Restore the log backups
RESTORE LOG MyDatabase
    FROM MyDatabaseBackups
    WITH FILE = 10,
      NORECOVERY;
GO
RESTORE LOG MyDatabase
    FROM MyDatabaseBackups
    WITH FILE = 11,
      NORECOVERY;
GO
RESTORE LOG MyDatabase
    FROM MyDatabaseBackups
    WITH FILE = 12,
      NORECOVERY;
GO
--Recover the database
RESTORE DATABASE MyDatabase WITH RECOVERY;
GO

[範例頂端][Top of examples]

J.J. 從資料庫快照集還原Reverting from a database snapshot

下列範例會將資料庫還原到某個資料庫快照集。The following example reverts a database to a database snapshot. 這個範例假設資料庫目前只有一個快照集。The example assumes that only one snapshot currently exists on the database. 如需如何建立這個資料庫快照集的範例,請參閱建立資料庫快照集For an example of how to create this database snapshot, see Create a Database Snapshot.

注意

還原為快照集會卸除所有全文檢索目錄。Reverting to a snapshot drops all the full-text catalogs.

USE master;
RESTORE DATABASE AdventureWorks2012 FROM DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';
GO

如需詳細資訊,請參閱將資料庫還原成資料庫快照集For more information, see Revert a Database to a Database Snapshot.

[範例頂端][Top of examples]

K.K. 從 Microsoft Azure Blob 儲存體服務進行還原Restoring from the Microsoft Azure Blob storage service

下面三個範例涉及使用 Microsoft Azure Blob 儲存體服務。The three examples below involve the use of the Microsoft Azure storage service. 儲存體帳戶名稱為 mystorageaccountThe storage Account name is mystorageaccount. 資料檔案的容器名為 myfirstcontainerThe container for data files is called myfirstcontainer. 備份檔案的容器名為 mysecondcontainerThe container for backup files is called mysecondcontainer. 已針對每個容器建立具有讀取、寫入、刪除及列出權限的預存存取原則。A stored access policy has been created with read, write, delete, and list, rights for each container. 已使用與此「預存存取原則」關聯的「共用存取簽章」建立 SQL Server 認證。SQL Server credentials were created using Shared Access Signatures that are associated with the Stored Access Policies. 如需使用 Microsoft Azure Blob 儲存體來進行 SQL Server 備份及還原的特定資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For information specific to SQL Server backup and restore with the Microsoft Azure Blob storage, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

K1.從 Microsoft Azure 儲存體服務還原完整資料庫備份 Sales 的完整資料庫備份 (位於 mysecondcontainer) 會還原至 myfirstcontainerK1. Restore a full database backup from the Microsoft Azure storage service A full database backup, located at mysecondcontainer, of Sales will be restored to myfirstcontainer. Sales 目前不存在於伺服器上。Sales does not currently exist on the server.

RESTORE DATABASE Sales
  FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
  WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
  MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
  STATS = 10;

K2.從 Microsoft Azure 儲存體服務將完整資料庫備份還原至本機存放區 Sales 的完整資料庫備份 (位於 mysecondcontainer) 會還原至本機存放區。K2. Restore a full database backup from the Microsoft Azure storage service to local storage A full database backup, located at mysecondcontainer, of Sales will be restored to local storage. Sales 目前不存在於伺服器上。Sales does not currently exist on the server.

RESTORE DATABASE Sales
  FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
  WITH MOVE 'Sales_Data1' to 'H:\DATA\Sales_Data1.mdf',
  MOVE 'Sales_log' to 'O:\LOG\Sales_log.ldf',
  STATS = 10;

K3.將完整資料庫備份從本機儲存體還原至 Microsoft Azure 儲存體服務K3. Restore a full database backup from local storage to the Microsoft Azure storage service

RESTORE DATABASE Sales
  FROM DISK = 'E:\BAK\Sales.bak'
  WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
  MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
  STATS = 10;

[範例頂端][Top of examples]

詳細資訊More information

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 Blob 儲存體帳戶從完整資料庫備份還原整個資料庫 (完整還原)。This command enables you to restore an entire database from a full database backup (a complete restore) from Azure Blob Storage account.

如需其他支援的 RESTORE命令,請參閱:For other supported RESTORE commands, see:

重要

若要從 Azure SQL Database 受控執行個體自動備份進行還原,請參閱 SQL Database 還原To restore from Azure SQL Database managed instance automatic backups, see SQL Database Restore.

語法Syntax

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
 FROM URL = { 'physical_device_name' | @physical_device_name_var } [ ,...n ]
[;]

引數Arguments

DATABASEDATABASE

指定目標資料庫。Specifies the target database.

FROM URLFROM URL

指定放置於 URL而且將用於還原作業的一或多個備份裝置。Specifies one or more backup devices placed on URLs that will be used for the restore operation. URL 格式可用於從 Microsoft Azure 儲存體服務還原備份。The URL format is used for restoring backups from the Microsoft Azure storage service.

重要

為了在從 URL 還原時能從多部裝置還原,您必須使用共用存取簽章 (SAS) 權杖。In order to restore from multiple devices when restoring from 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.

一般備註General Remarks

先決條件是,您需要使用符合 Blob 儲存體帳戶 URL 的名稱,以及放置為祕密的共用存取簽章來建立認證。As a prerequisite, you need to create a credential with the name that matches the blob storage account url, and Shared Access Signature placed as secret. RESTORE 命令會使用 Blob 儲存體 URL 查閱認證,以尋找讀取備份裝置所需要的資訊。RESTORE command will lookup credential using the blob storage url to find the information required to read the backup device.

還原作業非同步 - 即使用戶端連線中斷,還是會繼續還原。RESTORE operation is asynchronous - the restore continues even if client connection breaks. 如果您的連線中斷,可以檢查 sys.dm_operation_status 檢視以取得還原作業 (以及建立和卸除資料庫) 的狀態。If your connection is dropped, you can check sys.dm_operation_status view for the status of a restore operation (as well as for CREATE and DROP database).

會設定/覆寫下列資料庫選項,且稍後無法變更:The following database options are set/overridden and cannot be changed later:

  • NEW_BROKER (如果未在 .bak 檔案中啟用訊息代理程式)NEW_BROKER (if broker is not enabled in .bak file)
  • ENABLE_BROKER (如果未在 .bak 檔案中啟用訊息代理程式)ENABLE_BROKER (if broker is not enabled in .bak file)
  • AUTO_CLOSE=OFF (如果 .bak 檔案中的資料庫具有 AUTO_CLOSE=ON)AUTO_CLOSE=OFF (if a database in .bak file has AUTO_CLOSE=ON)
  • RECOVERY FULL (如果 .bak 檔案中的資料庫具有 SIMPLE 或 BULK_LOGGED 復原模式)RECOVERY FULL (if a database in .bak file has SIMPLE or BULK_LOGGED recovery mode)
  • 新增記憶體最佳化檔案群組,並呼叫 XTP,如果它不在原始 .bak 檔案中的話。Memory optimized filegroup is added and called XTP if it was not in the source .bak file. 任何現有的記憶體最佳化檔案群組都已重新命名為 XTPAny existing memory optimized filegroup is renamed to XTP
  • SINGLE_USER 和 RESTRICTED_USER 選項轉換為 MULTI_USERSINGLE_USER and RESTRICTED_USER options are converted to MULTI_USER

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

以下是適用的限制:These limitations apply:

  • 無法還原包含多個備份組的 .BAK 檔案。.BAK files containing multiple backup sets cannot be restored.
  • 無法還原包含多個記錄檔的 .BAK 檔案。.BAK files containing multiple log files cannot be restored.
  • 如果 .bak 包含 FILESTREAM 資料,則還原將會失敗。Restore will fail if .bak contains FILESTREAM data.
  • 無法將包含具有使用中記憶體內部物件之資料庫的備份還原至一般目的受控執行個體。Backups containing databases that have active In-memory objects cannot be restored to a General Purpose managed instance.
  • 目前無法還原唯讀模式之資料庫的備份。Backups containing databases in read-only mode cannot currently be restored. 即將移除這項限制。This limitation will be removed soon.

如需詳細資訊,請參閱受控執行個體For more information, see managed instance

還原加密資料庫Restoring an Encrypted Database

若要還原加密的資料庫,您必須能夠存取之前用來加密資料庫的憑證或非對稱金鑰。To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 如果沒有該憑證或非對稱金鑰,就無法還原資料庫。Without the certificate or asymmetric key, the database cannot be restored. 因此,只要需要備份,就必須保留用來加密資料庫加密金鑰的憑證。As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. 如需詳細資訊,請參閱 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

權限Permissions

使用者必須有 CREATE DATABASE 權限,才能執行 RESTORE。The user must have CREATE DATABASE permissions to be able to execute RESTORE.

CREATE LOGIN mylogin WITH PASSWORD = 'Very Strong Pwd123!';
GRANT CREATE ANY DATABASE TO [mylogin];

RESTORE 權限提供給伺服器隨時可以取得其成員資格資訊的角色。RESTORE permissions are given to roles in which membership information is always readily available to the server. 由於資料庫必須是可存取且未損毀,才能夠檢查固定資料庫角色成員資格,但執行 RESTORE 時未必如此;因此, db_owner 固定資料庫角色的成員並沒有 RESTORE 權限。Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

範例Examples

下列範例會從 URL 還原僅限複製資料庫備份,包括建立認證。The following examples restore a copy only database backup from URL, including the creation of a credential.

A.A. 從四個備份裝置還原資料庫Restore database from four backup devices


-- Create credential
CREATE CREDENTIAL [https://mybackups.blob.core.windows.net/wide-world-importers]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = 'sv=2017-11-09&ss=bq&srt=sco&sp=rl&se=2022-06-19T22:41:07Z&st=2018-06-01T14:41:07Z&spr=https&sig=s7wddcf0w%3D';
GO
-- Restore database
RESTORE DATABASE WideWorldImportersStandard
FROM URL = N'https://mybackups.blob.core.windows.net/wide-world-importers/00-WideWorldImporters-Standard.bak',
URL = N'https://mybackups.blob.core.windows.net/wide-world-importers/01-WideWorldImporters-Standard.bak',
URL = N'https://mybackups.blob.core.windows.net/wide-world-importers/02-WideWorldImporters-Standard.bak',
URL = N'https://mybackups.blob.core.windows.net/wide-world-importers/03-WideWorldImporters-Standard.bak'

如果資料庫已經存在,則會顯示下列錯誤:The following error is shown if the database already exists:

Msg 1801, Level 16, State 1, Line 9
Database 'WideWorldImportersStandard' already exists. Choose a different database name.

B.B. 還原透過變數所指定的資料庫Restore database specified via variable

DECLARE @db_name sysname = 'WideWorldImportersStandard';
DECLARE @url nvarchar(400) = N'https://mybackups.blob.core.windows.net/wide-world-importers/WideWorldImporters-Standard.bak';

RESTORE DATABASE @db_name
FROM URL = @url

C.C. 追蹤還原陳述式的進度Track progress of restore statement

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 = 'RESTORE DATABASE'

注意

此檢視可能會顯示兩個還原要求。This view will probably show two restore requests. 一個是由用戶端傳送的原始 RESTORE 陳述式,另一個則是即便用戶端連線失敗,也會持續執行的背景 RESTORE 陳述式。One is original RESTORE statement sent by the client, and the another one is background RESTORE statement that is executing even if the client connection fails.

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

 

分析平台系統Analytics Platform System

平行處理資料倉儲Parallel Data Warehouse使用者資料庫從資料庫備份還原到平行處理資料倉儲Parallel Data WarehouseRestores a 平行處理資料倉儲Parallel Data Warehouse user database from a database backup to a 平行處理資料倉儲Parallel Data Warehouse appliance. 資料庫會從 平行處理資料倉儲Parallel Data WarehouseBACKUP DATABASE - Analytics Platform System 命令先前所建立的備份還原。The database is restored from a backup that was previously created by the 平行處理資料倉儲Parallel Data Warehouse BACKUP DATABASE - Analytics Platform System command. 您可以使用備份和還原作業來建置災害復原計劃,或將資料庫從一個應用裝置移至另一個應用裝置。Use the backup and restore operations to build a disaster recovery plan, or to move databases from one appliance to another.

注意

還原 master 時,會包括還原應用裝置登入資訊。Restoring master includes restoring appliance login information. 若要還原 master,請使用設定管理員工具中的還原 master 資料庫頁面。To restore master, use the Restore the master Database page in the Configuration Manager tool. 能夠存取控制節點的系統管理員將可執行這項作業。An administrator with access to the Control node can perform this operation. 如需有關平行處理資料倉儲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


-- Restore the master database
-- Use the Configuration Manager tool.

Restore a full user database backup.
RESTORE DATABASE database_name
    FROM DISK = '\\UNC_path\full_backup_directory'
[;]

--Restore a full user database backup and then a differential backup.
RESTORE DATABASE database_name
    FROM DISK = '\\UNC_path\differential_backup_directory'
    WITH [ ( ] BASE = '\\UNC_path\full_backup_directory' [ ) ]
[;]

--Restore header information for a full or differential user database backup.
RESTORE HEADERONLY
    FROM DISK = '\\UNC_path\backup_directory'
[;]

引數Arguments

RESTORE DATABASE database_name 指定將使用者資料庫還原至名為 database_name 的資料庫。RESTORE DATABASE database_name Specifies to restore a user database to a database called database_name. 所還原資料庫的名稱可以與所備份來源資料庫的名稱不同。The restored database can have a different name than the source database that was backed up. database_name 不可以是目的地應用裝置上已經存在的資料庫。database_name cannot already exist as a database on the destination appliance. 如需有關所允許資料庫名稱的更多詳細資料,請參閱平行處理資料倉儲產品文件Parallel Data Warehouse product documentation中的<Object Naming Rules>(物件命名規則)。For more details on permitted database names, see "Object Naming Rules" in the 平行處理資料倉儲產品文件Parallel Data Warehouse product documentation.

還原使用者資料庫時,會將完整資料庫備份及視需要將差異備份還原至應用裝置。Restoring a user database restores a full database backup and then optionally restores a differential backup to the appliance. 還原使用者資料庫時,會包括還原資料庫使用者和資料庫角色。A restore of a user database includes restoring database users, and database roles.

FROM DISK = '\\UNC_path\backup_directory' 平行處理資料倉儲Parallel Data Warehouse 還原備份檔案的來源網路路徑及目錄。FROM DISK = '\\UNC_path\backup_directory' The network path and directory from which 平行處理資料倉儲Parallel Data Warehouse will restore the backup files. 例如 FROM DISK = '\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup'。For example, FROM DISK = '\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup'.

_directory 指定包含完整或差異備份的目錄名稱。backup_directory Specifies the name of a directory that contains the full or differential backup. 例如,您可以在完整或差異備份上執行 RESTORE HEADERONLY 作業。For example, you can perform a RESTORE HEADERONLY operation on a full or differential backup.

full_backup_directory 指定包含完整備份的目錄名稱。full_backup_directory Specifies the name of a directory that contains the full backup.

differential_backup_directory 指定包含差異備份的目錄名稱。differential_backup_directory Specifies the name of the directory that contains the differential backup.

  • 備份目錄的路徑必須已經存在,且必須以完整通用命名慣例 (UNC) 路徑的形式指定。The path and backup directory must already exist and must be specified as a fully qualified universal naming convention (UNC) path.
  • 備份目錄的路徑不可以是本機路徑,也不可以是任何平行處理資料倉儲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.

RESTORE HEADERONLY 指定只傳回一個使用者資料庫備份的標頭資訊。RESTORE HEADERONLY Specifies to return only the header information for one user database backup. 標頭包含備份的文字描述和備份名稱等。Among other fields, the header includes the text description of the backup, and the backup name. 備份名稱不一定要與儲存備份檔案的目錄同名。The backup name does not need to be the same as the name of the directory that stores the backup files.

RESTORE HEADERONLY 結果會比照 SQL ServerSQL Server RESTORE HEADERONLY 結果的模式。RESTORE HEADERONLY results are patterned after the SQL ServerSQL Server RESTORE HEADERONLY results. 此結果有超過 50 個資料行,這些資料行不會完全供平行處理資料倉儲Parallel Data Warehouse使用。The result has over 50 columns, which are not all used by 平行處理資料倉儲Parallel Data Warehouse. 如需 SQL ServerSQL Server RESTORE HEADERONLY 結果中資料行的描述,請參閱 RESTORE HEADERONLYFor a description of the columns in the SQL ServerSQL Server RESTORE HEADERONLY results, see RESTORE HEADERONLY.

權限Permissions

需要 CREATE ANY DATABASE 權限。Requires the CREATE ANY DATABASE permission.

需要具備備份目錄之存取和讀取權限的 Windows 帳戶。Requires a Windows account that has permission to access and read from the backup directory. 您也必須將 Windows 帳戶名稱和密碼儲存在平行處理資料倉儲Parallel Data Warehouse中。You must also store the Windows account name and password in 平行處理資料倉儲Parallel Data Warehouse.

錯誤處理Error Handling

在下列情況下,RESTORE DATABASE 命令會造成錯誤:The RESTORE DATABASE command results in errors under the following conditions:

  • 目標應用裝置上已經有要還原之資料庫的名稱。The name of the database to restore already exists on the target appliance. 若要避免此問題,請選擇一個唯一的資料庫名稱,或在執行還原之前,先卸除現有的資料庫。To avoid this, choose a unique database name, or drop the existing database before running the restore.
  • 備份目錄中有一組無效的備份檔案。There is an invalid set of backup files in the backup directory.
  • 登入權限不足以還原資料庫。The login permissions are not sufficient to restore a database.
  • 平行處理資料倉儲Parallel Data Warehouse沒有備份檔案所在網路位置的正確權限。does not have the correct permissions to the network location where the backup files are located.
  • 備份目錄的網路位置不存在或無法使用。The network location for the backup directory does not exist, or is not available.
  • 計算節點或控制節點上的磁碟空間不足。There is insufficient disk space on the Compute nodes or Control node. 平行處理資料倉儲Parallel Data Warehouse在起始還原之前,未先確認應用裝置上是否有足夠的磁碟空間。does not confirm that sufficient disk space exists on the appliance before initiating the restore. 因此,在執行 RESTORE DATABASE 陳述式時,可能產生磁碟空間不足錯誤。Therefore, it is possible to generate an out-of-disk-space error while running the RESTORE DATABASE statement. 發生磁碟空間不足問題時,平行處理資料倉儲Parallel Data Warehouse會復原還原作業。When insufficient disk space occurs, 平行處理資料倉儲Parallel Data Warehouse rolls back the restore.
  • 作為資料庫還原目的地之目標應用裝置的計算節點數目,比備份資料庫時之來源應用裝置的計算節點數目少。The target appliance to which the database is being restored has fewer Compute nodes than the source appliance from which the database was backed up.
  • 從交易內嘗試執行資料庫還原。The database restore is attempted from within a transaction.

一般備註General Remarks

平行處理資料倉儲Parallel Data Warehouse會追蹤資料庫還原是否成功。tracks the success of database restores. 在還原差異資料庫備份之前,平行處理資料倉儲Parallel Data Warehouse會確認完整資料庫還原已成功完成。Before restoring a differential database backup, 平行處理資料倉儲Parallel Data Warehouse verifies the full database restore finished successfully.

還原之後,使用者資料庫的資料庫相容性層級相會是 120。After a restore, the user database will have database compatibility level 120. 這適用於所有資料庫,不論其原始相容性層級為何。This is true for all databases regardless of their original compatibility level.

還原至具有較多計算節點的設備Restoring to an Appliance With a Larger Number of Compute Nodes

將資料庫從較小型應用裝置還原至較大型應用裝置之後,請執行 DBCC SHRINKLOG (Azure SQL 資料倉儲),因為轉散發會增加交易記錄。Run DBCC SHRINKLOG (Azure SQL Data Warehouse) after restoring a database from a smaller to larger appliance since redistribution will increase transaction log.

將備份還原至具有較多計算節點的應用裝置時,會讓已配置的資料庫大小依計算節點數目比例成長。Restoring a backup to an appliance with a larger number of Compute nodes grows the allocated database size in proportion to the number of Compute nodes.

例如,將 60 GB 資料庫從具有 2 個節點的應用裝置 (每個節點 30 GB) 還原至具有 6 個節點的應用裝置時,平行處理資料倉儲Parallel Data Warehouse會在具有 6 個節點的應用裝置上建立一個 180 GB 的資料庫 (6 個節點,每個節點 30 GB)。For example, when restoring a 60 GB database from a 2-node appliance (30 GB per node) to a 6-node appliance, 平行處理資料倉儲Parallel Data Warehouse creates a 180 GB database (6 nodes with 30 GB per node) on the 6-node appliance. 平行處理資料倉儲Parallel Data Warehouse一開始會將資料庫還原至 2 個節點以符合來源組態,然後會將資料轉散發至全部 6 個節點。initially restores the database to 2 nodes to match the source configuration, and then redistributes the data to all 6 nodes.

在轉散發之後,與較小型的來源應用裝置相比,每個計算節點將會包含較少的實際資料和較多的可用空間。After the redistribution each Compute node will contain less actual data and more free space than each Compute node on the smaller source appliance. 請使用額外的空間將更多資料新增至資料庫。Use the additional space to add more data to the database. 如果所還原資料庫大於您所需的大小,您可以使用 ALTER DATABASE - PDW 來縮減資料庫檔案大小。If the restored database size is larger than you need, you can use ALTER DATABASE - PDW to shrink the database file sizes.

限制事項Limitations and Restrictions

就這些限制而言,來源應用裝置是您從中建立資料庫備份的應用裝置,而目標應用裝置則是將作為資料庫還原目的地的應用裝置。For these limitations and restrictions, the source appliance is the appliance from which the database backup was created, and the target appliance is the appliance to which the database will be restored.

  • 還原資料庫並不會自動重建統計資料。Restoring a database does not automatically rebuild statistics.
  • 在任何指定時間,在應用裝置上都只能執行一個 RESTORE DATABASE 或 BACKUP DATABASE 陳述式。Only one RESTORE DATABASE or BACKUP DATABASE statement can be running on the appliance at any given time. 如果同時提交多個備份和還原陳述式,應用裝置就會將它們排入佇列,然後一次處理一個陳述式。If multiple backup and restore statements are submitted concurrently, the appliance will put them into a queue and process them one at a time.
  • 您只能將資料庫備份還原至所擁有計算節點數目等於或大於來源應用裝置的平行處理資料倉儲Parallel Data Warehouse目標應用裝置。You can only restore a database backup to a 平行處理資料倉儲Parallel Data Warehouse target appliance that has the same number or more Compute nodes than the source appliance. 目標應用裝置所擁有的計算節點數目不可以比來源應用裝置少。The target appliance cannot have fewer Compute nodes than the source appliance.
  • 您無法將在具有 SQL Server 2012 PDW 硬體之應用裝置上建立的備份,還原至具有 SQL Server 2008 R2 硬體的應用裝置。You cannot restore a backup that was created on an appliance that has SQL Server 2012 PDW hardware to an appliance that has SQL Server 2008 R2 hardware. 即使原先購買應用裝置時是配備 SQL Server 2008 R2 PDW 硬體,而現在執行的是 SQL Server 2012 PDW 軟體,也適用此限制。This holds true even if the appliance was originally purchased with the SQL Server 2008 R2 PDW hardware and is now running SQL Server 2012 PDW software.

鎖定Locking

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

範例Examples

A.A. 簡單的 RESTORE 範例Simple RESTORE examples

下列範例會將完整資料庫備份還原至 SalesInvoices2013 資料庫。The following example restores a full backup to the SalesInvoices2013 database. 備份檔案會儲存在 \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full 目錄中。The backup files are stored in the \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full directory. SalesInvoices2013 資料庫不可以是目標應用裝置上已經存在的資料庫,否則此命令會因發生錯誤而失敗。The SalesInvoices2013 database cannot already exist on the target appliance or this command will fail with an error.

RESTORE DATABASE SalesInvoices2013
FROM DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full';

B.B. 還原完整和差異備份Restore a full and differential backup

下列範例會先將完整備份還原至 SalesInvoices2013 資料庫,然後再將差異備份還原至該資料庫The following example restores a full, and then a differential backup to the SalesInvoices2013 database

還原資料庫完整備份時,會從儲存在 '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full' 目錄中的完整備份還原。The full backup of the database is restored from the full backup which is stored in the '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full' directory. 若還原順利完成,差異備份會還原至 SalesInvoices2013 資料庫。差異備份儲存在 '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff' 目錄中。If the restore completes successfully, the differential backup is restored to the SalesInvoices2013 database.The differential backup is stored in the '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff' directory.

RESTORE DATABASE SalesInvoices2013
    FROM DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff'
    WITH BASE = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full'
[;]

C.C. 還原備份標頭Restoring the backup header

此範例會還原資料庫備份 '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full' 的標頭資訊。This example restores the header information for database backup '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full' . 此命令會為 Invoices2013Full 備份產生一列資訊。The command results in one row of information for the Invoices2013Full backup.

RESTORE HEADERONLY
    FROM DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full'
[;]

您可以使用此標頭資訊來檢查備份的內容,或在嘗試還原備份之前,先確認目標還原應用裝置與來源備份應用裝置相容。You can use the header information to check the contents of a backup, or to make sure the target restoration appliance is compatible with the source backup appliance before attempting to restore the backup.

另請參閱See Also