How to query for current Recovery Model ?

Question

Post reply

How to query for current Recovery Model ?

ThomasH

SSCertifiable

Points: 7437
More actions
February 26, 2004 at 7:36 am

#107095

I've looked, apperently not hard enough, but not found how to query (in T-SQL) to determine the current recovery model?

Once you understand the BITs, all the pieces come together

Viewing 8 posts - 1 through 7 (of 7 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Allen Cui-55137

SSC Guru

Points: 51650

February 26, 2004 at 7:40 am

#495842

select DATABASEPROPERTYEX( yourdb , 'Recovery')

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Thanks Allen, this works perfect for me.

BTW, is the setting in a System table anywhere?

Once you understand the BITs, all the pieces come together

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Allen Cui-55137

SSC Guru

Points: 51650

February 26, 2004 at 8:03 am

#495861

I am not aware of that. Sorry.

gljjr SSCrazy Eights Points: 9970 More actions · Answer 4

Try

ALTER DATABASE YourDB SET RECOVERY SIMPLE

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

noeld SSC Guru Points: 96590 More actions · Answer 5

on sysdatabases the RECOVERY MODEL is a combination of the status Field

values 4 and 8 !!!

* Noel

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Thanks Noel...

select

Case Status & (4+8)

When 0 Then 'FULL'

When 4 Then 'BULK_LOGGED'

When 8 Then 'SIMPLE'

End as RecoveryModel,

*

from Master..SysDatabases

Once you understand the BITs, all the pieces come together

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 7

I'm working on a procedure that collects all this data into one location so I have to use your select statements.

Just wanted to mention that I've come up with a couple of databases that return a 12 and are SIMPLE recovery models. I've gotten it to work by adding another case, but I'm wondering if you have any ideas?

Thanks.

----------------

Jim P.

A little bit of this and a little byte of that can become bloatware.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.