將資料庫還原到新位置 (SQL Server)Restore a Database to a New Location (SQL Server)

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

此主題描述如何使用 SQL Server Management Studio (SSMS) 或 SQL ServerSQL Server ,在 SQL Server 2017SQL Server 2017 中將 Transact-SQLTransact-SQL資料庫還原至新位置,並選擇性地重新命名資料庫。This topic describes how to restore a SQL ServerSQL Server database to a new location, and optionally rename the database, in SQL Server 2017SQL Server 2017 by using SQL Server Management Studio(SSMS) or Transact-SQLTransact-SQL. 您可以將資料庫移至新目錄路徑,或是在相同的伺服器執行個體或不同的伺服器執行個體上建立資料庫的複本。You can move a database to a new directory path or create a copy of a database on either the same server instance or a different server instance.

開始之前!Before you begin!

限制事項Limitations and restrictions

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

必要條件Prerequisites

  • 在完整或大量記錄復原模式下,您必須先備份使用中交易記錄,才能還原資料庫。Under the full or bulk-logged recovery model, before you can restore a database, you must back up the active transaction log. 如需詳細資訊,請參閱 備份交易記錄 (SQL Server)資料庫還原至新位置,並選擇性地重新命名資料庫。For more information, see Back Up a Transaction Log (SQL Server).

  • 若要還原加密資料庫, 您必須能夠存取用來加密資料庫的憑證或非對稱金鑰!To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database! 如果沒有憑證或非對稱金鑰,您就無法還原資料庫。Without that certificate or asymmetric key, you cannot restore the database. 只要您需要備份,就必須保留用來加密資料庫加密金鑰的憑證!You must retain that certificate used to encrypt the database encryption key for as long as you need the backup! 如需詳細資訊,請參閱 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

建議Recommendations

  • 如需移動資料庫的其他考量,請參閱 使用備份與還原複製資料庫For additional considerations for moving a database, see Copy Databases with Backup and Restore.

  • 如果您將 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 或更高版本的資料庫還原成 SQL Server 2017SQL Server 2017,資料庫會自動升級。If you restore a SQL Server 2005 (9.x)SQL Server 2005 (9.x) or higher 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.

安全性Security

基於安全性的理由,建議您不要附加或還原來源不明或來源不受信任的資料庫。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.

權限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 權限預設為 系統管理員dbcreator 固定伺服器角色的成員,以及資料庫的擁有者 (dbo)。If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

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.

