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

 

2. Using catalog view [sys.databases]

SELECT [DatabaseName]=name,
[RecoveryModel]=recovery_model_desc
FROM sys.databases
GO

3

3. Using Metadata function – DATABASEPROPERTYEX()

SELECT ‘AdventureWorks2012’ AS [Database Name],
DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘RECOVERY’)
AS [Recovery Model]
GO

4

 

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.

1

 

 

Hope this was helpful!!!

Advertisements