Availability for the Always-On Enterprise
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 » 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?

v.Eremin
Veeam Software
Posts: 15077
Liked: 1132 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Automate restoring latest SQL database backup

Post by v.Eremin » 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.

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 » Feb 19, 2015 4:44 pm

OK, thanks for confirming.

v.Eremin
Veeam Software
Posts: 15077
Liked: 1132 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Automate restoring latest SQL database backup

Post by v.Eremin » Feb 19, 2015 4:46 pm

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 » Apr 13, 2017 10:29 pm 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 » Dec 12, 2017 10:05 pm

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 » Jan 26, 2018 1:43 pm

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

Post Reply

Who is online

Users browsing this forum: foggy, Gostev and 72 guests