Comprehensive data protection for all workloads
Post Reply
jalean
Novice
Posts: 6
Liked: never
Joined: Jan 21, 2015 2:34 pm
Full Name: James Lean
Contact:

Automate restoring latest SQL database backup

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Automate restoring latest SQL database backup

Post by 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
Novice
Posts: 6
Liked: never
Joined: Jan 21, 2015 2:34 pm
Full Name: James Lean
Contact:

Re: Automate restoring latest SQL database backup

Post by jalean »

OK, thanks for confirming.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Automate restoring latest SQL database backup

Post by veremin »

You're welcome. We do consider adding those capabilities to PS snap-in sooner rather than later, though.
MrGoldieLocks
Lurker
Posts: 2
Liked: 1 time
Joined: Apr 07, 2017 2:57 pm
Full Name: Roman Lorenzen
Contact:

Re: Automate restoring latest SQL database backup

Post by MrGoldieLocks » 1 person likes this post

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
Lurker
Posts: 1
Liked: never
Joined: Dec 12, 2017 9:58 pm
Full Name: Ricardo Pereira
Contact:

Re: Automate restoring latest SQL database backup

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Jan 26, 2018 1:06 pm
Contact:

Re: Automate restoring latest SQL database backup

Post by 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
Novice
Posts: 8
Liked: 1 time
Joined: Apr 18, 2019 8:15 am
Full Name: Remco Post
Contact:

Re: Automate restoring latest SQL database backup

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Automate restoring latest SQL database backup

Post by veremin »

Currently you cannot do it via RESTful API. Thanks!
RaboRemco
Novice
Posts: 8
Liked: 1 time
Joined: Apr 18, 2019 8:15 am
Full Name: Remco Post
Contact:

Re: Automate restoring latest SQL database backup

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Jun 08, 2021 8:37 pm
Full Name: MSP Support User
Contact:

Re: Automate restoring latest SQL database backup

Post by MSP_User »

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

Who is online

Users browsing this forum: Bing [Bot] and 260 guests