SQL Restore via Powershell places files in wrong location

[abeidson]

I am testing the Powershell SQL restore commands and scripts in an environment. I have a 120GB database that has 8 files ( 1 mdf, 6 ndf, and 1 log ) the mdf and ndf files are all on the same drive and then the log is on it's own drive. When I run the powershell to restore to a different server it places the mdf on it's own drive and then puts the ndf and log files all on the same drive. When I restore from the UI everything ends up on the proper drives, just when I try to automate the process with Powershell does it have this issue ( trying to setup a second test server and have weekly restores to it for the data ). Any ideas why this would happen?

Below is the script I am using, besides the weird placement of files it works well.

Code: Select all

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

$source_job_name    = "SQLVMtest" #Name of Veeam Backup Job for the server
$source_vm          = "SQL2012Test" #Original Server Location
$source_db_name     = "StackOverflow" #Database being restoreda

$target_vm          = "SQL2012ReplTest"
$target_credentials = Get-VBRCredentials -Name "Andylab\aeidson"
$target_instance    = " "

$target_database    =  $source_db_name

$restore_point = Get-VBRRestorePoint -Backup $source_job_name | ? VmName -match "^$source_vm" | Sort-Object creationtime -Descending | Select-Object -First 1

try {
    $database = Get-VBRSQLDatabase -ApplicationRestorePoint $restore_point -Name $source_db_name
} catch {
    "Couldnt find database" 
    break
}

$restore_session = Start-VBRSQLDatabaseRestore -Database $database -ServerName $target_vm -InstanceName $target_instance -DatabaseName $target_database -GuestCredentials $target_credentials -SqlCredentials $target_credentials -Force

[abeidson]

Well got back to doing further testing. Re-did the database where the MDF was on E, the Log was on F and the NDF files were on G to see what happened. It still placed the NDF files on the F drive when using the powershell script to restore the database to a different server. Not really sure why we would even use Veeam for application aware backups at this point for SQL except as another way of possibly restoring if the SQL backup file was corrupt for some reason.

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