SQL Restore via Powershell places files in wrong location

PowerShell script exchange

SQL Restore via Powershell places files in wrong location

Veeam Logoby abeidson » Fri Jun 01, 2018 5:14 pm

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
Novice
 
Posts: 6
Liked: never
Joined: Fri May 25, 2018 11:36 am

Re: SQL Restore via Powershell places files in wrong locatio

Veeam Logoby abeidson » Mon Jun 11, 2018 4:04 pm

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.
abeidson
Novice
 
Posts: 6
Liked: never
Joined: Fri May 25, 2018 11:36 am


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 5 guests