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

Saturday, 16 July 2016

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


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


SQL, Programming
Share with: 

Useful SQL tips - how to list the SQL Recovery Model of all your databases

Support this Site

Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
Kilimanjar 2023