在簡單復原模式下還原資料庫備份 (Transact-SQL)Restore a Database Backup Under the Simple Recovery Model (Transact-SQL)

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

本主題說明如何還原完整資料庫備份。This topic explains how to restore a full database backup.

重要

負責還原完整資料庫備份的系統管理員,必須是目前唯一正在使用即將還原之資料庫的人員。The system administrator restoring the full database backup must be the only person currently using the database to be restored.

必要條件和建議Prerequisites and Recommendations

  • 若要還原加密的資料庫,您必須能夠存取之前用來加密資料庫的憑證或非對稱金鑰。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.

  • 基於安全性的理由,建議您不要附加或還原來源不明或來源不受信任的資料庫。For security purposes, we recommend that you do not attach or restore databases from unknown or untrusted sources. 這種資料庫可能包含惡意程式碼,因此可能執行非預期的 Transact-SQLTransact-SQL 程式碼,或是修改結構描述或實體資料庫結構而造成錯誤。Such 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.

升級後的資料庫相容性層級Database Compatibility Level After Upgrade

tempdb模型msdb資源 資料庫的相容性層級在升級之後會設定為 SQL Server 2017SQL Server 2017 的相容性層級。The compatibility levels of the tempdb, model, msdb and Resource databases are set to the compatibility level of SQL Server 2017SQL Server 2017 after upgrade. 主要 系統資料庫會保留升級前的相容性層級,除非層級小於 100。The master system database retains the compatibility level it had before upgrade, unless that level was less than 100. 如果升級前 主要 的相容性層級小於 100,升級後會設定為 100。If the compatibility level of master was less than 100 before upgrade, it is set to 100 after upgrade.

如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。If the compatibility level of a user database was 100 or higher before upgrade, it remains the same after upgrade. 如果升級前的相容性層級為 90,則在升級後的資料庫中,相容性層級會設定為 100 (這是 SQL Server 2017SQL Server 2017所支援的最低相容性層級)。If the compatibility level was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017SQL Server 2017.

注意

新的使用者資料庫會繼承 模型 資料庫的相容性層級。New user databases will inherit the compatibility level of the model database.

程序Procedures

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

  1. 執行 RESTORE DATABASE 陳述式以還原完整資料庫備份,請指定:Execute the RESTORE DATABASE statement to restore the full database backup, specifying:

    • 所要還原的資料庫名稱。The name of the database to restore.

    • 將要還原完整資料庫備份的備份裝置。The backup device from where the full database backup is restored.

    • 如果完整資料庫備份還原以後需要套用交易記錄或差異資料庫備份,則請指定 NORECOVERY 子句。The NORECOVERY clause if you have a transaction log or differential database backup to apply after restoring the full database backup.

    重要

    若要還原加密的資料庫,您必須能夠存取之前用來加密資料庫的憑證或非對稱金鑰。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.

  2. (選擇性) 指定:Optionally, specify:

    • 識別備份裝置上欲還原之備份組的 FILE 子句。The FILE clause to identify the backup set on the backup device to restore.

注意

如果您將舊版資料庫還原至 SQL Server 2017SQL Server 2017,資料庫會自動升級。If 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.

範例Example

DescriptionDescription

此範例會從磁帶還原 AdventureWorks2012AdventureWorks2012 完整資料庫備份。This example restores the AdventureWorks2012AdventureWorks2012 full database backup from tape.

範例Example

USE master;  
GO  
RESTORE DATABASE AdventureWorks2012  
   FROM TAPE = '\\.\Tape0';  
GO  

另請參閱See Also

完整資料庫還原 (完整復原模式) Complete Database Restores (Full Recovery Model)
完整資料庫還原 (簡單復原模式) Complete Database Restores (Simple Recovery Model)
完整資料庫備份 (SQL Server) Full Database Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
備份記錄與標頭資訊 (SQL Server) Backup History and Header Information (SQL Server)
重建系統資料庫Rebuild System Databases