Solved

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

Posted on 2008-10-29
6
270 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

930 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now