Location of Database Files in SQL Server

Q

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

✍: FYIcenter.com

A

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
GO

SELECT type_desc, physical_name, size
   FROM sys.database_files
GO
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.

 

Managing Databases and Physical Files in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-24, 717👍, 0💬