Location of the mdf file of the database

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

    This does not produce information on TempDB, model or msdb.

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

    ,(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
    FROM sys.databases sd
    INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
    smf.type_desc IN (

    I would remove the WHERE clause to make sure you get all the files associated with the database.

  • 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

  • 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';
  • 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 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

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

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM