[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Displaying database recovery model

Jason

3/19/2007 6:41:00 PM

I'm using SQL 2000 sp4, on a windows 2000 sp4 server.

I'm trying to find a way to write a query that will return all database
names on an instance, along with what recovery model is being used for that
database (ie simple, full, or bulk-logged). Does anyone know an easy way to
get this without looking in enterprise manager?
4 Answers

Tom Cooper

3/19/2007 7:10:00 PM

0

Select DB_Name(dbid) As DatabaseName,
DatabasePropertyEx(DB_Name(dbid), 'Recovery') As RecoveryMode
From master.dbo.sysdatabases
Order By DB_Name(dbid);

Tom

"Jason" <Jason@discussions.microsoft.com> wrote in message
news:10105CD0-81AB-4B30-ACC7-201E7887E91D@microsoft.com...
> I'm using SQL 2000 sp4, on a windows 2000 sp4 server.
>
> I'm trying to find a way to write a query that will return all database
> names on an instance, along with what recovery model is being used for
> that
> database (ie simple, full, or bulk-logged). Does anyone know an easy way
> to
> get this without looking in enterprise manager?


Alejandro Mesa

3/19/2007 7:18:00 PM

0

Jason,

See function "databasepropertyex" in BOL.

select
dbid,
[name],
databasepropertyex([name], 'Recovery')
from
master.dbo.sysdatabases
order by
dbid
go


AMB


"Jason" wrote:

> I'm using SQL 2000 sp4, on a windows 2000 sp4 server.
>
> I'm trying to find a way to write a query that will return all database
> names on an instance, along with what recovery model is being used for that
> database (ie simple, full, or bulk-logged). Does anyone know an easy way to
> get this without looking in enterprise manager?

changliw

3/20/2007 5:16:00 AM

0

Another one sp_helpdb. :-)

Please feel free to let me know if you have any other questions or concerns.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

changliw

3/22/2007 8:12:00 AM

0

Hi, Jason,

Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.

Have a great day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================