PowerShell script exchange
vburgun
Lurker
Posts: 1
Liked: never
Joined: Jun 12, 2017 4:24 pm
Full Name: vburgun
Contact:

Specify database files SQL target location from PS

Post by vburgun »

Hi
I need to plan the automatic restoration of a database from one server to another.
I thought I could do it with PowerShell.

Unfortunately, the locations of the data and logs are not the same between the two servers.

I can not find a parameter to modify the location of the data with the command "Start-VBRSQLDatabaseRestore".

I tried to create a blank database with the correct parameters on the destination server. But when Veeam restores the database, it overwrites the existing database and recreates a database with default locations.

Code: Select all

invoke-sqlcmd -inputfile "$DatabaseDestination.sql" -serverinstance $ServeurDestination 

Code: Select all

Connect-VBRServer -Server $ServeurVeeam
#$LastBackup = (Get-VBRApplicationRestorePoint -SQL -Name "$ServeurSQLSource")[-1]
#$BackuBase = $LastBackup | Get-VBRSQLDatabaseRestoreInterval -Database $Database 
$LastBackup = (Get-VBRApplicationRestorePoint -SQL -Name "$ServeurSQLSource")[-1]
$BackuBase = Get-VBRSQLDatabase -ApplicationRestorePoint $LastBackup[0] -Name "$DatabaseSource"
$Interval = Get-VBRSQLDatabaseRestoreInterval -Database $BackuBase
#$Cred = Get-VBRCredentials -Name "OFIVALMO\svc_vea_bck"
Start-VBRSQLDatabaseRestore -Database $BackuBase -ServerName $ServeurDestination -DatabaseName $DatabaseDestination
Disconnect-VBRServer
Can you help me ? Or do you know another way?
dgmyrs
Lurker
Posts: 2
Liked: 2 times
Joined: Nov 10, 2016 5:14 pm
Contact:

Re: Specify database files SQL target location from PS

Post by dgmyrs »

Hi, I know this might be a bit late, but hopefully, it is still helpful. From what I have found, there is no way to specify the file location with the powershell commandlets. See powershell-f26/sql-server-default-file- ... 42895.html for more details on what I have found. To get around this, in my script I have let it restore to the default location and then issue sql commands afterwards to change the file location/name. Not ideal, but it's the only method that works with PowerShell.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

We're working on that at the moment. One of the next product release should have this functionality included. Thanks.
rweis
Veeam Software
Posts: 460
Liked: 71 times
Joined: Jun 13, 2011 7:46 pm
Full Name: Randy Weis
Location: Raleigh, NC, USA
Contact:

Re: Specify database files SQL target location from PS

Post by rweis »

Has this been addressed yet, the commands to specify the file locations with PowerShell?
Randy Weis
Enterprise SE, NA Strategic Accounts
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

Not yet. Thanks.
rweis
Veeam Software
Posts: 460
Liked: 71 times
Joined: Jun 13, 2011 7:46 pm
Full Name: Randy Weis
Location: Raleigh, NC, USA
Contact:

Re: Specify database files SQL target location from PS

Post by rweis »

I thought I'd found the answer, but this really doesn't address the individual file restore location question. Any idea if this will be added in the near future?

The following is an excerpt from the PowerShell Reference Guide, https://helpcenter.veeam.com/docs/backu ... tml?ver=95
Randy Weis
Enterprise SE, NA Strategic Accounts
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

Correct, plans to add this functionality are still there. Thanks.
Shane456
Novice
Posts: 4
Liked: never
Joined: Oct 04, 2018 3:51 pm
Full Name: Shane Sparks
Contact:

[MERGED] Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Shane456 »

I am restoring a SQL Database from a Production Server to a Test Server with a different Name using Powershell. Which works great. Although, I would like to rename the .mdf and .ldf files, is this possible using Powershell?

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

Thanks
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Dima P. »

Hi Shane,

Are you restoring backup file made by Veeam Agent for Windows controlled by Veeam B&R server? Thanks!
Shane456
Novice
Posts: 4
Liked: never
Joined: Oct 04, 2018 3:51 pm
Full Name: Shane Sparks
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Shane456 »

