在简单恢复模式下还原数据库备份 (Transact-SQL)Restore a Database Backup Under the Simple Recovery Model (Transact-SQL)

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

本主题说明如何还原完整数据库备份。This topic explains how to restore a full database backup.

重要

还原完整数据库备份的系统管理员必须是当前使用要还原的数据库的唯一人员。The system administrator restoring the full database backup must be the only person currently using the database to be restored.

前提条件和建议Prerequisites and Recommendations

  • 若要还原已加密的数据库,您必须有权访问用于对数据库进行加密的证书或非对称密钥。To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 如果没有证书或非对称密钥,数据库将无法还原。Without the certificate or asymmetric key, the database cannot be restored. 因此,只要需要该备份,就必须保留用于对数据库加密密钥进行加密的证书。As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. 有关详细信息,请参阅 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

  • 出于安全性考虑,我们建议您不要从未知或不信任的源附加或还原数据库。For security purposes, we recommend that you do not attach or restore databases from unknown or untrusted sources. 此类数据库可能包含恶意代码,这些代码可能会执行非预期的 Transact-SQLTransact-SQL 代码,或者通过修改架构或物理数据库结构导致错误。Such databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 使用来自未知源或不可信源的数据库前,请在非生产服务器上针对数据库运行 DBCC CHECKDB ,然后检查数据库中的代码,例如存储过程或其他用户定义代码。Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

升级后的数据库兼容级别Database Compatibility Level After Upgrade

升级后, tempdbmodelmsdbResource 数据库的兼容级别将设置为 SQL ServerSQL Server 的兼容级别。The compatibility levels of the tempdb, model, msdb and Resource databases are set to the compatibility level of SQL ServerSQL Server after upgrade. master 系统数据库保留它在升级之前的兼容级别,除非该级别小于 100。The master system database retains the compatibility level it had before upgrade, unless that level was less than 100. 如果 master 的兼容级别在升级前小于 100,升级后兼容级别将设置为 100。If the compatibility level of master was less than 100 before upgrade, it is set to 100 after upgrade.

如果升级前用户数据库的兼容级别为 100 或更高,升级后将保持相应级别。If the compatibility level of a user database was 100 or higher before upgrade, it remains the same after upgrade. 如果升级前兼容级别为 90,则在升级后的数据库中,兼容级别将设置为 100,该级别为 SQL ServerSQL Server支持的最低兼容级别。If the compatibility level was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL ServerSQL Server.

备注

新的用户数据库将继承 model 数据库的兼容级别。New user databases will inherit the compatibility level of the model database.

过程Procedures

还原完整数据库备份To restore a full database backup

  1. 执行 RESTORE DATABASE 语句可以还原完整数据库备份,同时指定:Execute the RESTORE DATABASE statement to restore the full database backup, specifying:

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

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

    • NORECOVERY 子句,前提是在还原完整数据库备份之后,还要应用事务日志备份或差异数据库备份。The NORECOVERY clause if you have a transaction log or differential database backup to apply after restoring the full database backup.

    重要

    若要还原已加密的数据库,您必须有权访问用于对数据库进行加密的证书或非对称密钥。To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 如果没有证书或非对称密钥,数据库将无法还原。Without the certificate or asymmetric key, the database cannot be restored. 因此,只要需要该备份,就必须保留用于对数据库加密密钥进行加密的证书。As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. 有关详细信息,请参阅 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

  2. 可以选择指定:Optionally, specify:

    • FILE 子句,以此标识备份设备上要还原的备份集。The FILE clause to identify the backup set on the backup device to restore.

备注

如果将早期版本的数据库还原到 SQL ServerSQL Server,将自动升级该数据库。If 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 the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. 另请注意,如果将升级选项设置为“导入”,并且全文目录不可用,则会重新生成关联的全文索引。Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. 若要更改 upgrade_option 服务器属性的设置,请使用 sp_fulltext_serviceTo change the setting of the upgrade_option server property, use sp_fulltext_service.

示例Example

描述Description

以下示例从磁带中还原 AdventureWorks2012AdventureWorks2012 的完整数据库备份。This example restores the AdventureWorks2012AdventureWorks2012 full database backup from tape.

示例Example

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

另请参阅See Also

完整数据库还原(完整恢复模式) Complete Database Restores (Full Recovery Model)
完整数据库还原(简单恢复模式) Complete Database Restores (Simple Recovery Model)
完整数据库备份 (SQL Server) Full Database Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
备份历史记录和标头信息 (SQL Server) Backup History and Header Information (SQL Server)
重新生成系统数据库Rebuild System Databases