This week I discovered a database with its files not placed in the default folder (it was in fact installed in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ :(). It turned out the creation of this database was done using a script provided by the manufacturer, ignoring the default file location settings. I moved the database files to the correct location to fix this problem.

The question that came up was “Are there more databases in my environment with their files in strange locations?” I created a script that gets the file locations of all data and log files in an instance and compares it to the default file location stored in the registry. You can find the script here. The script works on SQL Server 2005 and higher. I ran this against a number of servers and did find some databases with a file location different from the default.