查看或更改数据库的恢复模式 (SQL Server)View or Change the Recovery Model of a Database (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 StudioSQL Server Management StudioTransact-SQLTransact-SQL查看或更改数据库。This topic describes how to view or change the database by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

“恢复模式” 是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)进行备份,以及可以使用哪些类型的还原操作。A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. 有三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢复模式。Three recovery models exist: simple, full, and bulk-logged. 通常,数据库使用完整恢复模式或简单恢复模式。Typically, a database uses the full recovery model or simple recovery model. 数据库可以随时切换为其他恢复模式。A database can be switched to another recovery model at any time. model 数据库将设置新数据库的默认恢复模式。The model database sets the default recovery model of new databases.

有关更深入的说明,请参阅恢复模式For a more in depth explanation, see recovery models.

开始之前Before you begin

  • 在从完整恢复模式或大容量日志恢复模式 before switching from the full recovery or bulk-logged recovery model.Back up the transaction log before switching from the full recovery or bulk-logged recovery model.

  • 时点恢复在大容量日志模式下不可能进行。Point-in-time recovery is not possible with bulk-logged model. 在需要事务日志还原的大容量日志恢复模式下运行事务可能会导致事务丢失数据。Running transactions under the bulk-logged recovery model that require a transaction log restore can exposed them to data loss. 若要在灾难恢复方案中最大程度地恢复数据,则仅在以下条件下切换到大容量日志恢复模式:To maximize data recoverability in a disaster-recovery scenario, switch to the bulk-logged recovery model only under the following conditions:

    • 数据库中当前不允许存在用户。Users are currently not allowed in the database.

    • 在大容量处理过程中进行的所有修改均不依靠日志备份就可恢复;例如,通过重新运行大容量处理。All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

    如果满足这两个条件,在大容量日志恢复模式下还原备份的事务日志时将不会丢失任何数据。If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model..

注意!Note! 如果在大容量操作过程中切换到完整恢复模式,则大容量操作日志记录将从最小日志记录更改为最大日志记录,反之亦然。If you switch to the full recovery model during a bulk operation, bulk operations logging changes from minimal logging to full logging, and vice versa.

所需的权限Required permissions

需要对数据库拥有 ALTER 权限。Requires ALTER permission on the database.

使用 SQL Server Management StudioUsing SQL Server Management Studio

查看或更改恢复模式To view or change the recovery model

  1. 连接到相应的 SQL Server 数据库引擎SQL Server Database Engine实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。After connecting to the appropriate instance of the SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展开 “数据库” ,然后根据数据库的不同,选择用户数据库,或展开 “系统数据库” ,再选择系统数据库。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 右键单击该数据库,再单击“属性” ,这将打开“数据库属性” 对话框。Right-click the database, and then click Properties, which opens the Database Properties dialog box.

  4. “选择页” 窗格中,单击 “选项”In the Select a page pane, click Options.

  5. 当前恢复模式显示在 “恢复模式” 列表框中。The current recovery model is displayed in the Recovery model list box.

  6. 也可以从列表中选择不同的模式来更改恢复模式。Optionally, to change the recovery model select a different model list. 可以选择“完整” 、“大容量日志” 或“简单” 。The choices are Full, Bulk-logged, or Simple.

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

使用 Transact-SQLUsing Transact-SQL

查看恢复模式To view the recovery model

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何对 sys.databases 目录视图执行查询以了解 模型 数据库的恢复模式。This example shows how to query the sys.databases catalog view to learn the recovery model of the model database.

SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model' ;  
GO  
  

更改恢复模式To change the recovery model

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 model ALTER DATABASE FULL 语句的 SET RECOVERY 选项将 数据库中的恢复模式更改为This example shows how to change the recovery model in the model database to FULL by using the SET RECOVERY option of the ALTER DATABASE statement.

USE [master] ;  
ALTER DATABASE [model] SET RECOVERY FULL ;  

建议:在更改恢复模式后Recommendations: After you change the recovery model

  • 在完整恢复模式和大容量日志恢复模式之间切换后After switching between the full and bulk-logged recovery models

    • 完成大容量操作之后,立即切换回完整恢复模式。After completing the bulk operations, immediately switch back to full recovery mode.

    • 在从大容量日志恢复模式切换回完整恢复模式后,备份日志。After switching from the bulk-logged recovery model back to the full recovery model, back up the log.

      注意: 您的备份策略保持不变:继续执行定期数据库备份、日志备份和差异备份。NOTE: Your backup strategy remains the same: continue performing periodic database, log, and differential backups.

  • 从简单恢复模式切换之后After switching from the simple recovery model

    • 切换到完整恢复模式或大容量日志恢复模式之后,立即进行完整数据库备份或差异数据库备份以启动日志链。Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

      注意: 到完整恢复模式或大容量日志恢复模式的切换仅在第一个数据备份之后才生效。NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

    • 计划安排定期日志备份并相应地更新还原计划。Schedule regular log backups, and update your restore plan accordingly.

      重要说明!!!!IMPORTANT!!!! 备份日志!!Back up your logs!! 如果不经常备份日志,事务日志可能会扩展直到占满磁盘空间!If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space!

  • 切换到简单恢复模式之后After switching to the simple recovery model

    • 中断用于备份事务日志的所有计划作业。Discontinue any scheduled jobs for backing up the transaction log.

    • 确保定期执行数据库备份。Ensure periodic database backups are scheduled. 备份数据库对于保护数据和截断事务日志的不活动部分是基本操作。Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.

Related tasksRelated tasks

相关内容Related Content

另请参阅See Also

恢复模式 (SQL Server) Recovery Models (SQL Server)
事务日志 (SQL Server) The Transaction Log (SQL Server)
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
恢复模式 (SQL Server)Recovery Models (SQL Server)