Yep, that is what I am doing. Currently, the .mdf and .ldf file restores in the same location as the original source. I would like to change the name of the files after the restore process similar to what VEEAM does during the Console Restore.
Shane456
Novice
Posts: 4
Liked: never
Joined: Oct 04, 2018 3:51 pm
Full Name: Shane Sparks
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Shane456 »

So has anyone been able to restore a SQL Database from a VEEAM Backup to a different .mdf or .ldf files using Powershell? Or rename the .mdf and .ldf files after the restore has completed?

Another option for me which would be acceptable is to be able to monitor the Restore job and be notified when it finishes. Once the restore completes I can take the DB offline and rename the .mdf and .ldf files.
Shane456
Novice
Posts: 4
Liked: never
Joined: Oct 04, 2018 3:51 pm
Full Name: Shane Sparks
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Shane456 »

Shane456 wrote: Oct 05, 2018 7:31 pm So has anyone been able to restore a SQL Database from a VEEAM Backup to a different .mdf or .ldf files using Powershell? Or rename the .mdf and .ldf files after the restore has completed?

Another option for me which would be acceptable is to be able to monitor the Restore job (using Powershell) and be notified when it finishes. Once the restore completes I can take the DB offline and rename the .mdf and .ldf files.

Is there a Powershell command to monitor VEEAM Restore Jobs? The VBRJob command only reports on:

"This cmdlet returns jobs stored in Veeam Backup & Replication database.

With this cmdlet, you can get the following types of jobs:
•Backup jobs
•Replication jobs
•Backup copy jobs
•File copy jobs
•VM Copy jobs"
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by Dima P. » 1 person likes this post

Shane,

Thanks for the heads up! I've merged this port to the correct subforum and asked our PowerShell guru to join.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by veremin » 2 people like this post

So has anyone been able to restore a SQL Database from a VEEAM Backup to a different .mdf or .ldf files using Powershell? Or rename the .mdf and .ldf files after the restore has completed?
Corresponding cmdlets are planned for Update 4. Thanks!
JvZanten
Novice
Posts: 8
Liked: 1 time
Joined: Apr 01, 2015 12:50 pm
Full Name: Johan van Zanten
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by JvZanten » 1 person likes this post

Can you confirm that a database rename action can now be done with Update 4 as mentioned ? Otherwise I need to add some code to do so
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by veremin »

Confirmed. Thanks!
JvZanten
Novice
Posts: 8
Liked: 1 time
Joined: Apr 01, 2015 12:50 pm
Full Name: Johan van Zanten
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by JvZanten »

I installed Update4 and the first thing I noticed is that the databases were restored in different drives/folder than previously (it listened to the SQL default paths), now it restores to the same drive/folder as the original location

further I noticed that the

Code: Select all

$Database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorePoint -Name $sourceDB
$RestoreSession = Start-VBRSQLDatabaseRestore -force -wait -Database $database -ServerName $targetVM -InstanceName $targetInstance -GuestCredentials $targetCreds -SqlCredentials $targetCreds
commandlets are now obsolete and replaced by get-vesql* counterparts but I'm not getting any further than this:

$R

Code: Select all

estorePoint = Get-VBRApplicationRestorePoint -SQL -Name "$sourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1
$session=Start-VESQLRestoreSession -RestorePoint $RestorePoint
$sourceDBs=@array of databases

foreach ($sourceDB in $SourceDBs){ 
    $Database =  Get-VESQLDatabase -Session $session -Name $sourceDB
    $files = Get-VESQLDatabaseFile -Database $database
    $path = @("$TargetMDFPath$SourceDB.mdf","$TargetLDFPath$SourceDB.ldf")
    $restoreSession=Restore-VESQLDatabase -Database $Database -ServerName $targetVM -File $files -TargetPath $path -SqlCredentials $targetCreds -GuestCredential $TargetCred -Force
    $RestoreStatus = Get-VBRRestoreSession -id $restoreSession.Id
}
any ideas ?

grtx johan
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by veremin »

