# encoding: utf-8
# api: powershell
# title: Refresh AG All DBs
# description: This script gets database or databases from load server and backs up to FileShare. Checks target availability group, gets up to 3 replicas. Checks if DB is in AG already. For each database, removes database from Availability Group. Restores database to each replica, creates a new database IF does not exist. Adds databases back to AG.
# version: 4.0
# type: script
# author: Rob Sewell
# license: CC0
# function: Invoke-Sqlcmd2
# x-poshcode-id: 5853
# x-archived: 2015-05-09T02:58:13
# x-published: 2015-05-07T15:17:00
#
# This is a refresh of Rob Sewell created, but with option to do multiple databases looped.
#
<#
.NOTES
Name: Availability Group Refresh
Author: Rob Sewell
Revised: Brett Reynolds
.DESCRIPTION
Refresh single or all user database from one AG (or instance) to another AG (Same or Different).
1) Gets database or databases from load server and backs up to FileShare
2) Checks target availability group, gets up to 3 replicas. Checks if DB is in AG already.
3) For each database, removes database from Availability Group
4) Restores database to each replica, creates a new database IF does not exist.
5) Adds databases back to AG.
Notes: Used powershell 4.0, Need SQL Management objects on computer (Studio or SDK), SysAdm rights for user running,
and file share access for user running.
#>
cls
$LoadServer = "sqlserver-prod.domain.loc" # The Load Server - Server with production database Fully Qualified Name
$LoadServerInstance = "sqlinstance" # The Load Server Instance
$DBName = "databasename" # Database Name (Comman seperate) , IF BLANK WILL TARGET ALL DATABASES IF DBALL = 1
$DBAll = "0" # IF DBName is BLank and DBAll is 1 - Will Copy All User Databases
$FileShare = '\\sql-utility\scratch\' # Enter FileShare with trailing slash ex: \\fileshare\sql\
# Set to 1 to Auto set Primary, Secondary, Tri Server Replica info from AG Group
# Set to 0 to Manually set Primary, Secondary, Tri Server Replica info
$TargetAGLoadServer = "1"
# Target Availability Group Name ex: availability-groupname
$TargetAGName = "availability-groupname"
# FILL IN IF TargetAGLoadServer = 1
$TargetAGListner = "sqlserver-dev.domain.loc" # Target Listner Address Fully Qualified Name
$TargetAGInstance = "sqlinstance" # Target Instance Address
# FILL IN IF TargetAGLoadServer = 0
$PrimaryServer = "" # The Primary Availability Group Server ex: sql-server.company.loc
$PrimaryServerInstance = "" # The Primary Availability Group Instance ex: sqlinstance
$SecondaryServer = "" # The Secondary Availability Group Server
$SecondaryServerInstance = "" # The Secondary Availability Group Instance
$TertiaryServer = ""
$TertiaryServerInstance = ""
function Invoke-Sqlcmd2 {
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$false)] [string]$Database,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Position=3, Mandatory=$false)] [string]$Username,
[Parameter(Position=4, Mandatory=$false)] [string]$Password,
[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)
if ($InputFile)
{
$filePath = $(resolve-path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
&n bsp; $conn.ConnectionString=$ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
}
} #Invoke-Sqlcmd2
function Write-Exception {
Param($exception)
Write-Host $exception.Message
While ($exception.InnerException) {
$exception = $exception.InnerException
Write-Host $exception.Message
}
}
function Test-Access() {
param([String]$Path)
$guid = [System.Guid]::NewGuid()
$access= ""
try
{
Set-Location -Path HKCU:\
$filepath = 'filesystem::' + $Path
if(Test-Path -Path $filepath)
{
$access += "Read"
}
}
catch
{
#Write-Host $_
}
if($access -eq "Read")
{
try
{
$guid = [System.Guid]::NewGuid()
[IO.File]::Create($Path+$guid, 1, 'DeleteOnClose') > $null
$access += "Write"
}
catch
{
#Write-Host $_
}
finally
{
Remove-Item $Path$guid -ErrorAction SilentlyContinue
}
}
$access
}
# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�) | out-null
# Test Path File Share AND Write Access
try
{
$access = Test-Access $FileShare
if($access -eq "ReadWrite")
{
Write-Host "File Share Access to $FileShare"
}
elseif($access -eq "Read")
{
Write-Host "File Share Permission Write Error $FileShare" -ForegroundColor Red
Exit
}
else
{
Write-Host "File Share Permission Read Error $FileShare" -ForegroundColor Red
Exit
}
}
catch
{
Write-Host $_ -ForegroundColor Red
Exit
}
# Load Replica info if 1
if($TargetAGLoadServer -eq "1")
{
if (Test-Connection $TargetAGListner -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Target) Object $TargetAGListner"
}
else
{
Write-Exception $_ -ForegroundColor Red
Write-Host "Unable Get Response (Target) Object $TargetAGListner" -ForegroundColor Red
Exit
}
Try
{
$TargetServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $TargetAGListner\$TargetAGInstance
if([string]::IsNullOrWhiteSpace($TargetServerObj.InstanceName))
{
Write-Host "No Instance found on (Target) Object $TargetAGInstance" -ForegroundColor Red
Exit
}
$TargetServerAG = $TargetServerObj.AvailabilityGroups[$TargetAGName]
if($TargetServerAG.AvailabilityReplicas.Count -lt 2)
{
Write-Host "No Replicas found on (Target) Object $TargetAGListner" -ForegroundColor Red
Exit
}
#Get Primary Server
#$TargetServerAG.AvailabilityReplicas | Where-Object {$_.Role -eq "Primary"}
$TargetServerObj.AvailabilityGroups[$TargetAGName].AvailabilityReplicas | Where-Object {$_.Role -eq "Primary"} |
ForEach-Object {
$PrimaryServer = $_.Name.Substring(0,$_.Name.IndexOf('\'))
$PrimaryServerInstance = $_.Name.Substring($_.Name.IndexOf('\')+1)
}
if($PrimaryServer -eq "")
{
Write-Host "No Primary Replica found on (Target) Object $TargetAGListner" -ForegroundColor Red
Exit
}
else
{
Write-Host "Primary Replica found on (Target) Object $TargetAGListner"
}
#Get Secondary-Tertieary Servers
$TargetServerCount = 0
$TargetServerObj.AvailabilityGroups[$TargetAGName].AvailabilityReplicas | Where-Object {$_.Role -eq "Secondary"} |
ForEach-Object {
if($TargetServerCount -eq 0)
{
$SecondaryServer = $_.Name.Substring(0,$_.Name.IndexOf('\'))
$SecondaryServerInstance = $_.Name.Substring($_.Name.IndexOf('\')+1)
$TargetServerCount = $TargetServerCount + 1
}
elseif($TargetServerCount -eq 1)
{
$TertiaryServer = $_.Name.Substring(0,$_.Name.IndexOf('\'))
$TertiaryServerInstance = $_.Name.Substring($_.Name.IndexOf('\')+1)
$TargetServerCount = $TargetServerCount + 1
}
}
if($SecondaryServer -eq "")
{
Write-Host "No Secondary Replica found on (Target) Object $TargetAGListner" -ForegroundColor Red
Exit
}else
{
Write-Host "Secondary Replica found on (Target) Object $TargetAGListner"
}
# Show Replication Database Info
#$TargetServerObj.AvailabilityGroups[$TargetAGName].DatabaseReplicaStates | Select-Object AvailabilityReplicaServerName, AvailabilityDatabaseName, IsLocal, IsSuspended, SuspendReason
}
catch
{
Write-Exception $_
Write-Host "Unable Get Replica (Target) Object $TargetAGListner" -ForegroundColor Red
Exit
}
}
elseif ($PrimaryServer -eq "")
{
Write-Host "TargetAGLoadServer Not Set, Must Enter Replica Information" -ForegroundColor Red
Exit
}
# START
$Date = Get-Date -Format ddMMyy
# Path to Availability Database Objects
$MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryServer\$PrimaryServerInstance\AvailabilityGroups\$TargetAGName"
$MyAgSecondaryPath = "SQLSERVER:\SQL\$SecondaryServer\$SecondaryServerInstance\AvailabilityGroups\$TargetAGName"
$MyAgTertiaryPath = "SQLSERVER:\SQL\$TertiaryServer\$TertiaryServerInstance\AvailabilityGroups\$TargetAGName"
if($LoadServer -eq "" -or $LoadServerInstance -eq "")
{
Write-Host "Load server/instance not set, please add load server to get source database(s)." -ForegroundColor Red
Exit
}
# Check Database Name Exists
if($DBName -eq "" -and $DBAll -eq "0")
{
Write-Host "Database Name not set and DBAll not set. Either specify DBName or set DBAll to 1 for copying all user databases." -ForegroundColor Red
Exit
}
elseif($DBName -eq "" -and $DBAll -eq "1")
{
#Get List of User Databases and set to $DBName
Try
{
if (Test-Connection $LoadServer -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Target) Object $LoadServer"
}
else
{
Write-Host $_
Write-Host "Unable Get Response (Target) Object $LoadServer" -ForegroundColor Red
Exit
}
$LoadServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $LoadServer\$LoadServerInstance -warningaction silentlycontinue
if([string]::IsNullOrWhiteSpace($LoadServerObj.InstanceName))
{
Write-Host "No Instance found on (LoadDB) Object $LoadServerInstance" -ForegroundColor Red
Exit
}
# Get User Databases
$LoadServerDatabases = $LoadServerObj.Databases | Where-Object {$_.IsSystemObject -eq $false}
$LoadServerDatabases | ForEach{
if($DBName -eq "")
{
$DBName = $_.Name
}
else
{
$DBName = $DBName + "," + $_.Name
}
}
if($DBName -eq "")
{
Write-Host "No user databases found on load server." -ForegroundColor Red
Exit
}
else
{
Write-Host "Found " $LoadServerDatabases.Count " total databases on load server (" $DBName ")"
}
}
catch
{
Write-Host $_
Write-Host "Unable Get Databases (LoadDB) Object $LoadServer" -ForegroundColor Red
Exit
}
}
# FOR Each Database Name Restore on Target
$DBName.Split(",") | ForEach {
$DBNameTarget = $_
$PrimaryServerDBExist = ""
$PrimaryServerDBExistAG = ""
$SecondaryServerDBExist = ""
$SecondaryServerDBExistAG = ""
$TertiaryServerDBExist = ""
$TertiaryServerDBExistAG = ""
if($DBNameTarget -eq "")
{
Write-Host "Database Name is blank." -ForegroundColor Red
Exit
}
$LoadDatabaseBackupFile = $FileShare + $DBNameTarget + "_" + $Date + ".bak" # Load database Backup location - Needs access permissions granted
$DatabaseBackupFile = $FileShare + $DBNameTarget + "_" + $Date + ".bak" # database Backup location - Needs access permissions granted
$LogBackupFile = $FileShare + $DBNameTarget + "_" + $Date + ".trn" # database Backup location - Needs access permissions granted
# Check Load Server
if($LoadServer)
{
Try
{
if (Test-Connection $LoadServer -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Target) Object $LoadServer"
}
else
{
Write-Host $_
Write-Host "Unable Get Response (Target) Object $LoadServer" -ForegroundColor Red
Exit
}
$LoadServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $LoadServer\$LoadServerInstance -warningaction silentlycontinue
if([string]::IsNullOrWhiteSpace($LoadServerObj.InstanceName))
{
Write-Host "No Instance found on (LoadDB) Object $LoadServerInstance" -ForegroundColor Red
Exit
}
# Check Database Exists
if(($LoadServerObj.Databases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object Status))
{
Write-Host "Check Database (LoadDB) $LoadServer Passed"
}
else
{
Write-Host "Check Database (LoadDB) $LoadServer Failed" -ForegroundColor Red
Exit
}
}
catch
{
Write-Host $_
Write-Host "Unable Check Database (LoadDB) Object $LoadServer" -ForegroundColor Red
Exit
}
}
else
{
Write-Host "Unable Check Server (LoadDB) Object $LoadServer" -ForegroundColor Red
Exit
}
# Check Database Objects
if($PrimaryServer)
{
Try
{
if (Test-Connection $PrimaryServer -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Primary) Object $PrimaryServer"
}
else
{
Write-Host $_
Write-Host "Unable Get Response (Primary) Object $PrimaryServer" -ForegroundColor Red
Exit
}
$PrimaryServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue
if([string]::IsNullOrWhiteSpace($PrimaryServerObj.InstanceName))
{
Write-Host "No Instance found on (Primary) Object $PrimaryServerInstance" -ForegroundColor Red
Exit
}
# Check Database Exists
if(($PrimaryServerObj.Databases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object Status))
{
$PrimaryServerDBExist = "1"
Write-Host "Check Database (Primary) $PrimaryServer Passed for $DBNameTarget"
# Check Database in AG
$PrimaryServerAG = $PrimaryServerObj.AvailabilityGroups[$TargetAGName]
($PrimaryServerAG.AvailabilityDatabases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object SynchronizationState) | ForEach {
if($_.SynchronizationState -eq "Synchronized")
{
Write-Host "Check Database (Primary) AvailabilityGroup $PrimaryServer Passed for $DBNameTarget"
$PrimaryServerDBExistAG = "1"
}
elseif($_.SynchronizationState -eq "NotSynchronizing")
{
Write-Host "Database SynchronizationState Error (Primary) Object $PrimaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
if($PrimaryServerDBExistAG -eq "0")
{
Write-Host "Check Database (Primary) AvailabilityGroup $PrimaryServer Not Exist for $DBNameTarget" -ForegroundColor Yellow
}
}
else
{
Write-Host "Check Database (Primary) $PrimaryServer Not Exist for $DBNameTarget" -ForegroundColor Yellow
}
}
catch
{
Write-Host $_
Write-Host "Unable Check Database Object (Primary) $PrimaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
if($SecondaryServer)
{
Try
{
if (Test-Connection $SecondaryServer -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Secondary) Object $SecondaryServer"
}
else
{
Write-Host $_
Write-Host "Unable Get Response (Secondary) Object $SecondaryServer" -ForegroundColor Red
Exit
}
$SecondaryServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $SecondaryServer\$SecondaryServerInstance -warningaction silentlycontinue
if([string]::IsNullOrWhiteSpace($SecondaryServerObj.InstanceName))
{
Write-Host "No Instance found on (Secondary) Object $SecondaryServerInstance" -ForegroundColor Red
Exit
}
# Check Database Exists
if(($SecondaryServerObj.Databases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object Status))
{
$SecondaryServerDBExist = "1"
Write-Host "Check Database (Secondary) $SecondaryServer Passed for $DBNameTarget"
# Check Database in AG
$SecondaryServerAG = $SecondaryServerObj.AvailabilityGroups[$TargetAGName]
($SecondaryServerAG.AvailabilityDatabases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object SynchronizationState) | ForEach {
if($_.SynchronizationState -eq "Synchronized")
{
Write-Host "Check Database (Secondary) AvailabilityGroup $SecondaryServer Passed for $DBNameTarget"
$SecondaryServerDBExistAG = "1"
}
elseif($_.SynchronizationState -eq "NotSynchronizing")
{
Write-Host "Database SynchronizationState Error (Secondary) Object $SecondaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
if($SecondaryServerDBExistAG -eq "0")
{
Write-Host "Check Database (Secondary) AvailabilityGroup $SecondaryServer Not Exist for $DBNameTarget" -ForegroundColor Yellow
}
}
else
{
Write-Host "Check Database (Secondary) $SecondaryServer Not Exist for $DBNameTarget" -ForegroundColor Yellow
}
}
catch
{
Write-Host $_
Write-Host "Unable Check Database (Secondary) Object $SecondaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
if($TertiaryServer)
{
Try
{
if (Test-Connection $TertiaryServer -Count 1 -ea 0 -Quiet)
{
Write-Host "Responded (Tertiary) Object $TertiaryServer"
}
else
{
Write-Host $_
Write-Host "Unable Get Response (Tertiary) Object $TertiaryServer" -ForegroundColor Red
Exit
}
$TertiaryServerObj = New-Object Microsoft.SQLServer.Management.SMO.Server $TertiaryServer\$TertiaryServerInstance -warningaction silentlycontinue
if([string]::IsNullOrWhiteSpace($TertiaryServerObj.InstanceName))
{
Write-Host "No Instance found on (Tertiary) Object $TertiaryServerInstance" -ForegroundColor Red
Exit
}
# Check Database Exists
if(($TertiaryServerObj.Databases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object Status))
{
$TertiaryServerDBExist = "1"
Write-Host "Check Database (Tertiary) $TertiaryServer Passed for $DBNameTarget"
# Check Database in AG
$TertiaryServerAG = $TertiaryServerObj.AvailabilityGroups[$TargetAGName]
($TertiaryServerAG.AvailabilityDatabases | Where-Object {$_.Name -eq $DBNameTarget} | Select-Object SynchronizationState) | ForEach {
if($_.SynchronizationState -eq "Synchronized")
{
Write-Host "Check Database (Tertiary) AvailabilityGroup $TertiaryServer Passed for $DBNameTarget"
$TertiaryServerDBExistAG = "1"
}
elseif($_.SynchronizationState -eq "NotSynchronizing")
{
Write-Host "Database SynchronizationState Error (Tertiary) Object $TertiaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
if($TertiaryServerDBExistAG -eq "0")
{
Write-Host "Check Database (Tertiary) AvailabilityGroup $TertiaryServer Not Exist for $DBNameTarget" -ForegroundColor Yellow
}
}
else
{
Write-Host "Check Database (Tertiary) $TertiaryServer Failed for $DBNameTarget" -ForegroundColor Yellow
}
}
catch
{
Write-Host $_
Write-Host "Unable Check Database (Tertiary) Object $TertiaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
}
$StartDate = Get-Date
Write-Host "Start refresh $DBNameTarget on $PrimaryServer $SecondaryServer $TertiaryServer on AG $AGName Time Script Started $StartDate" -ForegroundColor Green
cd c:
# Remove old backups
If(Test-Path $LoadDatabaseBackupFile)
{
Remove-Item -Path $LoadDatabaseBackupFile -Force
Write-Host "LoadDatabaseBackupFile Files removed"
}
If(Test-Path $DatabaseBackupFile)
{
Remove-Item -Path $DatabaseBackupFile
Write-Host "DatabaseBackupFile Files removed"
}
If(Test-Path $LogBackupFile )
{
Remove-Item -Path $LogBackupFile
Write-Host "LogBackupFile Files removed"
}
# Remove Replica Databases from Availability Group
if($SecondaryServerDBExistAG -eq "1")
{
Try
{
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\$SecondaryServerInstance\AvailabilityGroups\$TargetAGName\AvailabilityDatabases\$DBNameTarget -warningaction silentlycontinue
Start-Sleep -s 3
Write-Host "Secondary Removed from Availability Group" -ForegroundColor Green
}
catch
{
Write-Host $_
Write-Host "Unable to Remove Secondary from Availability Group $SecondaryServer" -ForegroundColor Red
Exit
}
}
if($TertiaryServerDBExistAG -eq "1")
{
Try
{
# Remove Tertiary Replica Database from Availability Group to enable restore
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\$TertiaryServerInstance\AvailabilityGroups\$TargetAGName\AvailabilityDatabases\$DBNameTarget -warningaction silentlycontinue
Start-Sleep -s 3
Write-Host "Tertiary Removed from Availability Group" -ForegroundColor Green
}
catch
{
Write-Host $_
Write-Host "Unable to Remove Tertiary from Availability Group $TertiaryServer" -ForegroundColor Red
Exit
}
}
if($PrimaryServerDBExistAG -eq "1")
{
Try
{
# Remove Primary Replica Database from Availability Group to enable restore
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\$PrimaryServerInstance\AvailabilityGroups\$TargetAGName\AvailabilityDatabases\$DBNameTarget -warningaction silentlycontinue
Start-Sleep -s 3
Write-Host "Primary Removed from Availability Group" -ForegroundColor Green
}
catch
{
Write-Host $_
Write-Host "Unable to Remove Primary from Availability Group $PrimaryServer" -ForegroundColor Red
Exit
}
}
# Backup Load Database
Try
{
Backup-SqlDatabase -Database $DBNameTarget -BackupFile $LoadDatabaseBackupFile -ServerInstance $LoadServer\$LoadServerInstance -warningaction silentlycontinue
Write-Host "Backup Load Server Database for $DBNameTarget" -ForegroundColor Green
}
catch
{
Write-Exception $_
Write-Host "Unable to Backup Load Server Database $LoadServer for $DBNameTarget" -ForegroundColor Red
Exit
}
# Restore Database to Primary
Try
{
if($PrimaryServer)
{
# Remove connections to database for Restore
if($PrimaryServerDBExist)
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue
$srv.KillAllProcesses($DBNameTarget)
# Restore Primary Replica Database from Load Database
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LoadDatabaseBackupFile -ServerInstance $PrimaryServer\$PrimaryServerInstance -ReplaceDatabase -warningaction silentlycontinue
Write-Host "Primary Database Restored for $DBNameTarget" -ForegroundColor Green
}
else
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue
$dataFolder = $srv.Settings.DefaultFile;
$logFolder = $srv.Settings.DefaultLog;
if ($dataFolder.Length -eq 0 -or $logFolder.Length -eq 0)
{
Write-Host "Primary Database Log - Data Folder Default Path Empty" -ForegroundColor Red
EXIT
}
$backupDeviceItem = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $LoadDatabaseBackupFile, File;
$restore = new-object 'Microsoft.SqlServer.Management.Smo.Restore';
$restore.Database = $DBNameTarget;
$restore.Devices.Add($backupDeviceItem);
$dataFileNumber = 0;
foreach ($file in $restore.ReadFileList($srv))
{
$relocateFile = new-object 'Microsoft.SqlServer.Management.Smo.RelocateFile';
$relocateFile.LogicalFileName = $file.LogicalName;
if ($file.Type -eq 'D'){
if($dataFileNumber -ge 1)
{
$suffix = "_$dataFileNumber";
}
else
{
$suffix = $null;
}
$relocateFile.PhysicalFileName = "$dataFolder$DBNameTarget$suffix.mdf";
$dataFileNumber ++;
}
else
{
$relocateFile.PhysicalFileName = "$logFolder$DBNameTarget.ldf";
}
$restore.RelocateFiles.Add($relocateFile) | out-null;
}
#$restore.SqlRestore($srv);
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LoadDatabaseBackupFile -ServerInstance $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue -RelocateFile $restore.RelocateFiles
Write-Host "Primary Database Restored for $DBNameTarget" -ForegroundColor Green
}
# Backup Primary Database
Backup-SqlDatabase -Database $DBNameTarget -BackupFile $DatabaseBackupFile -ServerInstance $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue
Backup-SqlDatabase -Database $DBNameTarget -BackupFile $LogBackupFile -ServerInstance $PrimaryServer\$PrimaryServerInstance -BackupAction 'Log' -warningaction silentlycontinue
Write-Host "Primary Database/Log Backed Up for $DBNameTarget" -ForegroundColor Green
}
}
catch
{
Write-Host $_
Write-Host "Unable to Load Database Backed up $LoadServer for $DBNameTarget" -ForegroundColor Red
Exit
}
# Restore Database to Secondary
Try
{
if($SecondaryServer)
{
if($SecondaryServerDBExist)
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer\$SecondaryServerInstance -warningaction silentlycontinue
$srv.KillAllProcesses($DBNameTarget)
# Restore Tertiary Replica Database
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $DatabaseBackupFile -ServerInstance $SecondaryServer\$SecondaryServerInstance -NoRecovery -ReplaceDatabase -warningaction silentlycontinue
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LogBackupFile -ServerInstance $SecondaryServer\$SecondaryServerInstance -RestoreAction 'Log' -NoRecovery -ReplaceDatabase -warningaction silentlycontinue
Write-Host "Secondary Database Restored for $DBNameTarget" -ForegroundColor Green
}
else
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer\$SecondaryServerInstance -warningaction silentlycontinue
$dataFolder = $srv.Settings.DefaultFile;
$logFolder = $srv.Settings.DefaultLog;
if ($dataFolder.Length -eq 0 -or $logFolder.Length -eq 0)
{
Write-Host "Secondary Database Log - Data Folder Default Path Empty" -ForegroundColor Red
EXIT
}
$backupDeviceItem = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $DatabaseBackupFile, File;
$restore = new-object 'Microsoft.SqlServer.Management.Smo.Restore';
$restore.Database = $DBNameTarget;
$restore.Devices.Add($backupDeviceItem);
$dataFileNumber = 0;
foreach ($file in $restore.ReadFileList($srv))
{
$relocateFile = new-object 'Microsoft.SqlServer.Management.Smo.RelocateFile';
$relocateFile.LogicalFileName = $file.LogicalName;
if ($file.Type -eq 'D'){
if($dataFileNumber -ge 1)
{
$suffix = "_$dataFileNumber";
}
else
{
$suffix = $null;
}
$relocateFile.PhysicalFileName = "$dataFolder$DBNameTarget$suffix.mdf";
$dataFileNumber ++;
}
else
{
$relocateFile.PhysicalFileName = "$logFolder$DBNameTarget.ldf";
}
$restore.RelocateFiles.Add($relocateFile) | out-null;
}
#$restore.NoRecovery = $TRUE
#$restore.SqlRestore($srv)
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $DatabaseBackupFile -ServerInstance $SecondaryServer\$SecondaryServerInstance -NoRecovery -RelocateFile $restore.RelocateFiles -warningaction silentlycontinue
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LogBackupFile -ServerInstance $SecondaryServer\$SecondaryServerInstance -RestoreAction 'Log' -NoRecovery -ReplaceDatabase -RelocateFile $restore.RelocateFiles -warningaction silentlycontinue
Write-Host "Secondary Database Restored for $DBNameTarget" -ForegroundColor Green
}
}
}
catch
{
Write-Host $_
Write-Host "Unable to Load Database Backed up $SecondaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
# Restore Database to Tertiary
Try
{
if($TertiaryServer)
{
if($TertiaryServerDBExist)
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer\$TertiaryServerInstance -warningaction silentlycontinue
$srv.KillAllProcesses($DBNameTarget)
# Restore Tertiary Replica Database
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $DatabaseBackupFile -ServerInstance $TertiaryServer\$TertiaryServerInstance -NoRecovery -ReplaceDatabase -warningaction silentlycontinue
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LogBackupFile -ServerInstance $TertiaryServer\$TertiaryServerInstance -RestoreAction 'Log' -NoRecovery -ReplaceDatabase -warningaction silentlycontinue
Write-Host "Tertiary Database Restored for $DBNameTarget" -ForegroundColor Green
}
else
{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer\$TertiaryServerInstance -warningaction silentlycontinue
$dataFolder = $srv.Settings.DefaultFile;
$logFolder = $srv.Settings.DefaultLog;
if ($dataFolder.Length -eq 0 -or $logFolder.Length -eq 0)
{
Write-Host "Tertiary Database Log - Data Folder Default Path Empty" -ForegroundColor Red
EXIT
}
$backupDeviceItem = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $DatabaseBackupFile, File;
$restore = new-object 'Microsoft.SqlServer.Management.Smo.Restore';
$restore.Database = $DBNameTarget;
$restore.Devices.Add($backupDeviceItem);
$dataFileNumber = 0;
foreach ($file in $restore.ReadFileList($srv))
{
$relocateFile = new-object 'Microsoft.SqlServer.Management.Smo.RelocateFile';
$relocateFile.LogicalFileName = $file.LogicalName;
if ($file.Type -eq 'D'){
if($dataFileNumber -ge 1)
{
$suffix = "_$dataFileNumber";
}
else
{
$suffix = $null;
}
$relocateFile.PhysicalFileName = "$dataFolder$DBNameTarget$suffix.mdf";
$dataFileNumber ++;
}
else
{
$relocateFile.PhysicalFileName = "$logFolder$DBNameTarget.ldf";
}
$restore.RelocateFiles.Add($relocateFile) | out-null;
}
#$restore.NoRecovery = $TRUE
#$restore.SqlRestore($srv);
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $DatabaseBackupFile -ServerInstance $TertiaryServer\$TertiaryServerInstance -NoRecovery -RelocateFile $restore.RelocateFiles -warningaction silentlycontinue
Restore-SqlDatabase -Database $DBNameTarget -BackupFile $LogBackupFile -ServerInstance $TertiaryServer\$TertiaryServerInstance -RestoreAction 'Log' -NoRecovery -ReplaceDatabase -RelocateFile $restore.RelocateFiles -warningaction silentlycontinue
Write-Host "Tertiary Database Restored for $DBNameTarget" -ForegroundColor Green
}
}
}
catch
{
Write-Exception $_
Write-Host "Unable to Load Database Backed up $TertiaryServer for $DBNameTarget" -ForegroundColor Red
Exit
}
# Add database back into Availability Group
Try
{
if($PrimaryServer) {Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBNameTarget -warningaction silentlycontinue }
if($SecondaryServer) {Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBNameTarget -warningaction silentlycontinue }
if($TertiaryServer) {Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBNameTarget -warningaction silentlycontinue }
Write-Host "Add Database to Availability Group " -ForegroundColor Green
}
catch
{
Write-Exception $_
Write-Host "Unable Add Database to Availability Group"
Exit
}
}
# Check Availability Group Status
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer\$PrimaryServerInstance -warningaction silentlycontinue
$AG = $srv.AvailabilityGroups[$TargetAGName]
$AG.DatabaseReplicaStates|ft -AutoSize
$EndDate = Get-Date
$Time = $EndDate - $StartDate
Write-Host "Results of Script to refresh $DBName on $PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $TargetAGName Time Script ended at $EndDate and took $Time" -ForegroundColor Green