How to get Recovery Model of a SQL 2000 Database?

[honeyvirus]

Hi ,

Can somebody help with an SQL Statement to list the Recovery models for
all the Databases in a server. I am trying to use the status column from sysdatabases..but i am not able to get the right statement.

Any help/references asap...

Thanks in advance.

Sasi.

[Schprit]

For SQL 2000

select name, databasepropertyex(name, 'Recovery') as RecoveryModel from master.dbo.sysdatabases order by name

For SQL 2005...

select name, recovery_model, recovery_model_desc from master.sys.databases

Good luck with it...

[markwillium]

Default recovery model of SQL server database is same like as recovery model of Model database. Model Database recovery model depends upon the edition of SQL server. If your model database is in 'Full' recovery model then your newly created database will also in 'Full' recovery model. To verify this lets run below query:

Now run a query to select recovery model for 'model' & 'new test database'.
SELECT Name AS 'Databasename', recovery_model_desc
FROM sys.databases
WHERE name = 'Model' OR Name = 'New Test Database'
GO

Thanks,
Mark