SQL Files Query

[dsv]

Hey all,

My intention was this: Find the stored procedure that will give you:
· database name
· database file location
· log file location
· backup file location

With a little digging around it seems that this is not as simple a task
as I originally thought.
The obvious problems start to rear up when you look at sp_helpdb
'dbname' as although I only have one database file and log file per
database, this is by no means the limitation of sql server.

Then it gets a little stickier with the maintenance plans, I've no
prior experience querying the info from them and nosing around the
books online has left me a little more confused than I started on this
one.

So my question is this: any advice? Should I can this project and
collect the data by hand? Is there a stored procedure that I have
completely overlooked that is just the business? Is it in fact a lot
easier than I thought and I need only...?

Comments appreciated.

Thank you,
Don

[Danny]

Don,

If you are going to need to collect the information more than a couple of
times, then I recommend writing a script or stored proc. Here are a few
hints and tricks to help you.

Using supported techniques is better than direct system table access.
Unfortunately, not every piece of data is available via supported methods.
Use the information schema views where available.
Call stored procedures and/or DBCC statements and insert the results into
temp tables. This has its limitations but can be a good source to get
supported data.
It's not bad to use a cursor in a system maintenance routine. Use them if
you need them.
When all else fails read the system tables directly.

Good luck,
Danny
<ds*@lutonsfc.ac.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hey all,

My intention was this: Find the stored procedure that will give you:
· database name
· database file location
· log file location
· backup file location

With a little digging around it seems that this is not as simple a task
as I originally thought.
The obvious problems start to rear up when you look at sp_helpdb
'dbname' as although I only have one database file and log file per
database, this is by no means the limitation of sql server.

Then it gets a little stickier with the maintenance plans, I've no
prior experience querying the info from them and nosing around the
books online has left me a little more confused than I started on this
one.

So my question is this: any advice? Should I can this project and
collect the data by hand? Is there a stored procedure that I have
completely overlooked that is just the business? Is it in fact a lot
easier than I thought and I need only...?

Comments appreciated.

Thank you,
Don

[Don Vince]

Hey Danny,

Thank you for your pointers, should I succeed in my quest, I'll be sure
to post the code here.

Thanks again,
Don