How to change the recovery model on SQL Server

Last updated on April 11, 2025

A recovery model is a database property that determines when the transaction log is cleared.

The transaction log is a binary file associated with the database, typically with the .ldf extension. It records all transactions performed by users in the database. The transaction log serves two primary purposes:

Database recovery after failures. For instance, if a power outage occurs during a transaction, SQL Server will use the transaction log to fix incomplete transactions when the system restarts.
Incremental backups. Instead of always creating a full backup of a large database, you can backup only the changes recorded in the transaction log. During restore, SQL Server reconstructs the database using these incremental backups.

Different recovery models function as follows:

Simple
- The transaction log is cleared automatically.
- Transaction log backups are not available.
Full
- The transaction log is cleared only when a transaction log backup is performed.
- If transaction log backups are not performed, the log file will eventually grow to an unmanageable size.
- If you use the full recovery model, you must regularly perform transaction log backups.
Bulk-logged
- This is similar to the full recovery model but optimized for bulk operations.
- However, this does not support point-in-time recovery (you cannot specify the STOPAT option during restore).

This article explains how to change the recovery model of a SQL Server database using SQL Server Management Studio (SSMS) or T-SQL.

Steps to change recovery model

Method 1: using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to the server

- Launch SQL Server Management Studio and connect to the SQL Server instance hosting your database.

Connect-to-SQL-server-via-SSMS — Connect to SQL Server via SSMS

2. Locate the database

- In Object Explorer, expand the Databases node
- Right-click on the database you want to modify and select Properties

Database-propeties-SSMS — Open database properties

3. Navigate to Options

- In the Database Properties window, select the Options page from the left-hand menu

Database-propeties-options-SSMS — Database options

4. Change the recovery model

- Locate the Recovery model drop-down menu
- Choose the desired recovery model (Simple, Full or Bulk-Logged) from the drop-down menu

Select-recovery-model-SSMS — Select recovery model

5. Save changes

- Click OK to apply the changes

Method 2: using T-SQL

You can also change the recovery model using a simple T-SQL command.

Syntax:

ALTER DATABASE [DatabaseName] SET RECOVERY [Simple|Full|Bulk_Logged];

Example:

To change the recovery model of a database named MyDatabase to Full:

ALTER DATABASE [MyDatabase] SET RECOVERY FULL;

To change it to Simple:

ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE;

To change it to Bulk-Logged:

ALTER DATABASE [MyDatabase] SET RECOVERY BULK_LOGGED;

Verifying the recovery model

To verify the current recovery model of a database:

Using SSMS
- Right-click on the database, select Properties, and navigate to the Options page
- Check the value of the Recovery model drop-down menu

Using T-SQL

SELECT name AS DatabaseName, recovery_model_desc
FROM sys.databases
WHERE name = 'MyDatabase';

Changing the recovery model is a straightforward process, but it’s essential to understand its impact on your backup and restore strategy. Always plan carefully before making changes to ensure that data integrity and recovery requirements are met.

SqlBak Blog