SQL database restore to another server

PowerShell script exchange

SQL database restore to another server

Veeam Logoby itsysmedialaan » Thu Apr 06, 2017 2:34 pm

Hey guys

I want to restore the same database every day on a test server.
I think my code is correct, however I keep getting an error "Server was not found or not accessible"

Below is my code to get a sql backup database + restorepoint.

Code: Select all
#add snapin and connect to server
Add-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue
connect-vbrserver -server "xxxxxxx" #-user <domainname\username or servername\username> -password <password>

#Variables
$RestoreDatum = Get-Date -format dd/MM/yyyy-HH:mm:ss
$ServerName = "yyyyyyy"
$InstanceName = "MSSQLSERVER" # it's default instance, but with or without this, doesn't work
$DatabaseName = "the_database_name"
$DatabaseNameNew = "$($DatabaseName) $($RestoreDatum)"

#OS and SQL Credentials
$guestcreds = Get-VBRCredentials -Name  "xxx\ServiceACCVeeam"
$sqlcreds = Get-VBRCredentials -Name "xxx\ServiceACCVeeam"

#The $Restore variable contains the correct data
$Restore = Get-VBRApplicationRestorePoint -id 5d8516b2-7bcc-484f-b402-56224c54e625

#The $DBRestorePoint variable contains the correct data
$DBRestorePoint = Get-VBRSQLDatabase -ApplicationRestorePoint $Restore -Name $DatabaseName

Start-VBRSQLDatabaseRestore -Database $DBRestorePoint -ServerName $ServerName -DatabaseName $DatabaseNameNew -GuestCredentials $guestcreds  -SqlCredentials $sqlcreds -Wait

#Stop the connection
Disconnect-VBRServer | out-null



Errorcode:
Start-VBRSQLDatabaseRestore : SQL server validation failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not
found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 -
Error Locating Server/Instance Specified)
At line:21 char:1
+ Start-VBRSQLDatabaseRestore -Database $DBRestorePoint -ServerName $ServerName -D ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Start-VBRSQLDatabaseRestore], Exception
+ FullyQualifiedErrorId : System.Exception,Veeam.Backup.PowerShell.Cmdlets.StartVBRSQLDatabaseRestore



Any known bugs? Am I doing anything wrong here?

Thanks for the help.
itsysmedialaan
Novice
 
Posts: 6
Liked: 1 time
Joined: Wed May 11, 2016 7:12 am
Full Name: ITsysteembeheer

Re: SQL database restore to another server

Veeam Logoby v.Eremin » Thu Apr 06, 2017 4:53 pm

Have you tried to connect to this SQL server via SQL management studio, using the very same credentials?
v.Eremin
Veeam Software
 
Posts: 13291
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: SQL database restore to another server

Veeam Logoby itsysmedialaan » Fri Apr 07, 2017 5:52 am

Yes, I have tried this.
The domainaccount is OS admin and SQL sysadmin.
I do presume it is windows authentication and not sql auth?
itsysmedialaan
Novice
 
Posts: 6
Liked: 1 time
Joined: Wed May 11, 2016 7:12 am
Full Name: ITsysteembeheer

Re: SQL database restore to another server

Veeam Logoby itsysmedialaan » Fri Apr 07, 2017 1:32 pm 1 person likes this post

Ok, So I've had someone from veeam support helping me out on this one.
Seems if you have a server with default instance, you have to set the instancename parameter to " " (a space)


So for people wanting to use my code for any reason, this is a working code:

Code: Select all
#add snapin and connect to server
Add-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue
connect-vbrserver -server "xxxxxxx" #-user <domainname\username or servername\username> -password <password>

#Variables
$RestoreDate = Get-Date -format dd/MM/yyyy-HH:mm:ss
$ServerName = "yyyyyyy"
$InstanceName = " " # If you have default instance, use " "
$DatabaseName = "the_database_name"
$DatabaseNameNew = "$($DatabaseName)_new"

#OS and SQL Credentials, these must be stored in the credentials store in Veeam
$guestcreds = Get-VBRCredentials -Name  "domain\ServiceACCVeeam"
$sqlcreds = Get-VBRCredentials -Name "domain\ServiceACCVeeam"

#Adjust after the WHERE clause for the right date.
$Restore = Get-VBRApplicationRestorePoint -Name "RestoreFromServer" -SQL  | Where {$_.CreationTime.year -like "2017" -and $_.CreationTime.month -like "4" -and $_.CreationTime.day -like "6"}

$DBRestorePoint = Get-VBRSQLDatabase -ApplicationRestorePoint $Restore -Name $DatabaseName

Start-VBRSQLDatabaseRestore -Database $DBRestorePoint -ServerName $ServerName -InstanceName $InstanceName -DatabaseName $DatabaseNameNew -GuestCredentials $guestcreds -SqlCredentials $sqlcreds -Wait -Force

#Stop the connection
Disconnect-VBRServer | out-null



kind regards
itsysmedialaan
Novice
 
Posts: 6
Liked: 1 time
Joined: Wed May 11, 2016 7:12 am
Full Name: ITsysteembeheer

Re: SQL database restore to another server

Veeam Logoby v.Eremin » Fri Apr 07, 2017 2:43 pm

I should have mentioned that indeed. If you're restoring to the default instance, a space must be specified as an instance name. (Some other examples can be found here). Thanks.
v.Eremin
Veeam Software
 
Posts: 13291
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: SQL database restore to another server

Veeam Logoby MrGoldieLocks » Thu Apr 13, 2017 10:25 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

Re: SQL database restore to another server

Veeam Logoby john.heimiller » Wed Jun 14, 2017 9:10 pm

Thanks for this...I'm new to Veeam and didn't know they had PS cmdlets. This is a good bootstrap for what I want to do.

In the SQL world, I always appreciate people who provide this stuff.

So thanks
john.heimiller
Novice
 
Posts: 4
Liked: 2 times
Joined: Thu Oct 01, 2015 3:11 pm
Full Name: John Heimiller


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 2 guests