Location of the mdf file of the database

Question

I have a database Project. My problem is where can I find the .mdf and _log.ldf of my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management Studio

Database f properties

My Ssms.exe is stored in this path

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

score 28 · Answer 1 · edited Apr 01 '16 at 10:33

There are few ways to determine the location of the SQL Server mdf file(s) and associated log file(s).

Open Enterprise Manager, right click on the database you are interested in and select properties. Select the Files section and scroll across to the Path and FileName columns.
Open a query window and run the relevant query below and view the Physical_Name column.

SQL 2005 or later:

SELECT * FROM sys.database_files

SQL 2000:

SELECT * FROM dbo.sysfiles

Open a query window and run sp_helpfile and view the FileName column.

Of course, as these files are in use by SQL Server you should not attempt to copy the files to a different location.

The best method is to perform a backup from within Enterprise Manager by right clicking on the database you are interested in and selecting Tasks -> Backup.

Alternatively you can detach your database, copy the files and then attach.

A third alternative once you have a copy of the database running elsewhere, is to set up log shipping or replication.

This does not produce information on TempDB, model or msdb. – Slogmeister Extraordinaire Jun 28 '17 at 14:02 — Slogmeister Extraordinaire, Jun 28 '17 at 14:02

score 19 · Answer 2 · edited Sep 13 '13 at 13:00

19

SQL 2005 onwards: Following query will give you list of all databases and associated files:

SELECT sd.NAME
,smf.NAME
,smf.type_desc
,(CAST(smf.size AS FLOAT) * 8096) AS SizeBytes
,(CAST(smf.size AS FLOAT) * 8096) / (1024) AS SizeKB
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024) AS SizeMB
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024 * 1024) AS SizeGB
,smf.physical_name
,sd.log_reuse_wait_desc
,sd.recovery_model_desc
,*
FROM sys.databases sd
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE
smf.type_desc IN (
    'ROWS'
    ,'LOG'
    )
ORDER BY SizeGB DESC

edited Sep 13 '13 at 13:00

Jon Seigel

16,882
6
44
84

answered Sep 13 '13 at 11:57

masam

301
1
4

1

I would remove the WHERE clause to make sure you get all the files associated with the database. – Slogmeister Extraordinaire Jun 28 '17 at 14:03

score 16 · Answer 3 · answered Sep 20 '13 at 19:37

16

You were in the right dialog, but wrong tab Open the Files tab, the path is shown for all database files enter image description here

answered Sep 20 '13 at 19:37

Milena Petrovic

1,809
12
9

score 2 · Answer 4 · answered May 10 '17 at 08:47

2

The best and easy way to find the (.mdf & .ldf) file , size and growth rate, etc. details of the particular database through below mention TSQL Query.

exec sp_helpdb @dbname='Databasename';
Go

answered May 10 '17 at 08:47

Md Haidar Ali Khan

6,447
9
38
60

score -3 · Answer 5 · answered Jun 10 '15 at 05:32

-3

The path for .mdf and .ldf will be

Root drive(where Os is installed )->

Programfiles(for 64bit applications) or programfiles(x86) for 32 but applications->

Microsoft SQL Server->

MSSQL11.SQL2012->

MSSQL->

DATA

MSSQL11.SQL2012 This is for users having using SQL 2012...

So you may easily recognize what's the folder name for you as you are using SQL 2008 under "Microsoft SQL Serve" folder in the mentioned path

answered Jun 10 '15 at 05:32

VSVAditya

1

2

This is totally correct - for small installations. For any real installation someone will have planned a disc layout and may either have defined alternative locations on db create, or even as default. You can even change that during setup. I would say in any non trivial environment people are NOT dumping data and log files on the C drive (which is there program files lives). – TomTom Apr 01 '16 at 10:41

Location of the mdf file of the database

5 Answers5