I don't have console at hand at the moment, but can you give a shot to Export-VESQLDatabase cmdlet and see whether it something you're after? Thanks!
JvZanten
Novice
Posts: 8
Liked: 1 time
Joined: Apr 01, 2015 12:50 pm
Full Name: Johan van Zanten
Contact:

Re: Restoring a SQL DB using Powershell Script - Rename .mdf and .ldf

Post by JvZanten »

Hi Vladimir,

I don't think the export-VESQLDatabase would suffice, because that is intended to export the database to a bak file, which you may restore to sql of course but that is a 2-way step as the script ran on Update3a perfectly

So I have 2 remarks:
- the original commands were not only designated obsolete but changed as well, which I believe is not the correct way of implementing new commandlets.
- the restore-VESQLDatabase commandlet is (for so far I can see) not functioning properly
- I raised a ticket (03352607) and the reply I got were links to the Update3a help sites which is lacking the new commandlets

btw for any people using pre Update4 here is the complete script:

Code: Select all

#Ends any current transcripts and starts the Auto-DB-Restore script silencing any errors
$ErrorActionPreference="SilentlyContinue"
$Startdate=get-date
$resulttotal = $null
$maildestination= "address@mailfqdn.com"
$mailserver = "mail server fqdn"
$sender="originalsender@mailfqdn.com"
$subject = "Sync databases from Production to Test"
$EventSource="Restore-Databases-to-Test"
if (! [System.Diagnostics.EventLog]::SourceExists($EventSource)) {
        New-EventLog -LogName Application -Source $EventSource
        }
write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Information -eventid 1000 -message "Refresh Script started"


#Edit restore details below: (names should be in " ")
$SourceDBs =  "Comma separated array of databases to be restored
$SourceVM = "SourceVMNAME"
$BackupJob = "Backup Job name"
#Adds VeeamPSSnapIn
Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue
<#
Destination Database Server and Instance (when using the defaul instance please use a space within quotes"
#Target DB name (in order to keep the same name of original database)
#For testing - Target DB name used when adding a date to the end of target DB name (this is currently not in use - removed the # symbol and then place a # symbol in the Target DB name option above)
#$targetDB = "{0}-{1}" -f $sourceDB, (Get-Date -Format yyyMMdd)
#> 
Import-Module SQLSERVER
$targetVM = "TargetVMNAME"
$targetInstance = "TargetInstance"
Set-Location SQLSERVER:\SQL\$TargetVM\$targetInstance
#If any SQL Users needs to added to the databaseEasy update and insert rights aswell
$SQLUsers="SQLUser"
$DomainUsers="Domain\username"
#THIS IS WHERE THE SCRIPT ACTUALLY STARTS WORKING

#*****TARGET CREDENTIALS - LEAVE INTACT*****
$targetCreds = Get-VBRCredentials -Name "VBR ServiceAccount"
$RestorePoint = Get-VBRRestorePoint -Backup $backupJob -Name "$sourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1

foreach ($sourceDB in $SourceDBs){ 
    $Database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorePoint -Name $sourceDB
    $RestoreSession = Start-VBRSQLDatabaseRestore -force -wait -Database $database -ServerName $targetVM -InstanceName $targetInstance -GuestCredentials $targetCreds -SqlCredentials $targetCreds
    $RestoreStatus = Get-VBRRestoreSession -id $restoreSession.Id
    Invoke-Sqlcmd "USE [$SourceDB];DBCC SHRINKFILE (2,TRUNCATEONLY)" 
# Add sql users to restored databases for XXX
    foreach ($SQLUser in $SQLUSERS) {
        Invoke-SqlCmd "USE [$SourceDB]; CREATE USER $SQLUSER FOR LOGIN $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', N'$SQLUSER'"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT INSERT ON OBJECT::dbo.Table1 TO $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.Table2 TO $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.Table3 TO $SQLUSER"
    }
    foreach ($DomainUser in $DomainUsers) {
        Invoke-SqlCmd "USE [$SourceDB]; CREATE USER [$DomainUser] FOR LOGIN [$DomainUser]"
        Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', [$DomainUser]"
        invoke-Sqlcmd "USE [$SourceDB]; GRANT EXECUTE ON dbo.Table4 TO [$DomainUser]"
    }

    $result = "Database $sourceDB restore which started at: " + $RestoreStatus.CreationTime + " and ended at: " + $RestoreStatus.EndTime+ " ended with result: " + $RestoreStatus.result 
    switch ($RestoreStatus.Result){
        "Succes" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Information -eventid 1000 -message $result}
        "Warning"{write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Warning -eventid 5001 -message $result}
        "Failed" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Error -eventid 9000 -message $result}
    }
    $resulttotal = $resulttotal + ([Environment]::NewLine)  + $result
}

