Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Location of Database Files

By: FYIcenter.com

(Continued from previous topic...)

Where is my database stored on the hard disk?

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.

(Continued on next topic...)

1. What is a database?
2. What is the simplest way to create a new database?
3. How to set the current database?
4. How to delete a database?
5. Why I am getting this error when dropping a database?
6. How to get a list all databases on the SQL server?
7. Where is my database stored on the hard disk?
8. How to create database with physical files specified?
9. How to rename databases?
10. Why I am getting this error when renaming a database?
11. What are database states?
12. How to set a database state to OFFLINE?
13. How to move database physical files?
14. How to set database to be READ_ONLY?
15. How to set database to be SINGLE_USER?
16. What are system databases?