Skip to main content
  • EN

Not too long ago I had a customer who only had a Standard license which meant he didn’t have access to SureBackup, but had a need to validate a highly critical SQL database’s backups on a near daily basis. I figured it wouldn’t be too hard to write up a PS script that’d take some basic information about the source job name and database and where it needed to be restored and quickly sort by most recent and selecting it and kicking off the restore. That way he could just fire up the script when necessary. He ended up setting up a scheduled task where each day his DBAs would have the previous day’s backup restored next to the production database and they could do whatever verifications they needed. Easy peasy!

Note: Tested and working in v10 & v11 (Should work just fine in v9.x as well, but not tested)

<### Un-comment to enable debug output when troubleshooting script ###>
#$DebugPreference = "Continue"

<### Included for backward compatibility for servers still running pre-v10. ###>
Add-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue

<### Server we're getting SQL DB from ###>
$FromSQLServer = 'SOURCE_SQL_SERVER_NAME'

<### This is the name of the DB we want to restore from source SQL server #>
$DatabaseToRestore = 'SOURCE_DB_NAME'

<### Server we're restoring SQL DB to. ###>
$ToSQLServer = 'TARGET_SQL_SERVER_NAME'

<##################################################################################################
# If database is running under default SQL instance (MSSQLSERVER) comment this out and remove the #
# -InstanceName and $InstanceName from the Restore-VESQLDatabase line near the end of the script. #
###################################################################################################>
$InstanceName = 'TARGET_SQL_SERVER_INSTANCE_NAME'

<### Username/Password of account with proper access to VBR console. Un-comment if current user does not have access already. ###>
#$VBRCreds = Get-Credential

<### Needed only for connecting to VBR server from remote server. Un-comment if needed. ###>
#Connect-VBRServer -Server 'VBR_SERVER_NAME' -Credential $VBRCreds

<### Gets the list of backups we have for that SQL server and sorts the list by most recent and selects the first (newest). ###>
$RestorePoint = Get-VBRApplicationRestorePoint -SQL -Name $FromSQLServer | Sort-Object –Property CreationTime –Descending | Select -First 1

<### Start SQL restore session using specified database from latest backup point. ###>
Start-VESQLRestoreSession -RestorePoint $RestorePoint
$Session = Get-VESQLRestoreSession
$Database = Get-VESQLDatabase -Session $Sessione0] -Name $DatabaseToRestore

<### For debugging/verification that the correct information was selected (Set $DebugPreference = "Continue"). ###>
Write-Debug "Session: $Session"
Write-Debug "RestorePoint : $RestorePoint"
Write-Debug "Database: $Database"

<###########################################################################################
# If you do not specify SQL credentials, the cmdlet will use current account credentials.  #
# If credentials do not work, the cmdlet will use credentials specified in the backup job. #
# Credentials for account with sysadmin rights to target SQL server. Un-comment if needed. #
############################################################################################>
#$SQLCreds = Get-Credential

<### This is the name of the DB when it gets restored ###>
$RestoredDBName = 'TARGET_DB_NAME'
$TargetFiles = Get-VESQLDatabaseFile -Database $Database
Write-Debug "TargetFiles : $TargetFiles"

<### Adjust this to point to where the MDF/LDF files should be on target server. ###>
$TargetPath = @('D:\TARGET_DB_NAME.mdf', 'D:\TARGET_DB_NAME_log.ldf')

<################################################################################################################
# Remember to remove -InstanceName $InstanceName if you're restoring to the default SQL instance (MSSQLSERVER). #
# If using $SQLCreds, remove the comment from the end of the following command.                                 #
#################################################################################################################>
Restore-VESQLDatabase -ServerName $ToSQLServer -InstanceName $InstanceName -Database $Database -DatabaseName $RestoredDBName -File $TargetFiles -TargetPath $TargetPath -Force #-GuestCredentials $SQLCreds

Stop-VESQLRestoreSession -Session $Session
Disconnect-VBRServer | Out-Null

 

Interesting script, will have a closer look at it.

At first glance you could take the source and target db names and paths as parameters. I understand that in your usecase only one database was handeled, so hardcodingwas ok.

Thanks for sharing 😎👍🏼

Very interesting always looking at other ways for backup especially things like SQL. Thanks for sharing 👍

@CptAmerica : Thanks 

Edit (6/25/21):

Made a slight edit to the Restore-VESQLDatabase command towards the end to comment out the -GuestCredentials $SQLCreds by default since the $SQLCreds variable was commented out by default as well. Also adjusted the comments to provide additional clarity.

Thanks for the kind words. I’m a novice at PowerShell scripting, so I’m doing most of this for my own benefit in learning, so I always appreciate the feedback and ways to improve it. I hope someone finds use out of it! :)

PowerShell is fun and great to work with especially when working with Veeam as well.

Thanks, it’s interesting 🙂. Do not hesitate to publy your scripts who can be share on github.

https://github.com/VeeamHub/powershell

Thanks, it’s interesting 🙂. Do not hesitate to publy your scripts who can be share on github.

https://github.com/VeeamHub/powershell

I actually have submitted one which got added :) 
https://github.com/VeeamHub/powershell/tree/master/BR-Multi-FLR

 

I figured the VeeamHub repository was more for fleshed-out scripts that are wrapped up nice and neat into a function, but maybe I should include even the simple ones like this since I’m sure it’d be more likely to be found by someone searching for it.

It’s interesting, hope to never use :grin: thanks @CptAmerica 

Comment


Terms & Conditions