將資料庫還原至新位置,並選擇性地使用 SSMS 重新命名資料庫Restore a database to a new location; optionally rename the database using SSMS

  1. 連接到適當的 SQL Server Database EngineSQL Server Database Engine執行個體,然後在 [物件總管] 中,按一下伺服器名稱以展開伺服器樹狀目錄。Connect to the appropriate instance of the SQL Server Database EngineSQL Server Database Engine, and then in Object Explorer, click the server name to expand the server tree.

  2. 以滑鼠右鍵按一下 [資料庫] ,然後按一下 [還原資料庫] 。Right-click Databases, and then click Restore Database. [還原資料庫] 對話方塊隨即開啟。The Restore Database dialog box opens.

  3. [一般] 頁面上,使用 [來源] 區段指定要還原之備份組的來源和位置。On the General page, use the Source section to specify the source and location of the backup sets to restore. 選取下列其中一個選項:Select one of the following options:

    • [資料庫備份]Database

      從下拉式清單中選取要還原的資料庫。Select the database to restore from the drop-down list. 此清單僅包含已根據 msdb 備份記錄而備份的資料庫。The list contains only databases that have been backed up according to the msdb backup history.

    注意: 如果備份是根據不同的伺服器建立的,目的地伺服器就沒有指定之資料庫的備份記錄資訊。NOTE: If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. 在此情況下,請選取 [裝置] ,以便手動指定要還原的檔案或裝置。In this case, select Device to manually specify the file or device to restore.

    1. [裝置]Device

      按一下瀏覽 ( ... ) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box. [備份媒體類型] 方塊中,選取列出的其中一種裝置類型。In the Backup media type box, select one of the listed device types. 若要選取 [備份媒體] 方塊中的一個或多個裝置,請按一下 [加入]To select one or more devices for the Backup media box, click Add.

      將您要的裝置加入 [備份媒體] 清單方塊後,按一下 [確定] 即可回到 [一般] 頁面。After you add the devices you want to the Backup media list box, click OK to return to the General page.

      [來源:裝置:資料庫] 清單方塊中,選取應該還原的資料庫名稱。In the Source: Device: Database list box, select the name of the database which should be restored.

      注意 :這份清單只能在選取 [裝置] 時使用。Note This list is only available when Device is selected. 只有在所選取裝置上有備份的資料庫才可供使用。Only databases that have backups on the selected device will be available.

  4. [目的地] 區段中,會將要還原之資料庫的名稱自動填入 [資料庫] 方塊。In the Destination section, the Database box is automatically populated with the name of the database to be restored. 若要變更資料庫的名稱,請在 [資料庫] 方塊中輸入新名稱。To change the name of the database, enter the new name in the Database box.

  5. [還原至] 方塊中,保留 [到上次建立的備份] 預設值,或按一下 [時間表] 存取 [備份時間表] 對話方塊,手動選取停止復原動作的時間點。In the Restore to box, leave the default as To the last backup taken or click on Timeline to access the Backup Timeline dialog box to manually select a point in time to stop the recovery action. 如需有關指定特定時間點的詳細資訊,請參閱< Backup Timeline >。See Backup Timeline for more information on designating a specific point in time.

  6. [要還原的備份組] 方格中,選取要還原的備份。In the Backup sets to restore grid, select the backups to restore. 這個方格會顯示指定位置可用的備份。This grid displays the backups available for the specified location. 依預設,會建議一個復原計畫。By default, a recovery plan is suggested. 若要覆寫建議的復原計畫,您可以變更方格中的選取項目。To override the suggested recovery plan, you can change the selections in the grid. 取消選取先前的備份時,會自動取消選取相依於先前備份還原的備份。Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected.

    如需 [要還原的備份組] 方格中各資料行的相關資訊,請參閱還原資料庫 (一般頁面)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page).

  7. 若要指定資料庫檔案的新位置,請選取 [檔案] 頁面,然後按一下 [將所有檔案重新放置到資料夾]To specify the new location of the database files, select the Files page, and then click Relocate all files to folder. 提供 [資料檔資料夾][記錄檔資料夾] 的新位置。Provide a new location for the Data file folder and Log file folder. 如需這個方格的詳細資訊,請參閱還原資料庫 (檔案頁面)For more information about this grid, see Restore Database (Files Page).

  8. [選項] 頁面上,依需要調整選項。On the Options page, adjust the options if you want. 如需這些選項的詳細資訊,請參閱還原資料庫 (選項頁面)For more information about these options, see Restore Database (Options Page).

將資料庫還原至新位置,並選擇性地使用 T-SQL 重新命名資料庫Restore database to a new location; optionally rename the database using T-SQL

  1. (選擇性) 在包含您想要還原之完整資料庫備份的備份組中,決定檔案的邏輯和實體名稱。Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore. 這個陳述式會傳回備份組內所自主資料庫和記錄檔清單。This statement returns a list of the database and log files contained in the backup set. 基本語法如下:The basic syntax is as follows:

    RESTORE FILELISTONLY FROM <備份裝置> WITH FILE = backup_set_file_numberRESTORE FILELISTONLY FROM <backup_device> WITH FILE = backup_set_file_number

    此處,backup_set_file_number 表示媒體集中的備份位置。Here, backup_set_file_number indicates the position of the backup in the media set. 您可以使用 RESTORE HEADERONLY 陳述式來取得備份組的位置。You can obtain the position of a backup set by using the RESTORE HEADERONLY statement. 如需詳細資訊,請參閱 RESTORE 引數 (Transact-SQL) 中的<指定備份組>。For more information, see "Specifying a Backup Set" in RESTORE Arguments (Transact-SQL).

    這個陳述式也支援一些 WITH 選項。This statement also supports a number of WITH options. 如需詳細資訊,請參閱 RESTORE FILELISTONLY (Transact-SQL)For more information, see RESTORE FILELISTONLY (Transact-SQL).

  2. 使用 RESTORE DATABASE 陳述式來還原完整資料庫備份。Use the RESTORE DATABASE statement to restore the full database backup. 根據預設,資料和記錄檔會還原到其原始位置。By default, data and log files are restored to their original locations. 若要重新放置資料庫,請使用 MOVE 選項來重新放置每個資料庫檔案,避免與現有的檔案發生衝突。To relocate a database, use the MOVE option to relocate each of the database files and to avoid collisions with existing files.

