March 10, 2008 at 8:59 pm
Hi
I need to know on which table the database recovery model is stored.
Pls help me on this
Binu
March 10, 2008 at 9:36 pm
The recovery model is in Master..sysdatabases (column Status).
Personally, I prefer to use the function DATABASEPROPERTYEX - less fiddling with bit masks and it is more intuitive.
e.g. SELECT DATABASEPROPERTYEX ('master', 'Recovery')
March 11, 2008 at 11:25 am
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.
March 11, 2008 at 2:29 pm
Or - you could just read the results from sp_helpdb....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 3:16 pm
select name, databasepropertyex(name, 'recovery') from master..sysdatabases
😀
_____________
Donn Policarpio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply