Database Files Location


“I have created a database on XXX instance, where are the physical files located?” I saw a similar post today on MSDN forum and thought of writing this short note on it. Here are the few ways to find the location of the files.

The quick way to find out the location of the data and transaction log files of a database is using the following system stored procedure. I have been using this command on a daily basis since past few weeks, while migrating databases to SQL Server 2008 R2 instances.
-- List all the available data and log file
USE [Sudeepta];
GO
EXEC sp_helpfile;
GO

You can also execute the following command to find the location of the data and transaction log files of a database.
-- List all the available data and log file
USE [Sudeepta];
GO
SELECT name, type_desc, physical_name FROM sys.database_files;
GO
We can find it by GUI as well. Connect to the instance using SSMS; expand the Databases, Right-click on the database, Select Properties. Click on the Files tab and we are presented with the location of the data and log files:
Hope, this may help someone. Happy Learning 🙂

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, SQL Server, T-SQL and tagged , , . Bookmark the permalink.

2 Responses to Database Files Location

  1. nakulvachhrajani says:

    Good one, Sudeepta!

    You may also be interested in reading my post: http://beyondrelational.com/modules/2/blogs/77/posts/11327/ssms-database-restore-physical-file-names-should-default-to-logical-file-names-ms-connect-case-66856.aspx that talks about a drawback of the SQL 2005-2008 UI. The SQL 2012 UI is much better for file & file-group management for starters/casual developers.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s