Find The Database Recovery Model

• Oliver Wootton

  SSC Veteran

  Points: 291

  More actions

  September 23, 2007 at 9:28 pm

  #198180

  Comments posted to this topic are about the item Find The Database Recovery Model

• Fraser Blyth

  Grasshopper

  Points: 23

  More actions

  April 19, 2008 at 10:49 pm

  #804685

  You can also determine the recovery model using the function DATABASEPROPERTYEX

  It returns a SQL_VARIANT, so you need to convert it into character data

  CONVERT(NVARCHAR,DATABASEPROPERTYEX(' ','Recovery'))

  It will return 'FULL', 'SIMPLE' or 'BULK_LOGGED'

• Wendell Sá

  SSC Journeyman

  Points: 97

  More actions

  November 4, 2008 at 9:57 am

  #893726

  Script para listar todos os bancos de dados da instância utilizando sp_msforeachdb.

  IF object_id('Recovery') is not null

  DROP TABLE Recovery

  CREATE TABLE Recovery(

  [name] sysname NOT NULL,

  [RecoveryModel] nvarchar(128) NOT NULL

  )

  --sp_msforeachdb: http://blogs.techrepublic.com.com/datacenter/?p=292

  INSERT INTO Recovery

  EXECUTE master.dbo.sp_msforeachdb 'select DB_NAME(DB_ID(''?'')) as name, convert(sysname,DatabasePropertyEx(DB_NAME(DB_ID(''?'')),''Recovery'')) as RecoveryModel'

  SELECT * FROM Recovery

  IF object_id('Recovery') is not null

  DROP TABLE Recovery

• premkuttan

  SSCommitted

  Points: 1934

  More actions

  June 10, 2010 at 10:31 pm

  #1179016

  When i try executing that query it shows an error "string or binary data truncated"

  there is a pblm in insert into #temp exec sp_helpdb