Listing Recovery modes on Databases with SP or T-S

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Listing Recovery modes on Databases with SP or T-S

Author

Topic

akruegr
Starting Member

3 Posts

Posted - 2007-03-20 : 04:46:56

I need to display the recovery mode of ALL databases on a server into a table. (Just like the sp_helpdb prcedure extended with the recoverymodel of each Database).
Can anyone give me a hint on this or provide me with a little tsql code snipplet to realize this?

Thanks alot in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 04:52:51

[code]sp_msforeachdb 'select ''?'' as DatabaseName, databasepropertyex(''?'', ''Recovery'') as RecoveryModel'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

akruegr
Starting Member

3 Posts

Posted - 2007-03-20 : 05:06:51

Great! Works fine ! Except that it does not put it into one table!
But it really does what I want it to do :-)

akruegr
Starting Member

3 Posts

Posted - 2007-03-20 : 06:36:26

Could anybody help me again? I am trying to put this into one table by creating a temporary table and inserting the data to the corresponding Columns. It keeps complaining about a sql_variant conversion error by trying to convert 'RecoveryModel' to char(20)... which should be an implicit coversion method. But maybe this simply does not work?
I'd apprechiate some help on this :-)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 06:43:51

Loop through the master..sysdatabases for getting the database names and use DATABASEPROPERTYEX() as shown earlier to get the desired output and dump it in a temp table.

For example to dump current database name and its recovery model, do something similar to this:

declare @t table
(
	a nvarchar(256),
	b nvarchar(20)
)

insert @t select db_name(), convert(nvarchar(20), databasepropertyex(db_name(), 'RECOVERY'))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources