Automate routine restore of SQL DBs?

[kevinnaughton]

We are setting up a User Acceptance test environment for a number of our SQL servers and I want to be able to automate the weekly restore of individual SQL databases from the backup of multiple SQL servers to a single SQL server set up for UAT testing. I don't see that you can schedule restores anywhere in the GUI. Am I missing that option somewhere? I assume this could also be done via powershell, if anyone has some scripts related to the restore of individual databases from backup to a new live server and wants to point me to them, would be most appreciated.

Thanks!

[tsightler]

Hi Kevin. There are currently no options for doing this via the GUI, and options are somewhat limited even with Powershell (although I believe changes are coming in v9). However, it is possible to perform SQL database refreshes using Powershell in concert with Veeams FLR functionality. Every use case is different, but I've created the script below as a somewhat generic example on how you might refresh a data on a target server from a backup of a different SQL server. It makes the assumption that the target server file locations are the same as the source, but this could easily be adapted to other use cases.

The script as is prompts for the name a a VM backup containing the source DB, then the name of the target DB. It then connects to the target DB and provides a list of databases found and has the user select one to be updated. It then queries the DB to file the required files, start the Windows FLR for the source VM, takes the target DB offline, copies the backup files to the target, places the DB back online with the new files in place, and stops the Windows FLR. It could certainly use some error checking and other cleanups, but it was just a "getting started" type of example.

Code: Select all

#
# Example script to perform full DB restore of SQL database using Veeam FLR
#

# Load up SQL Powershell Module & Veeam PS Snapin
Import-Module -Name SQLPS -DisableNameChecking
asnp VeeamPSSnapin

# Get Source DB Server
write-host
$SrcSqlName = Read-Host -Prompt 'Enter Source SQL VM Name'

# Get DB Server Name and List fount databases
write-host
$TgtSqlName = Read-Host -Prompt 'Enter Target SQL Server Name'
$TgtSql = new-object ('Microsoft.SqlServer.Management.Smo.Server') $TgtSqlName
$TgtSql.Databases | Select Name, Status 

# Get DB Name & object, exit if no match found
write-host
$TgtDbName = Read-Host -Prompt 'Enter Database Name'
$TgtDb = $TgtSql.Databases | ?{$_.Name -eq $TgtDbName}

# Get List of files used by database
$TgtDbFiles = @()
$TgtDbFiles = $TgtDbFiles + $TgtDb.FileGroups.Files.FileName
$TgtDbFiles = $TgtDbFiles + $TgtDb.LogFiles.FileName

# Start the Veeam File Level Restore Session
write-host -NoNewline "`nStarting FLR Session..."
$FlrResult = Get-VBRRestorePoint -Name $SrcSqlName | ?{$_.GetBackup().JobType -eq "Backup"} | Sort -Property CreationTime -Descending | Select-Object -First 1 | Start-VBRWindowsFileRestore 
write-host -ForegroundColor Green "Done`n"

# Take DB Offline
$TgtDb.SetOffline()

# Copy all DB Files for restore
foreach ($File in $TgtDbFiles) {
    $RelPath = Split-Path $file -NoQualifier
    $Drive = Split-Path $File -Qualifier
    $FlrSrcPath = ($FlrResult.MountSession.MountedDevices | ?{$_.DriveLetter -eq $Drive}).MountPoint + $RelPath
    $FlrDstPath = "\\" + $TgtSqlName + "\" + ($Drive -replace ":","$") + $RelPath
    write-host -NoNewLine -ForegroundColor Green "Copying "
    write-host $FlrSrcPath
    write-host -NoNewLine -ForegroundColor Green "to "
    write-host "$FlrDstPath`n"
    Copy-Item -Path $FlrSrcPath -Destination ("Microsoft.PowerShell.Core\FileSystem::" + $FlrDstPath)
}

# Place DB Online
$TgtDb.SetOnline()

# Stop FLR Session
write-host -NoNewline "Stopping FLR Session..."
Stop-VBRWindowsFileRestore $FlrResult
write-host -ForegroundColor Green "Done`n"

[kevinnaughton]

That's awesome, thank you! Didn't expect anyone to have a handy perfect solution to my specific challenge, but this definitely gives me the stepping stones I was looking for. Thanks again.