Find The Database Recovery Model

  • Oliver Wootton

    SSC Veteran

    Points: 291

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

  • Fraser Blyth

    Grasshopper

    Points: 23

    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

    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: 1693

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply