View or Change the Recovery Model of a Database (SQL Server)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse.This topic describes how to view or change the database by using SQL Server Management Studio or Transact-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. The model database sets the default recovery model of new databases.

For a more in depth explanation of recovery models, see SQL Server Recovery Models provided by the folks at MSSQLTips!

Before you begin

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

Requires ALTER permission on the database.

Using SQL Server Management Studio

To view or change the recovery model

1. After connecting to the appropriate instance of the 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.

Using Transact-SQL

To view the recovery model

1. Connect 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. This example shows how to query the sys.databases catalog view to learn the recovery model of the model database.

tsql

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

To change the recovery model

1. Connect 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. 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.

tsql

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.
  
  


• 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.
  
  


• 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 tasks

• Create a Full Database Backup (SQL Server)


• Back Up a Transaction Log (SQL Server)


• Create a Job


• Disable or Enable a Job

Related Content

• Database Maintenance Plans (in SQL Server 2008 R2 Books Online)

See Also

Recovery Models (SQL Server)
The Transaction Log (SQL Server)
ALTER DATABASE (Transact-SQL)
sys.databases (Transact-SQL)
Recovery Models (SQL Server)

Source: http://docphy.com/technology/computers/software/view-change-recovery-model-database-sql-server.html