Blog Post

SQL Server – Different ways to check Recovery Model of a database


A Recovery Model is property of a database which control how transaction log is maintained. SQL Server supports SIMPLE, FULL and BULK-LOGGED recovery models.

There are multiple ways to check recovery model of a database in SQL Server.

1. Using SQL Server Management Studio:

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


2. Using Metadata function – DATABASEPROPERTYEX():

SELECT [RecoveryModel] DATABASEPROPERTYEX('SqlAndMe','Recovery')



Result Set:





3. Using catalog view – sys.databases:

SELECT [DatabaseName] name,

       [RecoveryModel] recovery_model_desc

FROM   sys.databases




Result Set:

DatabaseName         RecoveryModel

master               SIMPLE

tempdb               SIMPLE

model                FULL

msdb                 SIMPLE

Pubs                 SIMPLE

SqlAndMe             SIMPLE

AdventureWorks2012   SIMPLE

Northwind            SIMPLE

TestDB               SIMPLE

ProductCatalog       SIMPLE

ReportDemo           SIMPLE

ReportServer         FULL

ReportServerTempDB   SIMPLE


(13 row(s) affected)

Using sys.databases catalog view is easier as it returns information of all databases on server.

Hope This Helps!


If you like this post, do like my Facebook Page –> SqlAndMe

EMail me your questions ->

Follow me on Twitter -> @SqlAndMe

Filed under: Backup & Recovery, Catalog Views, Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating