Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-10-29
6
288 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

839 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