Check of database recovery model - SQL2k

[PugTheBlack]

I am trying to find a good command to check the recovery model of each database on my server, SQL 2k SP4.

I know how to do it in SQL2005, but I havent found a good command for it in 2000...

Select name, recovery_model_desc from sys.databases

Will do the trick in 2k5 - want a command that will do more or less the same on a SQL2000 server...

Anyone? - I could probably just mess around and make one, but why do it yourself if someone already has a good way of doing it

-Pug

 

[mutley1]

Hi Pug,

how about sp_helpdb, it's in the status column.


HTH,

M.

 

[PugTheBlack]

Good enough

atleast I can make something from that

Thx alot

-Pug

 

[mutley1]

If you wanted a TSQL script, see below.

select 
serverproperty ('machinename') as server,
name as dbname,
databasepropertyex (name, 'recovery') as rec_model
from master..sysdatabases

HTH

M.

 

[PugTheBlack]

Thx again - I made a script that created a stored procedure on all my SQL2k servers, did what I wanted it to do Was using most of the TSQL commands from that last post there

-Pug

 

[ptheriault]

You can also use

SELECT DatabaseProperty('db_name', IsTruncLog')

It will return a 1 or 0

0 = False
1 = True (simple recovery)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mutley1]

Paul,

I haven't got a test system here at the moment - just out of interest, what would bulk logged show as? Zero as well?

Thx,

M.

 

[ptheriault]

yes.

- Paul
- If at first you don't succeed, find out if the loser gets anything.