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



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

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

StackExchange Database Administrators Q#49811, answer score: 28