Find Database Recovery Mode

Question

Post reply

Find Database Recovery Mode

CodeLake

SSCrazy

Points: 2082
More actions
March 18, 2008 at 12:18 am

#128309

Hey Guyz,
I was wondering what TSQL Script you use to find database recovery mode?
I am using this.
SELECT CONVERT(SYSNAME, DATABASEPROPERTYEX(N'master', 'Recovery'))
Just wanted to share this with y'll and find out what others are using?
Do we have a snippet section where I can post little scripts which are useful?
Cheers,
Nirav
Kindest Regards,
WRACK
CodeLake

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I've been using this query:

select recovery_model_desc from sys.databases where name = 'master'

The sys.databases view is in every database.

I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.

Greg

SanjayAttray SSChampion Points: 13157 More actions · Answer 2

Use master

declare @DBName varchar(35),

@STR varchar (255)

declare DBRecoveryModelGenerator_cursor cursor for

select name from sysdatabases

where category in ('0', '1','16')

order by name

open DBRecoveryModelGenerator_cursor

fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)

begin

if (@@fetch_status <> -2)

begin

select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName

exec (@str)

end fetch next from DBRecoveryModelGenerator_cursor into @DBName end

close DBRecoveryModelGenerator_cursor

DEALLOCATE DBRecoveryModelGenerator_cursor

go

SQL DBA.

CodeLake SSCrazy Points: 2082 More actions · Answer 3

Greg Charles (3/18/2008)
I've been using this query:
select recovery_model_desc from sys.databases where name = 'master'
The sys.databases view is in every database.
I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.

Hi Greg,

Thanks for the reply. Will the script you posted also work under SQL 2000? I am not 100% sure that my script will work under SQL 2000.

Kindest Regards,

WRACK
CodeLake

CodeLake SSCrazy Points: 2082 More actions · Answer 4

$sanjayattray (3/18/2008)
Use master
declare @DBName varchar(35),
@STR varchar (255)
declare DBRecoveryModelGenerator_cursor cursor for
select name from sysdatabases
where category in ('0', '1','16')
order by name
open DBRecoveryModelGenerator_cursor
fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName
exec (@str)
end fetch next from DBRecoveryModelGenerator_cursor into @DBName end
close DBRecoveryModelGenerator_cursor
DEALLOCATE DBRecoveryModelGenerator_cursor
go

Hi Sanjay,

Thanks for replying. It seems to me that you are looping through the list of databases and printing the recovery model of each database.

I would normally stay away from cursor and just use the following query. I don't mean to be bashing your method, just a suggestion.

[font="Courier New"]SELECT [name] AS [DatabaseName], CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel] FROM master.dbo.sysdatabases ORDER BY name[/font]

Kindest Regards,

WRACK
CodeLake