$finishdate=get-date
$lasted=$finishdate - $startdate
$lhour=$lasted.hours
$lminute=$lasted.minutes
$sourcedate=$RestorePoint.CreationTime

$body = "These are the results of the last sync (Source backup dated: $sourcedate) started at: $startdate and lasted $lhour hours and $lminute minutes  `r`n$resulttotal `r`n`nWith kind regards."
send-mailmessage -to $maildestination -from $sender -Subject $subject -Body  $body -smtpserver $mailserver
if ($?) {write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Information -eventid 1000 -message "Mailmessage send succesfully with body:`r`n$body"}
if ($resulttotal -contains "failed"){send-mailmessage -to helpdesk@mailfqdn.com -from $sender -Subject $subject -Body  $body -smtpserver $mailserver
    write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Error -eventid 1000 -message "One of the Backups failed:`r`n$body"}

#Stop-Transcript
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

OK, finally I got my hands on my lab. Rename mdf and ldf files during restore or change their paths, you need to:

- Get those files first, using Get-VESQLDatabaseFile cmdlet
- Create an array of destination paths
- Specify both lists (file and paths), using -File and -TargetPath parameters of Restore-VESQLDatabase cmdlet

Just an example (the script assumes SQL restore session has been started already):

Code: Select all

$session = Get-VESQLRestoreSession 
database = Get-VESQLDatabase -Session $session[0] -Name "New SQL Database"
$files = Get-VESQLDatabaseFile -Database $database 
$path = @("C:\SQLDBfile1\new_file.mdf","C:\SQLDBfile2\new_file.ldf") 
$sqlcreds = Get-Credential 
$oscreds = Get-Credential 
Restore-VESQLDatabase -Database $database -ServerName "SQLServer" -File $files -TargetPath $path -SqlCredentials $sqlcreds -GuestCredential $oscreds -UseSqlAuthentication
Is this something you're after?

Thanks!
JvZanten
Novice
Posts: 8
Liked: 1 time
Joined: Apr 01, 2015 12:50 pm
Full Name: Johan van Zanten
Contact:

Re: Specify database files SQL target location from PS

Post by JvZanten »

Hi Vladimir,

that's what the get-help restore-vesqldatabase says indeed, I now have the script operational and it is able to restore databases, the only thing that is missing is the ability to read the success or failure status (the old command start-vbrsqldatabaserestore saved the restore results in the .result property) the new VESQL (Veeam ExplorerSQL ?? ) command does not such a thing

btw this is my (not entirely) finished script:

Code: Select all

#Ends any current transcripts and starts the Auto-DB-Restore script silencing any errors
$ErrorActionPreference="SilentlyContinue"
$Startdate=get-date
$Resulttotal = $null
$Maildestination= "address@mailfqdn"
$Mailserver = "mailfqdn"
$Sender="restore@mailfqdn"
$Subject = "Sync databases from Production to Test"
$EventSource="Restore-to-Test"
if (! [System.Diagnostics.EventLog]::SourceExists($EventSource)) {
        New-EventLog -LogName Application -Source $EventSource
        }
write-eventlog -logname Application -Source "Restore-to-Test" -entrytype Information -eventid 1000 -message "Refresh Script started"


#Edit restore details below: (names should be in " ")
$SourceDBs = comma separated list of databases to be restored

