Doing a Nightly Restore of Production Database to Reporting Server

I needed to offload reporting from my production SQL server, and the reports didn’t need to be real time, so I decided to use Veeam to restore the nightly SQL backup to another server for reporting. I used the following two scripts to get that done. The batch file is called as a post-backup script in the backup job that handles the SQL Server. It simply runs a PowerShell script that starts a Veeam SQL Restore job to the second server. The reason I am using the batch file to call the PowerShell script is so that if the PS Script hangs or fails, my backup doesn’t show as failed.

ReportingServerRestore.bat

@echo off
Powershell.exe -File C:\Scripts\Production_restore.ps1

Production_restore.ps1

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

$source_job_name = "Backup Job Name"
$source_vm = "ServerName"
$source_db_name = "DatabaseName"

$target_vm = "TargetServerName"
$target_credentials = Get-VBRCredentials -Name "Username Stored in Veeam to Use"
$target_instance = " "

$target_database = "TargetDatabaseName"

$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

Two outstanding issues that I still need to address are:

  1. If the restore job should fail for some reason, I have to manually delete the old restored database to make room for the next restore job to run.
    1. I intend to find a script to remove the database before the restore starts to eliminate this issue
  2. I would like some kind of email alert so that I would know if the job was successful or failed every night.

 

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *