在现有文件上还原文件和文件组 (SQL Server)Restore Files and Filegroups over Existing Files (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 ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中在现有文件上还原文件和文件组。This topic describes how to restore files and filegroups over existing files in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 还原文件和文件组的系统管理员必须是当前使用要还原数据库的唯一人员。The system administrator who is restoring the files and filegroups must be the only person currently using the database to be restored.

  • 不允许在显式或隐式事务中使用 RESTORE。RESTORE is not allowed in an explicit or implicit transaction.

  • 在完整恢复模式或大容量日志恢复模式下,必须先备份活动事务日志(称为日志尾部),然后才能还原文件。Under the full or bulk-logged recovery model, before you can restore files, you must back up the active transaction log (known as the tail of the log). 有关详细信息,请参阅 备份事务日志 (SQL Server)中还原文件和文件组。For more information, see Back Up a Transaction Log (SQL Server).

  • 若要还原已加密的数据库,您必须有权访问用于对数据库进行加密的证书或非对称密钥。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.

SecuritySecurity

权限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)(对于 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.

使用 SQL Server Management StudioUsing SQL Server Management Studio

在现有文件上还原文件和文件组To restore files and filegroups over existing files

  1. “对象资源管理器” 中,连接到 SQL Server 数据库引擎SQL Server Database Engine的实例,再依次展开该实例、 “数据库”In Object Explorer, connect to an instance of the SQL Server 数据库引擎SQL Server Database Engine, expand that instance, and then expand Databases.

  2. 右键单击所需数据库,指向“任务” ,再指向“还原” ,然后单击“文件和文件组” 。Right-click the database that you want, point to Tasks, point to Restore, and then click Files and Filegroups.

  3. “常规” 页上的 目标数据库 列表框中,输入要还原的数据库。On the General page, in the To database list box, enter the database to restore. 您可以输入新的数据库,也可以从下拉列表中选择现有的数据库。You can enter a new database or choose an existing database from the drop-down list. 该列表包含了服务器上除系统数据库 mastertempdb之外的所有数据库。The list includes all databases on the server, excluding the system databases master and tempdb.

  4. 若要指定要还原的备份集的源和位置,请单击以下选项之一:To specify the source and location of the backup sets to restore, click one of the following options:

    • 源数据库From database

      在列表框中输入数据库名称。Enter a database name in the list box. 此列表仅包含根据 msdb 备份历史记录已进行过备份的数据库。This list contains only databases that have been backed up according to the msdb backup history.

    • 源设备From device

      单击浏览按钮。Click the browse button. “指定备份设备” 对话框的 “备份介质类型” 列表框中,选择列出的设备类型之一。In the Specify backup devices dialog box, select one of the listed device types in the Backup media type list box. 若要为 “备份介质” 列表框选择一个或多个设备,请单击 “添加”To select one or more devices for the Backup media list box, click Add.

      将所需设备添加到 “备份介质” 列表框后,单击 “确定” 返回到 “常规” 页。After you add the devices you want to the Backup media list box, click OK to return to the General page.

  5. “选择用于还原的备份集” 网格中,选择用于还原的备份。In the Select 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. 任何依赖于已取消选择备份的备份,也将被自动取消选择。Any backups that depend on a deselected backup are deselected automatically.

    列标题Column head Values
    还原Restore 选中的复选框指示要还原的备份集。The selected check boxes indicate the backup sets to be restored.
    名称Name 备份集的名称。The name of the backup set.
    文件类型File Type 指定备份中数据的类型:数据、日志,或 Filestream 数据 。Specifies the type of data in the backup: Data, Log, or Filestream Data. 包含在表中的数据备份在 “数据” 文件中。Data that is contained in tables is in Data files. 事务日志数据备份在 “日志” 文件中。Transaction log data is in Log files. 存储在文件系统上的二进制大型对象 (BLOB) 数据备份在 Filestream 数据 文件中。Binary large object (BLOB) data that is stored on the file system is in Filestream Data files.
    类型Type 执行的备份类型:“完整”、“差异”或“事务日志” 。The type of backup performed: Full, Differential, or Transaction Log.
    ServerServer 执行备份操作的数据库引擎实例的名称。The name of the Database-Engine instance that performed the backup operation.
    文件逻辑名称File Logical Name 文件的逻辑名称。The logical name of the file.
    “数据库”Database 备份操作中涉及的数据库的名称。The name of the database involved in the backup operation.
    开始日期Start Date 备份操作开始的日期和时间,按客户端的区域设置显示。The date and time when the backup operation began, presented in the regional setting of the client.
    完成日期Finish Date 备份操作完成的日期和时间,按客户端的区域设置显示。The date and time when the backup operation finished, presented in the regional setting of the client.
    大小Size 备份集的大小(字节)。The size of the backup set in bytes.
    用户名User Name 执行备份操作的用户的名称。The name of the user who performed the backup operation.
  6. “选择页” 窗格中,单击 “选项” 页。In the Select a page pane, click the Options page.

  7. 在“还原选项” 面板中,选择“覆盖现有数据库 (WITH REPLACE)” 。In the Restore options panel, select Overwrite the existing database (WITH REPLACE). 还原操作将会覆盖所有现有数据库及其相关文件,即使已存在同名的其他数据库或文件。The restore operation overwrites any existing databases and their related files, even if another database or file already exists with the same name.

  8. 单击“确定”。 Click OK.

使用 Transact-SQLUsing Transact-SQL

在现有文件上还原文件和文件组To restore files and filegroups over existing files

  1. 执行 RESTORE DATABASE 语句以还原文件和文件组备份,同时指定下列内容:Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:

    • 要还原的数据库的名称。The name of the database to restore.

    • 从中还原完整数据库备份的备份设备。The backup device from where the full database backup will be restored.

    • 每个要还原文件的 FILE 子句。The FILE clause for each file to restore.

    • 每个要还原文件组的 FILEGROUP 子句。The FILEGROUP clause for each filegroup to restore.

    • REPLACE 选项,用来指定可以在具有相同名称和位置的现有文件上还原每个文件。The REPLACE option to specify that each file can be restored over existing files of the same name and location.

      注意

      请慎重使用 REPLACE 选项。Use the REPLACE option cautiously. 有关更多信息,请参见 。For more information, see .

    • NORECOVERY 选项。The NORECOVERY option. 如果在创建备份之后没有对文件进行修改,则指定 RECOVERY 子句。If the files have not been modified after the backup was created, specify the RECOVERY clause.

  2. 如果在创建文件备份之后对文件进行了修改,则执行 RESTORE LOG 语句以应用事务日志备份,同时指定下列内容:If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:

    • 事务日志将应用到的数据库的名称。The name of the database to which the transaction log will be applied.

    • 要还原的事务日志备份的备份设备。The backup device from where the transaction log backup will be restored.

    • 如果在应用当前事务日志备份之后还要应用其他事务日志备份,则指定 NORECOVERY 子句;否则指定 RECOVERY 子句。The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

      事务日志备份(如果已应用)必须包含备份文件和文件组时的时间。The transaction log backups, if applied, must cover the time when the files and filegroups were backed up.

示例 (Transact-SQL)Example (Transact-SQL)

下面的示例将还原 MyNwind 数据库的文件和文件组,并替换任何具有相同名称的现有文件。The following example restores the files and filegroups for the MyNwind database, and replaces any existing files of the same name. 为了将数据库还原到当前时间,还将应用两个事务日志。Two transaction logs will also be applied to restore the database to the current time.

USE master;  
GO  
-- Restore the files and filesgroups for MyNwind.  
RESTORE DATABASE MyNwind  
   FILE = 'MyNwind_data_1',  
   FILEGROUP = 'new_customers',  
   FILE = 'MyNwind_data_2',  
   FILEGROUP = 'first_qtr_sales'  
   FROM MyNwind_1  
   WITH NORECOVERY,  
   REPLACE;  
GO  
-- Apply the first transaction log backup.  
RESTORE LOG MyNwind  
   FROM MyNwind_log1  
   WITH NORECOVERY;  
GO  
-- Apply the last transaction log backup.  
RESTORE LOG MyNwind  
   FROM MyNwind_log2  
   WITH RECOVERY;  
GO  

另请参阅See Also

Restore a Database Backup Using SSMS Restore a Database Backup Using SSMS
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
还原文件和文件组 (SQL Server) Restore Files and Filegroups (SQL Server)
通过备份和还原来复制数据库Copy Databases with Backup and Restore