Location of Database Files in SQL Server


Where is my database stored on the hard disk in SQL Server?

✍: FYIcenter.com


If a database is created with simple CREATE DATABASE statement, the server will create two database files on the hard disk to store data and configuration information about that data bases:

  • database_name.mdf - SQL Server Database Primary Data File
  • database_name_log.ldf - SQL Server Database Transaction Log File

To find out the location of database files, you can query the "sys.database_files" view as shown in this tutorial example:

USE FyiCenterData

SELECT type_desc, physical_name, size
   FROM sys.database_files
type_desc physical_name                               size

ROWS      c:\Program Files\Microsoft SQL Server
             \MSSQL.1\MSSQL\DATA\FyiCenterData.mdf     152

LOG       c:\Program Files\Microsoft SQL Server
             \MSSQL.1\MSSQL\DATA\FyiCenterData_log.LDF  63

Go verify these two files with Windows Explorer.


Creating Databases with Specified Physical Files in SQL Server

Getting a List of All Databases on the Server in SQL Server

Managing Databases and Physical Files in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-11-24, 1412🔥, 0💬