Where did my database go?

  • Article

After creating a database with a CREATE DATABASE statement without specifying any files, you may wonder where the files went. In a default installation, you will find the database files under a directory such as C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA, next to the master.mdf file.

If you want to find out which directory this is, you can take a peek at the example for attaching a database in the CREATE DATABASE page in Books Online, and run this query:

SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1

Very handy for your development environment if you uninstall / reinstall SQL Server and decide to re-attach a database.