Automate restoring latest SQL database backup

Availability for the Always-On Enterprise

Automate restoring latest SQL database backup

Veeam Logoby jalean » Thu Feb 19, 2015 3:19 pm

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

Re: Automate restoring latest SQL database backup

Veeam Logoby v.Eremin » Thu Feb 19, 2015 4:41 pm

Speaking about Veeam VB&R Powershell snap-in, currently it doesn't have commandlets using which one can automate SQL database restore. Thanks.
v.Eremin
Veeam Software
 
Posts: 12606
Liked: 910 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Automate restoring latest SQL database backup

Veeam Logoby jalean » Thu Feb 19, 2015 4:44 pm

OK, thanks for confirming.
jalean
Novice
 
Posts: 6
Liked: never
Joined: Wed Jan 21, 2015 2:34 pm
Full Name: James Lean

Re: Automate restoring latest SQL database backup

Veeam Logoby v.Eremin » Thu Feb 19, 2015 4:46 pm

You're welcome. We do consider adding those capabilities to PS snap-in sooner rather than later, though.
v.Eremin
Veeam Software
 
Posts: 12606
Liked: 910 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Automate restoring latest SQL database backup

Veeam Logoby MrGoldieLocks » Thu Apr 13, 2017 10:29 pm

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)
MrGoldieLocks
Lurker
 
Posts: 2
Liked: never
Joined: Fri Apr 07, 2017 2:57 pm
Full Name: Roman Lorenzen


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: No registered users and 5 guests