script to find all databases ,which are not in full recovery mode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » script to find all databases ,which are not...

script to find all databases ,which are not in full recovery mode

Rate Topic

Display Mode

Topic Options

Author

Message

jaydxb

Posted Monday, April 28, 2008 7:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 05, 2009 7:08 AM
Points: 1, Visits: 17

i am looking for a script ,when executed on a server ,will list out all databases in full recovery mode.

thanks

jacky

Post #491799

N J

Posted Monday, April 28, 2008 9:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:38 PM
Points: 156, Visits: 417

1 of many ways...
select name from sys.databases where recovery_model = 1

NJ

Post #491809

RPSql

Posted Tuesday, April 29, 2008 7:24 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:10 AM
Points: 322, Visits: 368

This will work only with sql server 2005, how about sql server 2000?

Post #492069

RPSql

Posted Tuesday, April 29, 2008 7:38 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:10 AM
Points: 322, Visits: 368

I found for SQL server 2000 now.

Here is the script

SELECT [name] AS [DatabaseName], CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel] FROM master.dbo.sysdatabases where CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery'))<>'full'

Post #492094

« Prev Topic | Next Topic »

Permissions