Solved

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

Posted on 2008-10-29
6
279 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
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.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

775 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