How to Change MS SQL Database Recovery Model?

This article describes how to to change recovery model of a 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, MS SQL database uses full and simple recovery model. However, MS SQL  database can be switched to another recovery model at any time. Before you switch from full or bulk-logged recovery model, it is recommended to take the backup the transaction logs. Here are the recommendations you should refer to before you switch to other recovery model.


Change the recovery model Using SQL Server Management Studio


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



  2. Once you are connected to Microsoft SQL Server Database Engine, at left hand side in Object Explorer pane, click the SQL server name 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 would see current recovery model displayed under Recovery model list box.

    Set Recovery Model

  5. You can change the recovery model from 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 following statement in query window and click Execute

    USE Your-database-name ;
    ALTER DATABASE Your-database-name SET RECOVERY FULL ;

    Set Recovery Model Query




  • 2 Users Found This Useful

Was this answer helpful?

Related Articles

Comparison of MS SQL Server 2016 Express Edition V/S. MS SQL Server 2016 Web Edition

Following is a brief comparison between MSSQL Server 2016 Express Edition and MSSQL Server 2016...

How to Backup MSSQL Database from SQL Server Management Studio?

To take MS SQL database backup, first you need to connect to MS SQL Server through SQL Server...

Fix :: Reporting Service Error After Upgrade From MS SQL server from 2008 to 2012

Error Message Throwing Microsoft.ReportingServices.Diagnostics.Utilities....

Fix : Server Unable to load user-specified certificate. The server will not accept a connection

You may encounter this error when you attempt to start the MSSQL service. This error occurs when...

How to fix an error "change password failed for login sa"?

Sometimes you receive following error while trying to reset MS SQL sa password via SQL Management...