Find The Database Recovery Model

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Oliver Wootton » Find The Database Recovery Model

Find The Database Recovery Model

Rate Topic

Display Mode

Topic Options

Author

Message

Oliver Wootton

Posted Sunday, September 23, 2007 9:28 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 11, Visits: 332

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

Post #401811

Fraser Blyth

Posted Saturday, April 19, 2008 10:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:41 AM
Points: 1, Visits: 66

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'

Post #487619

Wendell Sá

Posted Tuesday, November 04, 2008 9:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 26, 2012 1:37 PM
Points: 3, Visits: 176

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

Post #596704

premkuttan

Posted Thursday, June 10, 2010 10:31 PM

Old Hand

Group: General Forum Members
Last Login: Monday, November 26, 2012 9:08 AM
Points: 310, Visits: 243

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

Post #935826

« Prev Topic | Next Topic »

Permissions