Caine posted:
I have a server with multiple instances (MSDE and SQL Express). I run the same exact code on both and the correct value for BackupDirectory came back as if it was read from the true instance directory.
I absolutely agree as the command "backup database X to disk = 'X.bak'" does result in the backup file being in the location specified by the BackupDirectory registry value. This works correctly for both default and named instances.
If you use the SSMS GUI (Object Explorer, right click "database", then select "new database"), the default locations are filled in correctly for both default and named instances.
The problem is limited to the case of a "create database" command where
For a default instance, the files locations are as specified.
For named instances, the file locations are NOT as specified.
SQL = Scarcely Qualifies as a Language