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

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

SQL, Programming
Share with: 

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


Support this Site

Buy me a coffeeBuy me a coffee

Popular Articles

What is Kutamo?
Kilimanjaro 2020
Kilimanjaro 2015
Australian Postcodes
Exchange 2013 in 60 minutes

Recent Articles

Australian Post Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
Telco Together Foundation
Cloud on Kilimanjaro