SQL Server – Different ways to check recovery model

Recovery Model (SQL Server)

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.


There are many ways to check recovery model of a database in SQL Server.  Below are few commonly used examples.

1. Using Management Studio

Right click on Database in Object Explorer > go to Properties dialog box > Options page >Recovery model



2. Using catalog view [sys.databases]

SELECT [DatabaseName]=name,
FROM sys.databases


3. Using Metadata function – DATABASEPROPERTYEX()

SELECT ‘AdventureWorks2012’ AS [Database Name],
AS [Recovery Model]



4. Using Object Explorer Details

Click on the Database Node in Object Explorer. In Object Explorer Details, you can find Recovery Model for all databases.




Hope this was helpful!!!

Leave a comment

1 Comment

  1. Thanks for sharing the relevant information with us…It is really very good..


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: