Two ways to Change the Recovery Model

-->

Microsoft SQL server offers a facility to manage transaction log of SQL server that is called Recovery Model. It is a database property and helps in deciding whether transaction log will require or not for the database. There are three types of recovery models: Simple, Full & Bulk logged. In general, database uses simple or full recovery model.

In this section, we will discuss about how to see & change the recovery model of a SQL server database.

There are two ways to see and change the recovery model

1. By SQL server management studio.
2. By Transact SQL

By SQL server management studio: To do this, follow below steps

• Connect with appropriate instance of SQL server database engine
• Go to the object explorer & click on the server name
• Expand the server tree
• Now expand the database and choose your desired database (user or system database)
• Right click on the desired database and select properties
• Click on the options in display panel
• See current Recovery Model from the recovery model dialog box
• To Change recovery model, select from available options simple, full or bulk-logged

By Transact SQL: To do this, run the below commands

To See,

SELECT name, recovery_master_desc

FROM sys.databases

WHERE name = 'master' ;

GO

Note: Above query will show the recovery model for the master database.

To Change,

USE model;

ALTER DATABASE model SET RECOVERY FULL;

Note: Above command will set the full recovery model for model database.

Note: First method is application for all version of SQL server but second method is application for SQL server 2012 only.