SQL Server default file locations

[dgmyrs]

We are on version 9.5 update 1

I wrote a PowerShell script that we use to automate requests from users to restore SQL Server database backups to different servers than the backup was taken on. This all works great under normal circumstances. However, there is a problem if the database contains more than one data file. For example, db_data2.ndf in the list below:

db_data1.mdf
db_data2.ndf
db_log.ldf

The PowerShell API SQL Server methods (Specifically Start-VBRSQLDatabaseRestore) do not give you the option to specify where the files being restored are placed on the target VM (A major short coming IMHO). For databases with just two files (A data and log file), it will put them in whatever the target sql server instance has set the default data directory and log directory to for each. However, when there are more than two files, it places the first file in the default data directory, and all remaining files in the default log directory. This can obviously lead to problems, as the log drive is typically a good deal smaller than the data drive(s), so it will run out of disk space.

The interesting thing is that if you run the restore from the Veeam Console GUI, it gives you the option of where to place the files, and it defaults to whatever locations were on the source database from the backup image. It doesn't even look at the target default directories, but it still works since it let's you edit file placement.

I should also point out that all the same deficiencies I found in PowerShell also apply to the enterprise manager web interface, in other words it doesn't give you file placement options, and defaults to all but the first going in the default log directory.

I believe there are two areas that should be fixed/changed in PowerShell and Enterprise Manager, as they seem to use completely different logic for file placement:
First, the default should not be to put all but the first file on the log directory. Rather, it should be to put all but the log file on the data directory, and only the log file on the log directory.
Second, PowerShell and the Backup Enterprise Manger web interface should have the option to place the files to specific locations added.

I'm curious if anyone else is running into this issue, and also if anyone has thoughts on ways around it.

Thanks!

[Spex]

We have the same requirements.
A common task in our company is to restore production data to the integration environment for testing new releases.
Since we have no option to relocate the db and log files we have to stay with our old backup solution.

[Karinne]

Parameters:
'-InstanceName', '-ServerName', 'DatabaseName' set restore will use SQL default location (right-click instance -> properties -> Database Settings).

Without setting these parameters, PS script will use the mdf/ldf/ndf file location as it was discovered during backup.