How to Identify the Recovery Model of a Database in SQL Server

Posted April 4, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql1

SQL Server backup and restore operations are closely associated with recovery models. Recovery models are ways to manage how transactions are logged, whether or not the transaction log allows and requires backups, and what types of restore operations are possible. Recovery models helps us to bring back and preserve a database after any unexpected failure without any data loss.

We have three types of recovery models in SQL Server:

1. Simple
2. Full
3. Bulk Logged

It is important to know which recovery model a database is using. By default, every database is created under a Full recovery model. However, as per database, critically it may be changed.

Let us see different ways to find out the recovery model of a database in SQL Server.

Method 1: Find the model using graphical options.

1. Expand Databases in your object explorer within SSMS.
2. Right-click on your database, and click Properties.
3. Go to Options, and the Recovery model is displayed under Collation.

Recovery model in SQL Server

Method 2: Query the SQL Server metadata.

Querying SQL server metadata finds the sys.databases which stores information related to each database.

SELECT Db.name as 'Database Name', DB.recovery_model_desc as 'Recovery Model'
FROM   sys.databases as DB
WHERE  name = 'Pratice';

 

how to find sql server recovery model metadata query

Method 3: Find out the recovery model of every database.

This gives us the name and the recovery model of each database.

SELECT Db.name as 'Database Name', DB.recovery_model_desc as 'Recovery Model'
FROM   sys.databases as DB;

 

how to find sql server recovery model

Method 4: Find out the recovery model using SQL Server’s built-in function.

DATABASEPROPERTYEX functions can be used to find out the recovery model of a database in SQL Server.

SELECT DATABASEPROPERTYEX('Pratice', 'Recovery') as 'Recovery Model';

 

how to find sql sever recovery model method4

Pratice is the database name here, and Recovery is the property name.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.