Post reply

Find The Database Recovery Model

  • September 23, 2007 at 9:28 pm

    #198180

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

  • 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'

  • 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

  • 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

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

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