How to Know the Recovery Model of Your Database

Recovery model plays an important role in SQL Server because they decide the nature of your transaction log file and the recovery strategy. SQL Server supports three types of recovery models: Simple, Bulk-Logged, and Full.These SQL Server recovery models have their own properties. User can also change the recovery model after creating the database.Let’s see brief information about them:

Simple Recovery model: If your database is in simple recovery model then following operations cannot be performed on database-

• Database Mirroring
• Log Shipping
• AlwaysOn feature
• Point-in-Time recovery

You can use simple recovery model when:

• There is no need for point-in-time recovery
• Data can be derived and easily recreated
• Data is static

Full Recovery Model: A user can choose the full recovery model:

• For point-in-time data recovery
• When data became very important and user cannot lose the data
• For the use of advanced features like log shipping, mirroring, AlwaysOn etc.

Bulk-Logged Model: A bulk-logged recoverymodel is used to preserve the transaction log records after full backup of the database.

• Data is critical, but user doesn’t want to log large bulk operations
• Bulk operations complete at different times versus normal processing

Different type of backups that you can run on the bulk-logged model:

• Copy-Only Backup
• Differential Backup
• Complete Backup
• Partial Backup
• File/Filegroup backup
• Transactional log backup

After the small overview on recovery model, here we will know about multiple ways for checking the recovery models of SQL Server database:

Step 1: Using SSMS (SQL Server Management Studio)

Go to Object Explorer and Right Click on your Database-> Go to Properties ->Options ->Recovery Model

Step2: Using catalog View

User can also check the recovery model type by the following query:

Select name, recovery_model_desc FROM sys.databases

GO

The result has two columns, first is name of the database and second is recovery model type as shown in the figure:

Step3: Using Metadata Function:

DATABASEPROPERTYEX() is a metadata function to find the recovery model of the database. This function returns only one database at a time. Here is the query:

SELECT DATABASEPROPERTYEX (‘DatabaseName’,’RECOVERY’) “Recovery Mode1”

GO

The output will be:

Step 4: Using sp_helpdb

The user can also use the stored procedure sp_helpdb to check the recovery model of the databases.

EXEC sp_helpdb

GO

The output contains several fields and in the Statussection, a user can easily find the recovery model type.

Step 5: Using Object Explorer

In the object explorer, click on the Databasesand press F7. The output will be like this:

So these are the basic options to find the recovery model type of the database. 

Final Words: We have seen the different reasons to choose the recovery models and all the steps to find the recovery model of the database. When a user switches one recovery model to another recovery model then, he/she should be aware of its important points. Every recovery model has its own features and all are essential so choose your recovery model very carefully to save your database.