I recently stumbled across a .mdf and .ldf file for a database while trying to clear up space on a work machine.
Although the database did have a name, I couldn’t find a database attached to the SQL Server instance that matched that name.
So, rather than look at the properties of each attached database to view the file details, I wanted a way to query all of the attached databases.
Enter: the internet. Thanks to SQL Autority I have the script below.
SELECT name, physical_name AS current_file_location FROM sys.master_files
This will list out the location of the database file. Fantastic!
As it turns out, I was looking at a SQL Server 2012 instance, but there was also a SQL Server 2014 instance on the machine and the database in question was attached to the 2014 instance. Hence why I couldn’t find it. D’oh.