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 数据库
托管实例
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 rollforward. 这可用于下列情况:将文件添加到数据库时,日志备份包含了已写入的日志记录。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 和 NORECOVERY 的比较Comparison of RECOVERY and NORECOVERY

回滚由 RESTORE 语句通过 [ RECOVERY | NORECOVERY ] 选项控制:Rollback is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

  • NORECOVERY 指定不发生回滚。NORECOVERY specifies that rollback doesn't occur. 从而使前滚按顺序在下一条语句中继续进行。This allows rollforward 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 rollback should be performed after rollforward is completed for the current backup.

    恢复数据库要求要还原的整个数据集(“前滚集 ”)必须与数据库一致。Recovering the database requires that the entire set of data being restored (the rollforward set) is consistent with the database. 如果前滚集尚未前滚到与数据库保持一致的地步,并且指定了 RECOVERY,则 数据库引擎Database Engine 将发出错误。If the rollforward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the 数据库引擎Database Engine issues an error. 有关恢复过程的详细信息,请参阅还原和恢复概述 (SQL Server)For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

兼容性支持Compatibility Support

SQL ServerSQL Server 无法还原使用 SQL ServerSQL Server 的早期版本创建的 master、model 和 msdb 备份 。Backups of master, model and msdb that were created by using an earlier version of SQL ServerSQL Server cannot be restored by SQL ServerSQL Server.

备注

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 ServerSQL Server后,将自动升级该数据库。After you restore an earlier version 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 on 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 语句向服务器提供 DMK(使用服务主密钥 (SMK) 加密)的副本,即可拥有将来启用自动解密的选项。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.

只要操作系统支持数据库排序规则,就可以跨平台执行还原操作,即使这些平台使用不同的处理器类型也不例外。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.

还原数据库会清除要还原的数据库的计划缓存。Restoring a database clears the plan cache for the database being restored. 清除计划缓存将导致对所有后续执行计划进行重新编译,并可能导致查询性能暂时性地突然降低。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 的实例,然后再将数据库添加到可用性组。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.

SQL Server 大数据群集SQL Server Big Data Clusters

某些操作(包括配置服务器(实例级)设置或手动将数据库添加到可用性组)需要连接到 SQL Server 实例。Certain operations, including configuring server (instance level) settings, or manually adding a database to an availability group, require a connection to the SQL Server Instance. 某些操作(例如 sp_configureRESTORE DATABASE 或属于可用性组的数据库中的任何 DDL 命令)需要连接到 SQL Server 实例。Operations like sp_configure, RESTORE DATABASE, or any DDL command in a database belonging to an availability group require a connection to the SQL Server instance. 默认情况下,大数据群集不包含用于连接到实例的终结点。By default, a big data cluster does not include an endpoint that enables a connection to the instance. 必须手动公开此终结点。You must expose this endpoint manually.

有关说明,请参阅连接到主副本上的数据库For instructions, see Connect to databases on the primary replica.

元数据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 rollforward by starting over on a per-file basis. 若要重新开始,请再次还原所需的文件并执行前滚。To start over, restore the desired file and perform the rollforward 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 rollforward 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 权限默认授予 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.

示例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 AMApril 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 来还原在 11 设备上的后续三个备份集(12MyDatabaseBackupsWITH 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. 有关恢复过程的详细信息,请参阅还原和恢复概述 (SQL Server)For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

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 存储服务的使用。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 存储服务还原完整数据库备份 K1. Restore a full database backup from the Microsoft Azure storage service
位于 mysecondcontainerSales 的完整数据库备份将还原到 myfirstcontainerA 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 Server) Restore and Recovery Overview (SQL Server)
SQL Server 数据库的备份和还原 Back Up and Restore of SQL Server Databases
系统数据库的备份和还原 (SQL Server) Back Up and Restore of System Databases (SQL Server)
Restore a Database Backup Using SSMS Restore a Database Backup Using SSMS
备份和还原全文目录和索引 Back Up and Restore Full-Text Catalogs and Indexes
备份和还原复制的数据库 Back Up and Restore Replicated Databases
BACKUP BACKUP
媒体集、媒体簇和备份集 Media Sets, Media Families, and Backup Sets
RESTORE REWINDONLY RESTORE REWINDONLY
RESTORE VERIFYONLY RESTORE VERIFYONLY
RESTORE FILELISTONLY (Transact-SQL) RESTORE FILELISTONLY (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL) RESTORE HEADERONLY (Transact-SQL)
备份历史记录和标头信息Backup History and Header Information

SQL ServerSQL Server *SQL 数据库
托管实例*
* SQL Database
managed instance *
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL 数据库托管实例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 数据库托管实例自动备份还原,请参阅 SQL 数据库还原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使用 Powershell 简化在 Azure 存储空间中使用共享访问签名 (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 look up credentials using the blob storage url to find the information required to read the backup device.

RESTORE 操作是异步的,即使客户端连接中断,还原也会继续运行。RESTORE operation is asynchronous - the restore continues even if client connection breaks. 如果连接断开,可检查 sys.dm_operation_status 视图,查看还原操作的状态(以及查看 CREATE 和 DROP 数据库)。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)
  • 如果源 .bak 文件中不存在内存优化文件组,则会添加一个,并将其命名为 XTP。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 数据库托管实例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 数据库
托管实例
SQL Database
managed instance
* Analytics
Platform System (PDW) *
* Analytics
Platform System (PDW) *

 

分析平台系统Analytics Platform System

并行数据仓库Parallel Data Warehouse用户数据库从数据库备份还原到并行数据仓库Parallel Data Warehouse设备。Restores a 并行数据仓库Parallel Data Warehouse user database from a database backup to a 并行数据仓库Parallel Data Warehouse appliance. 数据库会从以前通过 并行数据仓库Parallel Data Warehouse BACKUP 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,请使用 Configuration Manager 工具中的还原 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中的“备份和还原”。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中的“对象命名规则”。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'.

backup_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 数据库。If the restore completes successfully, the differential backup is restored to the SalesInvoices2013 database. 差异备份存储在 \\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff 目录中。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

BACKUP DATABASE - Analytics Platform SystemBACKUP DATABASE - Analytics Platform System