Location of the mdf file of the database
I have a database
Project. My problem is where can I find the
_log.ldfof my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management Studio
Ssms.exeis 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
Open a query window and run the relevant query below and view the
SQL 2005 or later:
SELECT * FROM sys.database_files
SELECT * FROM dbo.sysfiles
- Open a query window and run
sp_helpfileand view the
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.
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
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).