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')

GO

Result Set:

RecoveryModel

SIMPLE

3. Using catalog view – sys.databases:

SELECT [DatabaseName] = name,

       [RecoveryModel] = recovery_model_desc

FROM   sys.databases

GO

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!

Vishal

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

EMail me your questions -> Vishal@SqlAndMe.com

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