19

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

migrated from stackoverflow.com Sep 13 '13 at 9:13

This question came from our site for professional and enthusiast programmers.

22

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

  1. 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.

  2. 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
  1. 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.

14

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
  • 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
14

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

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
-2

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

  • 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 1 '16 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy