Sunday, May 8, 2011

Identify the Recovery Model

Hi All,
In this post I will like to share a script which will generate a report about the detail of the databases on the server. The script can be modified to show more information as per requirement. I have used sp_helpdb to get all the information.

create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'
select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
from #data
where name not in ('model','master','tempdb','msdb')
drop table #data

Hope this helps a lot of accidental DBA's to perform thier task of reporting.



  1. SELECT name AS [Database Name],
    recovery_model_desc AS [Recovery Model]
    FROM sys.databases

    i this is simple enough

  2. Thanks a lot on that posting. Well these dmv's make life really easy. :)