Find Recovery model - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Find Recovery model

Author

Topic

Johnf62
Starting Member

7 Posts

Posted - 2010-09-26 : 17:25:01

Hello,

I want to find a list of databases on our test servers which are not simple mode as we want to change them all to simple as they are not in daily use once tested. I use the code below in 2005 but do not have experience of 2000 and I'm struggling to get the code to work.

select * from sys.databases

where recovery_model_desc <> 'SIMPLE'

and name not in ('master','model','msdb','tempdb')

I hope someone can help and thanks given in advance of any help.

cheers
john

John Frederick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-26 : 18:00:32

Use the DATABASEPROPERTYEX function for this instead of what you've got.

Here's an example:
http://www.mssqltips.com/tip.asp?tip=1033

You could run the same code on 2000/2005 as sysdatabases is still there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog