Automate restoring latest SQL database backup

[jalean]

We are currently in the process of moving all our SQL backups into Veeam, now that v8.0 supports point-in-time recovery using the T-logs.

However with our current native SQL backups, we have a process in place where every morning the latest full backup of one of our databases is restored to a secondary server for reporting purposes:

1) Job on primary server runs native full backup every night, creating .bak file on a network share
2) Job on secondary server restores database from latest full backup on network share

Is there any way of automating this if we move the SQL backups into Veeam? Using Powershell maybe?

[veremin]

Speaking about Veeam VB&R Powershell snap-in, currently it doesn't have commandlets using which one can automate SQL database restore. Thanks.

[jalean]

OK, thanks for confirming.

[veremin]

You're welcome. We do consider adding those capabilities to PS snap-in sooner rather than later, though.

[MrGoldieLocks]

This information is used for creating a Scheduled Task in Windows to run a PowerShell script that restores a SQL database from one VM to another VM. If you are new to PowerShell and/or Veeam - pay attention to notes inside script - it is possible to overwrite your database. I am not responsible for ANYTHING you do, use at your own risk.

I worked on this task for a while and customized it to my liking. If you would like to restore multiple databases with one script - I can provide that for you as well.

Feel free to test, use, and modify all you would like. There will be some adjustments you need to make for your environment (such as database name, backup job name, domain name, CREDENTIALS, ect).

This script is setup to run on the Veeam Server using Task Scheduler. You will need to create a directory here: C:\DBRestoreScript and place this code in a .ps1 file in that directory - I named mine Auto-DB-Restore.ps1. I also created a Log folder in that directory for the logs (which are set to retain for only 7 days).

Code: Select all

#Ends any current transcripts and starts the Auto-DB-Restore script silencing any errors
$ErrorActionPreference="SilentlyContinue"
#Stop-Transcript | out-null
#$ErrorActionPreference = "Continue"


#Edit restore details below: (names should be in " ")
$sourceDB = "(database name)"
$sourceVM = "(VM name you are restoring database from)"
$backupJob = "(name of backup job in Veeam)"
$targetVM = "(VM name you wish to restore database to)"
$targetInstance = "(instance of SQL to restore to - if default, leave a space between the quotes -like this " ")"

#Target DB name (in order to keep the same name of original database)
$targetDB = "$sourceDB"

#For testing - Target DB name used when adding a date to the end of target DB name (this is currently not in use - removed the # symbol and then place a # symbol in the Target DB name option above)
#$targetDB = "{0}-{1}" -f $sourceDB, (Get-Date -Format yyyMMdd)


#*****CREATES LOG FILE WITH CURRENT DATE*****
$logName = "log-{0}-{1}.txt" -f $sourceDB,(Get-Date -Format yyyMMdd)
Start-Transcript -path C:\DBRestoreScript\Logs\$logName -append


#THIS IS WHERE THE SCRIPT ACTUALLY STARTS WORKING


#Adds VeeamPSSnapIn
Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

#*****TARGET CREDENTIALS - LEAVE INTACT*****
$targetCreds = Get-VBRCredentials -Name "(LEAVE THE QUOTES AND PUT YOUR DOMAIN\USERNAME HERE)"


#*****CHECK $RESTOREPOINT BEFORE RUNNING CODE BELOW THIS POINT FOR THE FIRST TIME TO MAKE SURE EVERYTHING IS CORRECT TO THIS POINT*****
$restorePoint = Get-VBRRestorePoint -Backup $backupJob -Name "$sourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1


#Checks the availability of restoring the DB
try {
    $database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorePoint -Name $sourceDB #NOT SURE WHY I GET THE CREATION TIME OF 9/18/2016
} catch {
    "Couldnt find database" 
    break
}

#Performs restore to another server/instance of SQL
$restoreSession = Start-VBRSQLDatabaseRestore -force -Database $database -ServerName $targetVM -InstanceName $targetInstance -DatabaseName $targetDB -GuestCredentials $targetCreds -SqlCredentials $targetCreds

#End logs
Stop-Transcript

#Remove logs older than 7 days
$limit = (Get-Date).AddDays(-7)
$path = "C:\DBRestoreScript\Logs"
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force

#Create 'Readme for logs folder
New-Item C:\DBRestoreScript\Logs\`ReadMe.txt -Force -Value "If the logs are "blank" or don't show any errors before transcript ends - job completed without error."

#Ends script
Exit

The Task Schedule was setup like:

* Using an Admin account (the same one I use with Veeam)
* Run with the highest privileges
* Configure for Windows 7, Windows Server 2008 R2 (even though I'm using 2012R2
* Trigger on a schedule of your choosing (I used midnight every night)
* Action - Start a program
* Program/script = Powershell.exe
* Add arguments = -file "C:\DBRestoreScript\Auto-DB-Restore.ps1" (you need to use the quotes - also adjust the directory and file name to what you set it as)
* Stop the task if it runs loger than: 1 hour (you can adjust the time depending on how large your databases are)

[rpereira_solubox]

Hi MrGoldieLocks, do you have any alternative for automate restore from SQL Server but in physical machines that uses Veean Agent?

[eugene.browne]

I wanted to copy a specific database from backups to a dev server periodically. This is very basic with no error checking but it worked for me. I am using AD auth for SQL server so i don't have to specify credentials:

<# Must be run as Administrator! #>

$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "DBSERVER-IN-BACKUPJOB" | Sort-Object -Property CreationTime -Descending | select -First 1 $db = Get-VBRSQLDatabase -ApplicationRestorePoint $restorepoint -Name "DB-NAME"
Start-VBRSQLDatabaseRestore -Database $db -ServerName "TARGET-DB-SERVER" -Force


Explanation:

<# Must be run as Administrator! #>

$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "DBSERVER-IN-BACKUPJOB" //get restore points for the VM with the live DB

| Sort-Object -Property CreationTime -Descending //sort by date
| select -First 1 $db = Get-VBRSQLDatabase -ApplicationRestorePoint $restorepoint -Name "DB-NAME" //select the newest restore point
Start-VBRSQLDatabaseRestore -Database $db -ServerName "TARGET-DB-SERVER" -Force //restore to the dev server. No creds needed because using an AD user with privs on the DB server

[RaboRemco]

This is a nice solution. Now, does anybody know how to do this from the Veeam Enterprise manager RESTful API? I'm guessing it must be possible because EM can do it, but I haven't found anything in the RESTful API documentation pointing to how to achieve this. I know I can get the database information from a restore point, but not a call to perform the restore or a call to find the available restore time range that comes with a database backup.

[veremin]

Currently you cannot do it via RESTful API. Thanks!

[RaboRemco]

That's a shame… we're trying to integrate Veeam into some CI/CD pipelines via vRO and having a good and complete RESTful API would make life so much easier.

[MSP_User]

Just wondering if there has been an update to this original request ?

[nathan_epay]

Hi, I know this is a somewhat old thread but I'm wondering if there are any relevant updates. I've been asked to see if Veeam has enough capability to act as a replacement for another vendor's product that includes a "workflow" tool that looks to me like a "low-code/no-code" click-n-drag UI that is probably under the hood not too far different than the type of thing that @MrGoldielocks shared above accomplishes.

Before I get too much into reviewing that PowerShell that he was kind enough to share, I thought I would check into see if there are any recent developments that might make approaching this task any easier?