Tuesday, February 28, 2012

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' ;

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

To Change,
USE model;

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.

No comments:

Post a Comment