Where Are SQL Express Databases Located?

by

There are three easy ways to find where your SQL Express database is located.

Before we go through them step by step, I’ll quickly explain how your databases are stored. You may be surprised to learn that a database has more than one file.

How SQL Express Stores Databases In Files

Each SQL Express database is stored in two or more files on your local disk.

  • data is stored in a file with the extension “.mdf”
  • logs for the database are stored in one or more files with the extension “.ldf”

Your database will probably have one log file, meaning that there are a total of two files for it. By default, the two files are stored in the same folder.

If you have several databases, you will need to know your target database name in order to identify which files belong to which database.

The file name will be the same as the database name. If you named your database “apples”, the files will be called:

  • apples.mdf
  • apples.ldf

Now let’s look at how to find the location of the files.

Video Walkthrough

If you’re prefer a visual walkthrough, then this video goes through the same three methods that I describe in the next sections of this article.

Method 1: Use Management Studio To Look At Database Properties

  1. Launch SQL Server Management Studio and connect to your server.
  2. Expand the list of databases
  3. Right-click on the database you are looking for
  4. Click on the “Files” item in the left window
  5. Expand the display in the main window

The picture below shows the Files window on my server. I’ve expanded the window horizontally so I can see the full file location.

You can also copy the path field to notepad to see it in full.

Method 2: Execute A System Procedure To Find The Database Location

You can use this method in a query window in Management Studio.

But you can also use the SQL command utility logged into the SQL Server instance. This is handy if you find that launching Management Studio is slow (click the link for ways to fix this).

You just need to know the database name.

Run this SQL command:

exec sp_helpdb [database name]

The output gives you several rows. The first row is a summary of the database size and creation date.

The next lines show the file locations. Here is the output from my server:

Method 3: Run SQL To Find The Database Location

This method queries the system tables to get the same information that is returned in the earlier methods.

The file location is stored in the system table sys.master_files.

This table has a numeric database_id field to identify which database you’re looking at. You will probably want to join to sys.databases to use the database name.

Here is a sample query where the name of the database is “aa”. Simply substitute your own database name and the query will show you the database file locations.

select f.type_desc, f.physical_name

from   sys.databases d

       inner join sys.master_files f on d.database_id = f.database_id

where d.name = ‘aa’

Note that I’m also pulling back the “type_desc” field. This will show either “ROWS” or “LOG”.

The “ROWS” record is the file that holds the data. The other file holds log information.