將資料庫還原至新位置和新名稱的基本 Transact-SQLTransact-SQL 語法為:The basic Transact-SQLTransact-SQL syntax for restoring the database to a new location and a new name is:

RESTORE DATABASE new_database_nameRESTORE DATABASE new_database_name

FROM backup_device [ ,...n ]FROM backup_device [ ,...n ]

[ WITH[ WITH

{{

[ RECOVERY | NORECOVERY ][ RECOVERY | NORECOVERY ]

[ , ] [ FILE ={ backup_set_file_number | @backup_set_file_number } ][ , ] [ FILE ={ backup_set_file_number | @backup_set_file_number } ]

[ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ][ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]

}}

;;

注意

準備要在不同的磁碟上重新放置資料庫時,您應該確認有足夠的可用空間,並且識別與現有檔案發生衝突的任何可能性。When preparing to relocate a database on a different disk, you should verify that sufficient space is available and identify any potential collisions with existing files. 這項作業包括使用 RESTORE VERIFYONLY 陳述式,其中指定您打算在 RESTORE DATABASE 陳述式中使用的相同 MOVE 參數。This involves using a RESTORE VERIFYONLY statement that specifies the same MOVE parameters that you plan to use in your RESTORE DATABASE statement.

下表針對將資料庫還原到新位置的作業,描述這個 RESTORE 陳述式的引數。The following table describes arguments of this RESTORE statement in terms of restoring a database to a new location. 如需這些引數的詳細資訊,請參閱 RESTORE (Transact-SQL)資料庫還原至新位置,並選擇性地重新命名資料庫。For more information about these arguments, see RESTORE (Transact-SQL).

new_database_namenew_database_name
資料庫的新名稱。The new name for the database.

注意

如果您要將資料庫還原至不同的伺服器執行個體,可以使用原始資料庫名稱而非新名稱。If you are restoring the database to a different server instance, you can use the original database name instead of a new name.

backup_device [ , ...n ]backup_device [ ,...n ]
指定一份逗號分隔清單,其中列出要從中還原資料庫備份的 1 到 64 個備份裝置。Specifies a comma-separated list of from 1 to 64 backup devices from which the database backup is to be restored. 您可以指定實體備份裝置,也可以指定對應的邏輯備份裝置 (如果已定義的話)。You can specify a physical backup device, or you can specify a corresponding logical backup device, if defined. 若要指定實體備份裝置,請使用 DISK 或 TAPE 選項:To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name{ DISK | TAPE } =physical_backup_device_name

如需詳細資訊,請參閱備份裝置 (SQL Server)For more information, see Backup Devices (SQL Server).

{ RECOVERY | NORECOVERY }{ RECOVERY | NORECOVERY }
如果資料庫使用完整復原模式,您可能必須在還原資料庫之後套用交易記錄備份。If the database uses the full recovery model, you might need to apply transaction log backups after you restore the database. 在此情況下,請指定 NORECOVERY 選項。In this case, specify the NORECOVERY option.

否則,請使用 RECOVERY 選項 (預設值)。Otherwise, use the RECOVERY option, which is the default.

FILE = { backup_set_file_number | @backup_set_file_number }FILE = { backup_set_file_number | @backup_set_file_number }
識別要還原的備份組。Identifies the backup set to be restored. 例如, backup_set_file_number1 ,表示備份媒體的第一個備份組; backup_set_file_number2 ,表示第二個備份組。For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. 您可以使用 RESTORE HEADERONLY 陳述式來取得備份組的 backup_set_file_numberYou can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.

沒有指定這個選項時,預設值是使用備份裝置上的第一個備份組。When this option is not specified, the default is to use the first backup set on the backup device.

如需詳細資訊,請參閱 RESTORE 引數 (Transact-SQL) 中的<指定備份組>。For more information, see "Specifying a Backup Set," in RESTORE Arguments (Transact-SQL).

MOVE ' logical_file_name_in_backup ' TO ' operating_system_file_name ' [ , ...n ]MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]
指定 logical_file_name_in_backup 所指定的資料或記錄檔要還原至 operating_system_file_name所指定的位置。Specifies that the data or log file specified by logical_file_name_in_backup is to be restored to the location specified by operating_system_file_name. 針對您想要從備份組還原到新位置的每一個邏輯檔案指定 MOVE 陳述式。Specify a MOVE statement for every logical file you want to restore from the backup set to a new location.

選項Option DescriptionDescription
logical_file_name_in_backuplogical_file_name_in_backup 指定備份組中資料或記錄檔的邏輯名稱。Specifies the logical name of a data or log file in the backup set. 備份組中資料或記錄檔的邏輯檔案名稱,會與當初建立備份組時資料庫中的邏輯名稱相符。The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.



注意:若要取得備份組中的邏輯檔清單,請使用 RESTORE FILELISTONLYNote: To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.
operating_system_file_nameoperating_system_file_name 針對 logical_file_name_in_backup所指定的檔案指定新的位置。Specifies a new location for the file specified by logical_file_name_in_backup. 檔案將還原至這個位置。The file will be restored to this location.

(選擇性) operating_system_file_name 會針對還原的檔案指定新的檔案名稱。Optionally, operating_system_file_name specifies a new file name for the restored file. 如果您要在相同的伺服器執行個體上建立現有資料庫的副本,這就是必要選項。This is necessary if you are creating a copy of an existing database on the same server instance.
nn 這是預留位置,表示您可以指定其他 MOVE 陳述式。Is a placeholder indicating that you can specify additional MOVE statements.

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

此範例會透過還原 MyAdvWorks 範例資料庫的備份 (其中包括兩個檔案: AdventureWorks2012AdventureWorks2012 _Data 和 AdventureWorks2012AdventureWorks2012_Log),建立名為 AdventureWorks2012AdventureWorks2012的新資料庫。This example creates a new database named MyAdvWorks by restoring a backup of the AdventureWorks2012AdventureWorks2012 sample database, which includes two files: AdventureWorks2012AdventureWorks2012_Data and AdventureWorks2012AdventureWorks2012_Log. 這個資料庫會使用簡單復原模式。This database uses the simple recovery model. AdventureWorks2012AdventureWorks2012 資料庫已經存在伺服器執行個體上,因此備份中的檔案都必須還原至新的位置。The AdventureWorks2012AdventureWorks2012 database already exists on the server instance, so the files in the backup must be restored to a new location. RESTORE FILELISTONLY 陳述式是用來決定資料庫中所要還原的檔案數目及名稱。The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. 此資料庫備份是備份裝置上的第一個備份組。The database backup is the first backup set on the backup device.

注意: 備份和還原交易記錄 (包括時間點還原) 的範例會使用從 AdventureWorks2012AdventureWorks2012 建立的 MyAdvWorks_FullRM 資料庫,就如同以下 MyAdvWorks 範例。NOTE: The examples of backing up and restoring the transaction log, including point-in-time restores, use the MyAdvWorks_FullRM database that is created from AdventureWorks2012AdventureWorks2012 just like the following MyAdvWorks example. 不過,您必須使用下列 Transact-SQLTransact-SQL 陳述式,將產生的 MyAdvWorks_FullRM 資料庫變更為使用完整復原模式:ALTER DATABASE <資料庫名稱> SET RECOVERY FULL。However, the resulting MyAdvWorks_FullRM database must be changed to use the full recovery model by using the following Transact-SQLTransact-SQL statement: ALTER DATABASE <database_name> SET RECOVERY FULL.

USE master;  
GO  
-- First determine the number and names of the files in the backup.  
-- AdventureWorks2012_Backup is the name of the backup device.  
RESTORE FILELISTONLY  
   FROM AdventureWorks2012_Backup;  
-- Restore the files for MyAdvWorks.  
RESTORE DATABASE MyAdvWorks  
   FROM AdventureWorks2012_Backup  
   WITH RECOVERY,  
   MOVE 'AdventureWorks2012_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf',   
   MOVE 'AdventureWorks2012_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf';  
GO  
  

如需如何建立 AdventureWorks2012AdventureWorks2012 資料庫之完整資料庫備份的範例,請參閱 建立完整資料庫備份 (SQL Server)資料庫還原至新位置,並選擇性地重新命名資料庫。For an example of how to create a full database backup of the AdventureWorks2012AdventureWorks2012 database, see Create a Full Database Backup (SQL Server).

Related tasksRelated tasks

另請參閱See also

在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料 (SQL Server) Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
使用備份與還原複製資料庫Copy Databases with Backup and Restore