Command to list recovery models for all databases???

Question

Post reply

Command to list recovery models for all databases???

Jpotucek

SSCoach

Points: 18583
More actions
July 17, 2008 at 9:29 am

#71673

Can't find one.. is there a table I can query to get this??

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

select

name, recovery_model_desc

from

sys.databases

Will do it in SQL Server 2005. I do not have a SQL Server 2000 instance handy to test it, but I believe:

select

name, recovery_model_desc

from

dbo.databases

will work there.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Thank you for the reply.

select * from DBO.DATABASES against a sql server 2000 master DB only shows columns Databasename, Databaseuser, databasepassword and databaseserver..

I'll have to keep poking around : )

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

This will do it in 2000.

SELECT DATABASEPROPERTYEX(N'DatabaseName', N'RECOVERY')

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 4

I just noticed you said for all databases. If you don't mind using an undocumented procedure for sql 2000 this will work.

Create Table #tmp

(dbname varchar(100), RecoveryModel sql_variant)

INSERT INTO #tmp

EXEC sp_msforeachdb '

SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')

'

Select * from #tmp

Drop Table #tmp

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 5

Ken Simmons (7/17/2008)
I just noticed you said for all databases. If you don't mind using an undocumented procedure for sql 2000 this will work.
Create Table #tmp
(dbname varchar(100), RecoveryModel sql_variant)
INSERT INTO #tmp
EXEC sp_msforeachdb '
SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')
'
Select * from #tmp
Drop Table #tmp

No need for a temp table or the msforeach...

SELECT Name

, DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]

FROM master.dbo.sysdatabases

Your friendly High-Tech Janitor...

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 6

sp_msforeachdb 'use ? select ''?'' as DatabaseName, DATABASEPROPERTYEX(N''?'', N''RECOVERY'')'

Jpotucek SSCoach Points: 18583 More actions · Answer 7

Jpotucek

SSCoach

Points: 18583

July 17, 2008 at 12:27 pm

#844590

That worked. Thank you so much

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 8

No need for a temp table or the msforeach...

Good catch Gordon. The first thing I thought of was sp_msforeachdb and I usually create a temp table so I get the results back in the same dataset. Way more work than the solution really needed.

Thanks for the followup reply.

Command to list recovery models for all databases???

Cookies on SQLServerCentral