SQL Tips #5 - Listing the recovery model for each database

How many times have you wanted to the check the recovery model configured for each of your databases, and clicked through their properties individually? I Know I have done this too many times.

This query simply dumps the recovery model setting for each database.

How it works

The query is very simple - it just extracts the settings from sys.databases.

The Query

SELECT name AS dbname,recovery_model_desc AS recoverymodel FROM sys.databases

The output

A list of databases and their recovery model is returned.

dbname      recoverymodel
master	    SIMPLE
tempdb	    SIMPLE
model	    FULL
msdb	    SIMPLE

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags