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 8 (of 8 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.