将数据库还原到新位置 (SQL Server)Restore a Database to a New Location (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍如何使用 SQL Server Management Studio (SSMS) 或 SQL ServerSQL ServerSQL ServerSQL Server 中将 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 ServerSQL Server 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 ServerSQL Server,将自动升级该数据库。If you restore a SQL Server 2005 (9.x)SQL Server 2005 (9.x) or higher database to SQL ServerSQL Server, 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.

SecuritySecurity

出于安全性考虑,我们建议您不要从未知或不信任的源附加或还原数据库。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 权限默认授予 sysadmindbcreator 固定服务器角色成员以及该数据库的所有者 (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 数据库引擎SQL Server Database Engine实例,然后在对象资源管理器中,单击服务器名称以展开服务器树。Connect to the appropriate instance of the SQL Server 数据库引擎SQL 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 <backup_device> 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_name*  

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

[ WITH  

 {  

    [ **RECOVERY** | NORECOVERY ]  

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

    [ , ] 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 描述Description
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 的一个新数据库,该数据库包括两个文件: AdventureWorks2012AdventureWorks2012_Data 和 AdventureWorks2012AdventureWorks2012_Log。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 <database_name> 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