$SourceVM = "SOURCEVM"
$BackupJob = "ACKUP Job NAME"
$TargetMDFPath="driveletter:\Databases\"
$TargetLDFPath="driveletter:\LOGS\"
<#
#*****CREATES LOG FILE WITH CURRENT DATE*****
$logName = "log-{0}-{1}.txt" -f $sourceDB,(Get-Date -Format yyyMMdd)
Start-Transcript -path driveletter:\Scripts\Logs\$logName -append
#>
#Adds VeeamPSSnapIn
Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

<#
Destination Database Server and Instance (when using the defaul instance please use a space within quotes"
#Target DB name (in order to keep the same name of original database)
#For testing - Target DB name used when adding a date to the end of target DB name (this is currently not in use - removed the # symbol and then place a # symbol in the Target DB name option above)
#$targetDB = "{0}-{1}" -f $sourceDB, (Get-Date -Format yyyMMdd)
#>
Import-Module SQLSERVER
$TargetVM = "TARGETVM"
$TargetInstance = "TARGETINSTANCE"
Set-Location SQLSERVER:\SQL\$TargetVM\$targetInstance
#EasyInvoice needs update and insert rights aswell
$SQLUsers=comma separtedlist of sql users to be addded (for test databases)
$DomainUsers=comma separtedlist of domain users to be addded (for test databases)
#THIS IS WHERE THE SCRIPT ACTUALLY STARTS WORKING
#*****TARGET CREDENTIALS - LEAVE INTACT*****
## this oneliner is to generate an excrypted password (get-credential).password | ConvertFrom-SecureString | set-content "driveletter:\scripts\password.txt"
$password = Get-Content "driveletter:\scripts\password.txt" | ConvertTo-SecureString 
$TargetCreds = New-Object System.Management.Automation.PsCredential("domain\serviceaccount",$password)
$RestorePoint = Get-VBRApplicationRestorePoint -SQL -Name "$sourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1
Start-VESQLRestoreSession -RestorePoint $RestorePoint[0]
$session=Get-VESQLRestoreSession
foreach ($SourceDB in $SourceDBs){ 
    $Database =  Get-VESQLDatabase -Session $Session[0] -Name $SourceDB
    $Files = Get-VESQLDatabaseFile -Database $Database
    $LDFFile=$SourceDB + "_log"
    $Path = @("$TargetMDFPath$SourceDB.mdf","$TargetLDFPath$LDFFile.ldf")
  
    $RestoreSession = Restore-VESQLDatabase -Database $Database -ServerName $TargetVM -InstanceName $TargetInstance -TargetPath $path  -File $Files -SqlCredentials $TargetCreds -Force
    $RestoreStatus = $?
    Invoke-Sqlcmd "USE [$SourceDB];DBCC SHRINKFILE (2,TRUNCATEONLY)" 
# Add sql users to restored databases for EasyInvoice
    foreach ($SQLUser in $SQLUSERS) {
        Invoke-SqlCmd "USE [$SourceDB]; CREATE USER $SQLUSER FOR LOGIN $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', N'$SQLUSER'"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT INSERT ON OBJECT::dbo.BacoDiscussions TO $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.Amutak TO $SQLUSER"
        Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.GBKMUT TO $SQLUSER"
    }
    foreach ($DomainUser in $DomainUsers) {
        Invoke-SqlCmd "USE [$SourceDB]; CREATE USER [$DomainUser] FOR LOGIN [$DomainUser]"
        Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', [$DomainUser]"
        invoke-Sqlcmd "USE [$SourceDB]; GRANT EXECUTE ON dbo.bacoSalesPrice TO [$DomainUser]"
    }
    $result = "Database $sourceDB restore which started at: " + $RestoreStatus.CreationTime + " and ended at: " + $RestoreStatus.EndTime+ " ended with result: " + $RestoreStatus.result 
    switch ($RestoreStatus.Result){
        "Succes" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Information -eventid 1000 -message $result}
        "Warning"{write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Warning -eventid 5001 -message $result}
        "Failed" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Error -eventid 9000 -message $result}
    }
    $resulttotal = $resulttotal + ([Environment]::NewLine)  + $result
}
Stop-VESQLRestoreSession -Session $session[0]
$finishdate=get-date
$lasted=$finishdate - $startdate
$lhour=$lasted.hours
$lminute=$lasted.minutes
$sourcedate=$RestorePoint.CreationTime

