Indentifying Recovery Mode via Query Analyzer

Question

Indentifying Recovery Mode via Query Analyzer

Cat-253986

Ten Centuries

Points: 1082
More actions
June 22, 2007 at 1:51 pm

#372483

I am writing some scripts to check the status of things on an instance.
I am currently trying to indentify databases in Full recovery mode that haven't had a log backup in x number of days. I know in SQL 2000 I can use DATABASEPROPERTYEX to identify the recovery model, but I don't think anything like this exists in SQL 7.0. Any ideas where I can find the recovery model (via T-SQL) in SQL Server 7.0?

Viewing 12 posts - 1 through 11 (of 11 total)

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

I believe it is status in master.dbo.sysdatabases. I don't know the definitions of the integer but they change corresponding to what options you have checked in the database options tab.

Here are some of the definitions that I got from this thread

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=150618

1 = autoclose; set with sp_dboption.

4 = select into/bulkcopy; set with sp_dboption.

8 = trunc. log on chkpt; set with sp_dboption.

16 = torn page detection, set with sp_dboption.

32 = loading.

64 = pre recovery.

128 = recovering.

256 = not recovered.

512 = offline; set with sp_dboption.

1024 = read only; set with sp_dboption.

2048 = dbo use only; set with sp_dboption.

4096 = single user; set with sp_dboption.

32768 = emergency mode.

4194304 = autoshrink.

1073741824 = cleanly shutdown.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Just to add to the above... In SQL Server 7.0...

If neither Item 4 or 8 are set, that's the equivelent to FULL recovery.

IF Item 4 is set and Item 8 is not, that's the equivalent to BULK-LOGGED recovery.

IF both Item 4 and 8 are set, that's the equivelent to SIMPLE recovery.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cat-253986 Ten Centuries Points: 1082 More actions · Answer 3

Thank you both for your help. This is what I came up with

SELECT name,status,

CASE status

when (4) then 'Bulk-Logged'

when (8) then 'Simple'

when (24) then 'Simple'

when (28) then 'Bulk-Logged'

when (1073741848) then 'Simple'

when (1073741844) then 'Bulk-Logged'

ELSE 'Full'

END

FROM master.dbo.sysdatabases

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

I don't think you accounted for autoshrink being set.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

Also according to Jeff 12 would be simple and you have it as full. I think you will find that there are a number of other possibilities where both 4 and 8 are set along with other options.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

Here is a thread where Lowell wrote some code to decipher which bits are set.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=326235

Cat-253986 Ten Centuries Points: 1082 More actions · Answer 7

I agree that there are many possiblities. It lookes like it would be difficult to capture them all.

I will add 12. In my preliminary testing, 12 didn't come up.

Cat-253986 Ten Centuries Points: 1082 More actions · Answer 8

Cat-253986

Ten Centuries

Points: 1082

June 25, 2007 at 1:30 pm

#715007

GREAT! Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 9

sweet someone found something I wrote and it helped...i'm humbled

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

well you know what they say about blind pigs and broken clocks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

For SQL Server 7.0, run this script. If you see "trunc. log on chkpt" in the result set, it is not Full Recovery.

declare @db sysname
select @db = db_name()
exec sp_dboption @dbname = @db

Output:
The following options are set:      
----------------------------------- 
trunc. log on chkpt.
auto create statistics
auto update statistics