poulpreben.com

Refresh SQL databases for test and development

A customer recently asked me how they could automate refreshing SQL databases into their test/dev environment using Veeam Backup & Replication. I could already think of several ways to do this, as Virtual Lab, Instant VM Recovery and file-level recovery was already part of the product since many versions. However, as this blog indicated several times, I heart PowerShell quite a lot. Another addition to the VeeamPSSnapIn in version 9 is a cmd-let called Start-VBRSQLDatabaseRestore. This sounded exactly like what the customer was requesting. As indicated in the Veeam PowerShell Reference, the cmdlet requires at least one parameter of type VBRSQLDatabase (the source). In this example, we will perform an out-of-place restore, and thus we must also specify the target SQL Server server and instance name. Let us run through how it works.

Locating the source

When navigating the user interface, there are multiple ways to locate SQL Server items available for restore. The most straight forward way is to click ‘Restore’ in the ribbon menu > Application items > Microsoft SQL Server. In this dialog we are presented with all available backups containing VMs with SQL Server installed. Even copies in secondary storages, or encrypted copies stored with Cloud Connect providers.

Restore dialog

For our PowerShell script, the first thing we must do is to locate the source database. This requires two cmd-lets. First we will use Get-VBRRestorePoint to locate the restore point of a specific job, which should contain our database for restore. Using Get-VBRSQLDatabase, we can search for available databases within the restore point. As jobs usually have more than one restore point available, we will automatically select the latest one. I have wrapped this in a couple of variables, which should make this easy to repurpose:

$source_job_name    = "backup-bfss-3par"
$source_vm          = "demo-SQLandSP"
$source_db_name     = "VeeamTest"

$restore_point      = Get-VBRRestorePoint -Backup $source_job_name | ? VmName -match "^$source_vm" | Sort-Object creationtime -Descending | Select-Object -First 1
$database           = Get-VBRSQLDatabase -ApplicationRestorePoint $restore_point -Name $source_db_name

Now, our $database variable should contain something like this:

RestorePointId : ef2716f9-0e10-4d37-a867-c5529bc84d0e
ServerName     : SQLANDSP
InstanceName   : SHAREPOINT
IsSystem       : False
IsReadonly     : False
CreationTime   : 7/28/2015 6:02:24 PM
Name           : VeeamTest
Id             : 3b794d3c-2a0f-4e76-9c2a-c73c35b95214

If we issue Start-VBRSQLDatabaseRestore -Database $database, an in-place restore is initiated. Since we want to perform out-of-place restore to our test/dev environment, we have a few more steps to complete.

Specifying the target

In this case, the SQL Server we are restoring to is not part of Veeam Backup & Replication at all. It is sitting in complete different network segment, using different credentials than our source instance. In order to register the database after restoring, we must ensure the specified credentials have access to both the server and the database instance. As we would obviously not want to store cleartext passwords in our script, we are using the already encrypted Veeam credentials manager:

SQL Server Credentials Manager

Once we have verified these prerequisites, it is time to specify them in our script. As I am restoring to the default instance, I simply specified a space as placeholder.

$target_vm          = "dcvbrsql1.democenter.int"
$target_credentials = Get-VBRCredentials -Name "DEMOCENTER\svcveeamse"
$target_instance    = " "

I have added one more detail, as the customer did not want to automatically overwrite existing databases after the refresh. I simply suffixed the source database name with today’s date:

$target_database    = "{0}-{1}" -f $source_db_name, (Get-Date -Format yyyyMMdd)

Final script

As usual, the final script is avaialbe on my Github profile > SQLAutoRestore.ps1

When executed, it is possible to monitor the progress in Veeam Backup & Replication user interface and finally watch the end result on the SQL Server.

Happy SQL developing!

Share it!

If you found this blog post to be helpful, I would be happy to see you sharing it with your social networks.