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

• jaydxb

  Grasshopper

  Points: 19

  More actions

  April 28, 2008 at 7:41 pm

  #125072

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

  thanks

  jacky

• N J

  Right there with Babe

  Points: 732

  More actions

  April 28, 2008 at 9:06 pm

  #808105

  1 of many ways...

  select name from sys.databases where recovery_model = 1

  NJ

• RPSQL

  Hall of Fame

  Points: 3229

  More actions

  April 29, 2008 at 7:24 am

  #808308

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

• RPSQL

  Hall of Fame

  Points: 3229

  More actions

  April 29, 2008 at 7:38 am

  #808332

  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'