SQL DB Restore Script

[andy0789]

For DR purposes restoring the entire SQL VM was not possible as our SQL servers where in a clustered setup. Tip: When restoring a clustered VM, the clustered volumes are not restored. They need to be restored manually after the VM is restored. This was bad for automation. Instead we created new SQL servers in our DR datacenter that always remained up. I created this script to restore the databases to these DR SQL servers from a backup (backup copy). This allowed for automation and keeping our database in the DR site up to date in the event they are needed.

Hope it helps someone.

Code: Select all

<#
Script handles restoring of database from the server [ServerName] to DR Site server [DRServerName]

Written By: Andy Gawronski agawronski@gmail.com
Date: 8/2/2021
#>

$TimeStamp = Get-Date -Format yyyyMMdd_HHmmss
#Specify path and filename to write log files
$Logfile = 'D:\VeeamSQLRestore\Scripts\SQLSERVERNAME_' + $TimeStamp + '.log'
Start-Transcript -Path $Logfile

# Enter the Backup (Copy) Job Name containing the Backups
# The name may not be what the Veeam application shows. Run "Get-VBRBackup | select Name" to see all job names to verify.
$JobName = "SQL Backup Copy Job\SQL Server Backup Job - SQLSERVERNAME.domain.com"

# Enter the name of the server that contains the database to be restored.
$SourceSQLServerName = "SQLSERVERNAME.domain.com"
$SourceSQLInstanceName = "SQLINSTANCENAME"

#Enter Credentials to use for restore
$User = "domain\useraccount"
$PWord = ConvertTo-SecureString -String "P@ssw0rd" -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
#Tried to use get-storedcrential, but did not have success
#$Credential = get-storedcredential -target "SQL Restore Account"


$AgentBackups = Get-VBRBackup -Name $JobName 
$RestorePoint = Get-VBRRestorePoint -Backup $AgentBackups -Name $SourceSQLServerName | ?{$_.IsCorrupted -eq $False} | Sort-Object –Property CreationTime –Descending | Select -First 1
$ApplicationRestorePoint = Get-VBRApplicationRestorePoint -Id $RestorePoint.Id
$SQLRestoreSession = Start-VESQLRestoreSession -RestorePoint $ApplicationRestorePoint[0]

#Enter the Name of the database(s) to restore. Restore as many DBs as you want that exist on the source SQL server. You can specify a different destination server for each database.
#TargetDataFolder and TargetLogFolder can be whatever you have setup on your destination DR SQL server. They can be the same if you want.
$DBtoRestore = @(
[pscustomobject]@{DBName='DBName1';TargetDataFolder='E:\Data';TargetLogFolder='F:\Logs';DestServerName='DRSQLSERVERNAME.domain.com'}
[pscustomobject]@{DBName='DBName2';TargetDataFolder='E:\Data';TargetLogFolder='F:\Logs';DestServerName='DRSQLSERVERNAME.domain.com'}
#...
)


foreach ($rDB in $DBtoRestore) {
  #Get the database needing to be restored
  $SQLRestoreDB = Get-VESQLDatabase $SQLRestoreSession -Name $rDB.DBName -InstanceName $SourceSQLInstanceName
  #Get the Interval that is available for the database. This will be last backup with all recent transactional log backups.
  #Since the most recent transactional log backup may still be running, we are just going to use the last full/inc backup we have.
  #Was having issues using most recent vlb file because of locking. If there is a way to check and use one prior, we may be able to get a more recent restore daily.
  $SQLRestoreDBInterval = Get-VESQLDatabaseRestoreInterval -Database $SQLRestoreDB
  #Get all the database files
  $SQLRestoreDBFilePaths = Get-VESQLDatabaseFile -Database $SQLRestoreDB
  #Set proper paths for data vs log files on destination server. Check for mdf or ldf file and set new path based on that. Use the TargetDataFolder and TargetLogFolder specified above.
  $SQLRestoreDBNewFilePaths = @()
  foreach ($rDBFiles in $SQLRestoreDBFilePaths) {
   $SQLRestoreDBFileName = Split-Path $rDBFiles.Path -leaf
   if($SQLRestoreDBFileName -like '*.mdf') {
    $SQLRestoreDBNewFilePaths += $rDB.TargetDataFolder + '\' + $SQLRestoreDBFileName
   } elseif($SQLRestoreDBFileName -like '*.ldf') { 
    $SQLRestoreDBNewFilePaths += $rDB.TargetLogFolder + '\' + $SQLRestoreDBFileName
   } else {
    $SQLRestoreDBNewFilePaths += $rDB.TargetDataFolder + '\' + $SQLRestoreDBFileName
   }
  }
  #Restore the database and leave it operational.
  Restore-VESQLDatabase -Database $SQLRestoreDB -DatabaseName $rDB.DBName -ServerName $rDB.DestServerName -SqlCredentials $Credential -GuestCredentials $Credential -ToPointInTime $SQLRestoreDBInterval.FromUtc -File $SQLRestoreDBFilePaths -TargetPath $SQLRestoreDBNewFilePaths -RecoveryState "Recovery" -Force
}


Stop-VESQLRestoreSession $SQLRestoreSession
Write-Output 'End of Processing'

Stop-Transcript

[PetrM]

Hi Andrew,

We appreciate your willingness to share the script with the Community!

Thanks!

[oleg.feoktistov]

Great script. Thank you, Andrew!

[roger.vieira]

Hi Andrew,

First great script help me a lot. But in case of need to do parallel restore, do you have any idea how?

Thanks!

[johannesk]

yeah, I wondering the same with parallel restore of SQL server databases from the same instance, like is possible from within the UI.

Is that possible with powershell? Perhaps with powershell 7 and use the foreach -parallel ?