PowerShell script exchange
Post Reply
abeidson
Novice
Posts: 6
Liked: never
Joined: May 25, 2018 11:36 am
Contact:

SQL Restore via Powershell places files in wrong location

Post by abeidson » 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: May 25, 2018 11:36 am
Contact:

Re: SQL Restore via Powershell places files in wrong locatio

Post by abeidson » 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.

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

Re: SQL Restore via Powershell places files in wrong locatio

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

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.

Post Reply

Who is online

Users browsing this forum: No registered users and 7 guests