$body = "These are the results of the last sync (Source backup dated: $sourcedate) started at: $startdate and lasted $lhour hours and $lminute minutes  `r`n$resulttotal `r`n`nWith kind regards."
send-mailmessage -to $maildestination -from $sender -Subject $subject -Body  $body -smtpserver $mailserver
if ($?) {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Information -eventid 1000 -message "Mailmessage send succesfully with body:`r`n$body"}

Stop-Transcript
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

You can handle warning and error statuses, using PS common parameters (-ErrorVariable, -WarningVariable, -ErrorAction, -WarningAction). Thanks!
rokerstrom
Lurker
Posts: 2
Liked: never
Joined: Apr 27, 2019 1:03 pm
Full Name: Ryan Okerstrom
Contact:

Re: Specify database files SQL target location from PS

Post by rokerstrom »

Is there a way to restore all the databases in the instance at once without creating multiple mount operations? I was able to get the Get-VESQLDatabase to create the $dbObject containing all the databases from the instance. However the Restore-VESQLDatabase cmdlet wouldn't accept it as an input for the -Database parameter. So instead I was able to loop through the $dbObject as an array and restore each database individually. While this saves some execution of the script with only creating the single restore session for all the databases restores. The Restore-VESQLDatabase cmdlet still mounts/dismounts a restore point during each individual database restore. I was hoping to save time on the restores that the cmdlet could be used to only mount the restore point once and then restore all the databases in the same operation.

Code: Select all

$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name $Source  | Sort -property CreationTime -Descending | Select -First 1
$session = Start-VESQLRestoreSession -RestorePoint $restorepoint[0]
$dbObject = Get-VESQLDatabase -Session $session[0]

foreach ($sourceDB in $dbObject){
$dbObject2 = Get-VESQLDatabase -Session $session[0] -Name $sourceDB.Name
Restore-VESQLDatabase -Database $dbObject2 -ServerName $Target –RecoveryState Recovery –Force -SqlCredentials $credentials
}
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

Restore-VESQLDatabase can restore only single db at a time, so, unfortunately, you cannot avoid the described situation. Thanks!
DMO
Lurker
Posts: 2
Liked: never
Joined: Jun 21, 2019 12:37 pm
Contact:

[MERGED] SQL Database Restore with Powershell

Post by DMO »

Hello,

with the Version 9.5.0.1922 I was able to restore a database with the following code.
With the new Version 9.5.4.2615 this script doesn´t work anymore.
I know that there are now new cmdlets https://helpcenter.veeam.com/docs/backu ... l?ver=95u4,
but I dont´ get a working Script like in the Version used before.
Could someone be so kind and provide me a working script based on the new Version?

Thanks in advance.

Code: Select all

Invoke-Command -ComputerName ServerName -ScriptBlock {

# ==============PARAMATERS TO CHANGE================

$veeam_backup_jobname = "TestBackupJobName" #Veeam-Backup-Jobname
$source_sql_server    = "TestSQLServer" 
$source_db_name       = "TestDatabaseName"
$target_sql_server    = "TestServer"
$target_sql_instance  = "TESTInstance"
$target_db_name       = "{0}{1}" -f $source_db_name, "" #when database exists will be overwritten, change name in "" 
$replace_to_time      = ((get-date).AddMinutes(-15)).ToUniversalTime()

# ==================================================

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue
Connect-VBRServer -Server "Servername"

$target_credentials = Get-VBRCredentials -Name "Domain\Servername"

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

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

Start-VBRSQLDatabaseRestore -Database $database -ServerName $target_sql_server -InstanceName $target_sql_instance -DatabaseName $target_db_name -GuestCredentials $target_credentials -SqlCredentials $target_credentials -topointintime $replace_to_time -force

Disconnect-VBRServer | out-null}
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

