Restore Veeam V11 SQL backup with SQL Query <..>

Restore Database using a Native SQL Query from Veeam Backup & Replication V11 Backups.

I Decided to write the following "how to" based on a request through the customer community. The question posed was “ how can you use Veeam Database publish functions from a SQL query rather then the purpose built Veeam Explorer for SQL GUI tool ? “

The Logic behind the request is,  if client had web based application running on SQL DB , providing self service with roll back & restore functionality in web app .This done by simply calling SQL query to copy data between tables & this query can be called via the webapp represented as a clickable button, rather than backup , restore & then copy why not integrate Veeam's Temporary Publish function.

To start I need to briefly describe the solution :

We will create somewhat template PowerShell Scripts ,Meaning everything in the script would be static and wont need variable inputs , so basically can be reused going forward with minimal to no updating.

To input certain variables like : DB name , Target SQL & Point In Time Ive used Parameters in this Script to allow you to add this to Query on request.

Additionally in the the post to show use case I have added a Table copy Query from the Mounted Database to target database Table.

At the End of the process we have another Static PowerShell script that Unmounts the Database & Detaches it in SQL.

Veeam Publish function

Its important to understand the Veeam SQL publish function , this allows you to mount a DB ( MDF & LDF ) out of the backup Image directly to targeted SQL server . “ This requires no copy from backup to production/dev “ this is reusing backup image to supply DB files to SQL Instance.

More Here on the Veeam Publish function URL

Veeam V11: In veeam Version 11 Release there has been further enhancement on the Publish function , which now allows you to Migrate the mounted/published Database to production. ( For another post )

Why this works With V11 ?

Pre V11 , If you call a PS script to run a Veeam SQL Publish session & then subsequently close the same PowerShell session then Veeam Publish Task will Timeout and DB will no longer be mounted.

In V11

In the version 11 an enhancement on the Pre Version Publish function, you can now instantly recover databases and the process itself will be managed by the dedicated service: Veeam Explorers Recovery Service, in other words the process will survive closing a PowerShell session.

So first , I created the base script that will always be called in the SQL query below :

These are all static values, I used the Param() function in PowerShell to input changing variables through the Native SQL Query , so to keep the base script static.

#params

param(

  [string] $sqlsvrbackupname,

  [string] $restoredb,

  [string] $sqlservername,

  [string] $newinstance,

  [string] $newname,

  [string] $pointintime

  )

$RestorePointDateTime = (([datetime]$pointintime).ToUniversalTime())    

Add-PSSnapin VeeamPSSnapin

Connect-VBRServer -Server "Name OF Backup Server"

# grab most recent restore point

$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name $sqlsvrbackupname| Sort -Property CreationTime -Descending | Select -First 1

$selectedsession = Start-VESQLRestoreSession -RestorePoint $restorepoint

$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name $restoredb

$database = Publish-VESQLDatabase -Database $database -ServerName $sqlservername -InstanceName $newinstance -DatabaseName $newname -ToPointInTime $RestorePointDateTime

 

I saved this Script to C:\SQL.ps1 on the SQL server.

I then tested calling this is PowerShell Script via a Native SQL Query Using Inputs to supply information to Parameters in the Static PowerShell script which then become the variables used in the publish function.

I then ran this in SQL as a Query

Xp_cmdshell 'powershell.exe C:\SQLPS.ps1 -sqlsvrbackupname "localhost" -restoredb "vac" -sqlservername "localhost" -newinstance "VEEAMSQL2016" -newname "VACTEMP" -pointintime 10/14/2020 10:05:39 AM'

Executes and completes and DB is then published to the SQL Server.

Notice NEW DB “VACTEMP” this was the -newname Parameter given in my Query.

I ran a rudimentary Query to copy Table data from a VACTEMP DB Table to another in VAC DB

Select * Into VAC.VeeamBR.Agentnew From VACTEMP.VeeamBR.Agent

 

Remove Un Publish DB

I then wrote the following in PowerShell & saved as C:\Unpublish.ps1 to Be called through PowerShell in SQL query.

 

Add-PSSnapin VeeamPSSnapin

Connect-VBRServer -Server "Name OF Backup Server"

 

$restoresessions = Get-VESQLRestoreSession

foreach ($restore in $restoresessions){ stop-VESQLRestoreSession -Session $restore }

 

This PowerShell script will get, or SQL restore Sessions in Veeam and Stop each one.

I then Call a Query in SQL to call this newly created PowerShell script.

 

Xp_cmdshell 'powershell.exe C:\unpublish.ps1'

 

Lastly to clean up the SQL Server , Veeam has Unpublished the SQL DB files from backup during the last PowerShell Script execution these files are no longer available to the SQL server but DB is still present , so we run final SLQ query to remove Mounted DB during the Publish “ VACTEMP”

SQL Detach DB Query :

EXEC sp_detach_db ‘VACTEMP’ , ‘true’ ;

Thank you for taking the time to read, please share and leave a comment.