How to Change MS SQL Database Recovery Model?

This article describes how to change the recovery model of an MS SQL database through SQL Server Management Studio and Transact-SQL. A recovery model database property tells SQL Server how you want to log the transaction logs. Mainly there are three recovery models in MS SQL Server.

  • Simple
  • Full
  • Bulk-logged
In most cases, the MS SQL database uses a complete and straightforward recovery model. However, anytime the MS SQL  database can be switched to another recovery model. Before you switch from a full or bulk-logged recovery model, taking a backup of the transaction logs is recommended. Here are the recommendations you should refer to before you switch to other recovery models.

Change the recovery model Using SQL Server Management Studio

1. Connect to MS SQL Server through SQL Server Management Studio. You can refer to the following tutorials to connect to MS SQL Server.

2. Once connected to Microsoft SQL Server Database Engine, click the SQL server name on the left-hand side in the Object Explorer pane to expand the server tree.

3. Expand Databases and right-click on the database whose recovery model you wish to change. Right-click the database, and then click Properties which opens the Database Properties dialog box.

Database Properties
4. Under Select a page pane, click Options. You will see the current recovery model displayed under the Recovery model list box.

Set Recovery Model
5. You can change the recovery model from the drop-down and Click OK.

Change the recovery model using Transact-SQL

1. Connect to the Database Engine.

New Query Window
2. From the Standard bar, click New Query.

3. Type the following statement in the query window and click Execute

USE Your-database-name ;


Set Recovery Model Query

Was this answer helpful?

« Back