Kindly, see the examples provided above - just remove the parts that renames and places db files in different folders. Thanks!
DMO
Lurker
Posts: 2
Liked: never
Joined: Jun 21, 2019 12:37 pm
Contact:

Re: Specify database files SQL target location from PS

Post by DMO »

I have problems with the credentials.
I get the error:
"Cannot find an overload for "PSCredential" and the argument count: "2"."
and "Failed to get access to SQLServer"

In the past it was easy I used it like this:

Code: Select all

$target_credentials = Get-VBRCredentials -Name "Domain\User
-GuestCredentials $target_credentials -SqlCredentials $target_credentials"
This is my complete code so far, please help how I can use the credentials….

Code: Select all

Invoke-Command -ComputerName VeeamServer -ScriptBlock {

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

$SourceDB = "VeeamBackup-TEST"
$SourceVM = "SQLServer"
$BackupJob = "BackupJobname"
$TargetVM = "SQLServer"
$TargetInstance = "Instancename"
$TargetCreds = New-Object System.Management.Automation.PsCredential("Domain\User","Password")
$RestorePoint = Get-VBRApplicationRestorePoint -SQL -Name "$SourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1

Start-VESQLRestoreSession -RestorePoint $RestorePoint[0]

$session=Get-VESQLRestoreSession
$Database =  Get-VESQLDatabase -Session $session[0] -Name $SourceDB

Restore-VESQLDatabase -Database $Database -ServerName $TargetVM -InstanceName $TargetInstance -SqlCredentials $TargetCreds -Force  

Stop-VESQLRestoreSession -Session $session[0]

}
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Specify database files SQL target location from PS

Post by veremin »

You can use Get-Credential cmdlet to get credentials interactively. If that's not the option, create PSCredential object, using this article. Thanks!
johannesk
Expert
Posts: 146
Liked: 31 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

Re: Specify database files SQL target location from PS

Post by johannesk »

Hi,

I'm also struggling with passing the credentials to Restore-VESQLDatabase and I need it to run as a scheduled script, thus I can't use Get-Credential, since it's makes the script be interactive. I checked 'ConvertTo-SecureString' method. But as far as I can see, I still need to specify the username and password in the script. Can I tell the script somehow to take the credential from the credentials of the task scheduler? The only way I can see, is to store the password in a file and have the restore script do a lookup on that file. But that don't seem to secure for me.

Has anyone here solved this? This is a script I currently have and it's working, but with credentials stored in the script.

Code: Select all

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

$source_vm          = "backedupvm"
$source_db_name     = "test"

$target_vm          = "restoretovm"
$target_instance    = " "
$target_database    = "test-ps-restore"

# PASSWORD IN CLEAR TEXT 
$password = ConvertTo-SecureString 'securepassword' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ('domain\restoruser', $password)
$restore_point = Get-VBRApplicationRestorePoint -SQL -Name $source_vm | Sort -Property CreationTime -Descending | Select -First 1
$restore_session = Start-VESQLRestoreSession -RestorePoint $restore_point


try {
    $db_to_restore = Get-VESQLDatabase -Session $restore_session -Name $source_db_name
} catch {
    "Couldnt find database" 
    break
}


Restore-VESQLDatabase -Database $db_to_restore -DatabaseName $target_database -ServerName $target_vm -InstanceName $target_instance -GuestCredentials $credential
Stop-VESQLRestoreSession $restore_session

exit
Regards,
Jóhannes
oleg.feoktistov
Veeam Software
Posts: 1912
Liked: 635 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: Specify database files SQL target location from PS

Post by oleg.feoktistov » 1 person likes this post

Hi @johannesk,

You can use Credential Manager PS module to store passwords in your system securely and interact with them on demand:

Code: Select all

Install-Module CredentialManager
New-StoredCredential -Target 'WhatFor'  -Username 'Username' -Password 'Password'
Get-StoredCredential -Target 'WhatFor'
This module communicates with Windows Credential Manager, so you can see the same passwords there when created.

Thanks,
Oleg
Post Reply

Who is online

Users browsing this forum: No registered users and 16 guests