November 15, 2004 at 4:21 pm
Hi list,
Does anybody have an idea where SQL server keeps the information about database recovery modes (full, simple, etc) rather then looking up in EM.
Maybe someone has a script to get all modes for all databases on the server.
Thank you,
JP
November 15, 2004 at 4:39 pm
JP,
try running sp_helpdb without passing any parameters, the recovery model is listed in the 'status' column.
lloyd
November 15, 2004 at 4:55 pm
Thank you,
Yes, this is what I was looking for!
November 17, 2004 at 12:21 pm
You can run following script against master database which will list all databases with their recovery mode on the server.
################################################
Create Table #temp1 (databasename varchar(200), Recoverymode varchar(200))
Declare dbname CURSOR
FOR
select name from sysdatabases
Declare @databasename varchar(30)
OPEN dbname
FETCH NEXT FROM dbname
INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
Insert INTO #temp1 select @databasename, cast( databasepropertyex( @databasename , 'Recovery') as varchar(200))
FETCH NEXT FROM dbname
INTO @databasename
END
Select * from #temp1 order by Recoverymode, databasename
Drop Table #temp1
CLOSE dbname
DEALLOCATE dbname
####################################################
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply