Display recovery models of DB's
-
Hello, I found this on this site and I am sure it does what I wanted, but as I cannot (due to policy) create any tables on the servers I am working on, could somebody please clarify that script just displays the details and doesnt create any databases or tables?
--Steve Bergkamp
--Script to list recovery model of databases.
declare @dblist table(db sysname,model char(45))
declare @dbname sysname,@model char(45)
insert @dblist(db)select name from master..sysdatabases where name not in ('master','model','tempdb','msdb')
select @dbname=min(db) from @dblist
while @dbname is not null
begin
select @model=cast(databasepropertyex(@dbname, 'Recovery') as char(45))
update @dblist set model=@model where db=@dbname
select @dbname=min(db) from @dblist where db>@dbname
end
select * from @dblist order by model
Thank you,
D.
-
The only "table" that is being created is a table variable. You should be fine using this script.
-
Why so complicated?
Just use this:
select [db]=name, [model]=databasepropertyex(name, 'Recovery')from master..sysdatabases
where name not in ('master','model','tempdb','msdb')
order by 2, 1
-
Actually in 2005 ..
Simply this will give you that information also 🙂
SELECT * FROM sys.databasesLook for column Recovery_Model_Desc.
Thanks.
[font="Arial"]---Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font] -
or
select name, DatabasePropertyEx(name,'Recovery') as model
from master.sys.sysdatabases
where dbid > 4
order by 2
this is fun. Next.
---------------------------------------------------------------------
-
SELECT name as [Database Name], recovery_model_desc as [Recovery Model]FROM sys.databases
WHERE name NOT IN ('master','model','tempdb','msdb')
GO
-
Now you see, this is why I come here, you guys are great and a real inspiration to me, yes that sounds cheesy, but I really mean it.
Thank you all very much!
D.
(1 hour later)
PS. What would be the SQL 2000 version of this?
SELECT name as [Database Name], recovery_model_desc as [Recovery Model]
FROM sys.databases
WHERE name NOT IN ('master','model','tempdb','msdb')
GO
-
2000 version is what I posted above
-
mine would also work in both 2000 and 2005 if you replaced the master.sys.sysdatabases with master..sysdatabases
its the reference to the sys schema and the column recovery_model_desc that are 2005 specific
---------------------------------------------------------------------
-
Ah, sorry, I didnt notice the difference. Thank you.
Regards,
D.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply