June 22, 2007 at 1:51 pm
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?
June 22, 2007 at 2:25 pm
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.
June 23, 2007 at 6:22 pm
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
Change is inevitable... Change for the better is not.
June 25, 2007 at 1:07 pm
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
June 25, 2007 at 1:12 pm
June 25, 2007 at 1:25 pm
June 25, 2007 at 1:28 pm
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
June 25, 2007 at 1:29 pm
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.
June 25, 2007 at 1:30 pm
GREAT! Thanks!
June 29, 2007 at 12:42 pm
sweet someone found something I wrote and it helped...i'm humbled
Lowell
June 29, 2007 at 12:56 pm
June 29, 2007 at 2:07 pm
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
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy