Script to list the recovery models for every database

Below is a script 1 that can be used to retrieve the recovery model for each database:

CREATE TABLE #DBs (DBName VARCHAR(100), RecoveryModel SQL_VARIANT)

INSERT INTO #DBs 
EXEC sp_msforeachdb 'SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')'

SELECT * FROM #DBs

DROP TABLE #DBs
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.
Updated: Friday, 12th January, 2018 — 2:53 pm

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.