PowerShell script exchange
Post Reply
Posts: 2
Liked: 2 times
Joined: Nov 10, 2016 5:14 pm

SQL Server default file locations

Post by dgmyrs » May 10, 2017 7:53 pm 2 people like this post

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:


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.


Posts: 30
Liked: 1 time
Joined: May 09, 2012 12:52 pm
Full Name: Stefan Holzwarth

Re: SQL Server default file locations

Post by Spex » Nov 02, 2017 12:00 pm

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.

Veeam Software
Posts: 2
Liked: never
Joined: Mar 13, 2017 3:00 pm
Full Name: Karinne

Re: SQL Server default file locations

Post by Karinne » Jul 12, 2018 10:55 pm

'-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.

Post Reply

Who is online

Users browsing this forum: No registered users and 7 guests