January 13, 2015 06:34 by
Peter
A Recovery Model is property of a database that control how transaction log is maintained. SQL Server supports simple, FULL and BULK-LOGGED recovery models. There are multiple ways in which 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
EuWindows SIMPLE
TestDB SIMPLE
ProductCatalog SIMPLE
ReportDemo SIMPLE
ReportServer FULL
ReportServerTempDB SIMPLE
(11 row(s) affected)
Using sys.databases catalog view is easier as it returns information of all databases on server. Hope this tutorial works for you!