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
- By SQL server management studio.
- 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.
