Solved

Get path and name of data file and log file for a given sql 2000 database

Posted on 2008-10-29
6
294 Views
Last Modified: 2012-05-05
Hi

For doing a restore with move I need to be able to ask any given database what its datafile (Mdf) and its logfile (ldf) is.
So I need to query the server and get these two pieces of information.
How do I do that?

(SQL server 2000)
0
Comment
Question by:liversen
6 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 22830473
if you have the backup you can do this:

restore filelistonly from disk = 'c:\backupfile.bak'

if you just want to see the files for a particular db:

select * from sysfiles
0
 
LVL 5

Assisted Solution

by:Cvijo123
Cvijo123 earned 250 total points
ID: 22830474
is this what u need ?

exec sp_helpfile
0
 
LVL 1

Author Closing Comment

by:liversen
ID: 31511127
Thanks guys. Both solutions are valid.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 7

Expert Comment

by:Cedric_D
ID: 22830631
I think

select * from sysdatabases where name = 'DBNAME'

is more appropriate.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22830656
Actually its not.  That query only shows the data file location...it doesn't include the log file.
0
 
LVL 1

Author Comment

by:liversen
ID: 22830704
Just so that you know:

I have chosen to do this:

SELECT filename FROM MYDATABASE.dbo.sysfiles

and it does what I need. thanks again.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question