SQL database restore to another server

[itsysmedialaan]

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.

[veremin]

Have you tried to connect to this SQL server via SQL management studio, using the very same credentials?

[itsysmedialaan]

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]

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

[veremin]

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.

[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)

[john.heimiller]

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

[Steven Bricklayer]

Hello,

We made a quick script to restore using powershell from a distant server, I hope it will help someone :

Code: Select all

#parameters
param([string]$sourceVM = "", [String]$sourceDB = "", [String]$targetVM = "", [String]$targetDB = "", [String]$DistantLogPathAsLocal = "", $password = "") 

Write-Host "sourceVM: $sourceVM";
Write-Host "sourceDB: $sourceDB";
Write-Host "targetVM: $targetVM";
Write-Host "targetDB: $targetDB";
Write-Host "DistantLogPathAsLocal: $DistantLogPathAsLocal";

"begin script restore-proper"

#Edit restore details below: (names should be in " ")
$servershortname = "veeam_server"
$username = "DOMAIN\useraccount"
$targetInstance = " "
$ServerName = "\\veeam_server\"


#*****CREATES LOG FILE WITH CURRENT DATE*****
Start-Transcript -path $DistantLogPathAsLocal -append


#import snap in
Add-PSSnapin VeeamPSSnapin


#THIS IS WHERE THE SCRIPT ACTUALLY STARTS WORKING

Try {
	#Start the connection
	"connecting to server"
	Connect-VBRServer -Server $servershortname

	#Get-VBRApplicationRestorePoint : Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication' is denied.
	#attention : add right to the registry key to $username 
	"getting restore point"
	$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name $sourceVM

	if ($restorepoint -eq "") {throw "Cant get restore point"}

	"getting session"
	$sessions = Start-VESQLRestoreSession -RestorePoint $restorepoint[$restorepoint.length-1]

	if ($sessions -eq "") {throw "Cant to get sessions"}

	"getting database"
	$database = Get-VESQLDatabase -Session $sessions[0] -Name $sourceDB

	if ($database -eq "") {throw "Cant to get database"}
	
	"getting powershell password"
	$credPS = new-object -typename System.Management.Automation.PSCredential -argumentlist ($username, $password)

	if ($credPS  -eq "") {throw "Cant to get powershell password"}

	$files = Get-VESQLDatabaseFile -Database $database

	$dataPath = "D:\Datapath\" + $targetDB + ".mdf"
	$logPath = "E:\Logspath\" + $targetDB + "_Log.ldf"
	$targetPath = @($dataPath,$logPath)

	#Performs restore to another server/instance of SQL
	"restoring database"
	Restore-VESQLDatabase -Database $database -ServerName $targetVM -InstanceName $targetInstance -DatabaseName $targetDB -SqlCredentials $credPS -File $files -TargetPath $targetPath -GuestCredentials $credPS -Force
}
Catch {
	$ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
	$StackTrace = $_.Exception.StackTrace
    "An error occurred that could not be resolved: `nError message: $ErrorMessage`nFailed item: $StackTrace`nStack trace: $StackTrace"
    Break
}
Finally {
	#Stop the session
	"stopping session"
	Stop-VESQLRestoreSession -Session $sessions[0]

	#Stop the connection
	"disconnecting from server"
	Disconnect-VBRServer
}


#End logs
Stop-Transcript


#Ends script
"end script restore-proper"
Exit

[jhoughes]

One issue I see in there is that you are not specifying any sorting or selection for your restore points when running Get-VBRApplicationRestorePoint.

Without that, you are not ensuring that you are getting the most recent SQL application-aware restore point.

If you are trying to get that most recent restore point, the results may not match that ordering in the current state.