PoshCode Archive  Artifact [f994826f64]

Artifact f994826f64c0a366713d468f28c6ff9edb25cad4a3fc693192bc4d4ebe069e31:

  • File Refresh-an-AG-DB.ps1 — part of check-in [8327949223] at 2018-06-10 13:54:59 on branch trunk — Refreshes an Availability group database from a backup taken from a Load Server (user: Rob Sewell http size: 6193)

# encoding: utf-8
# api: powershell
# title: Refresh an AG DB
# description: Refreshes an Availability group database from a backup taken from a Load Server
# version: 0.1
# type: script
# author: Rob Sewell http
# license: CC0
# x-poshcode-id: 5412
# x-archived: 2016-03-22T01:43:46
# x-published: 2016-09-08T19:43:00
#
# YOU WILL NEED TO RESOLVE ORPHANED USERS IF REQUIRED
#
<#

    .NOTES 
    Name: Availability Group Refresh
    Author: Rob Sewell http://sqldbawithabeard.com
    
    .DESCRIPTION 
        Refreshes an Availability group database from a backup taken from a Load Server

        YOU WILL NEED TO RESOLVE ORPHANED USERS IF REQUIRED
#> 

## http://msdn.microsoft.com/en-gb/library/hh213078.aspx#PowerShellProcedure
# http://msdn.microsoft.com/en-us/library/hh213326(v=sql.110).aspx
cls

# 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

$LoadServer = "" # The Load Server 

$Date = Get-Date -Format ddMMyy
$PrimaryServer = "" # The Primary Availability Group Server
$SecondaryServer = "" # The Secondary Availability Group Server
$TertiaryServer = "" # The Tertiary Availability Group Server
$AGName = "" # Availability Group Name
$DBName = "" # Database Name

$LoadDatabaseBackupFile = "\LoadTestDatabase" + $Date + ".bak" # Load database Backup location - Needs access permissions granted
$LogBackupFile = "\TestDatabase" + $Date + ".trn" # database Backup location - Needs access permissions granted

# Path to Availability Database Objects
$MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgSecondaryPath = "SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgTertiaryPath = "SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName"

$StartDate = Get-Date
Write-Host "
##########################################
Results of Script to refresh $DBName 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}
If(Test-Path $DatabaseBackupFile){Remove-Item -Path $DatabaseBackupFile}
If(Test-Path $LogBackupFile ) {Remove-Item -Path $LogBackupFile }

Write-Host "Backup Files removed" -ForegroundColor Green

# Remove Secondary Replica Database from Availability Group to enable restore
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName 

Write-Host "Secondary Removed from Availability Group" -ForegroundColor Green

# Remove Tertiary Replica Database from Availability Group to enable restore
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

Write-Host "Tertiary removed from Availability Group" -ForegroundColor Green

# Remove Primary Replica Database from Availability Group to enable restore
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

Write-Host "Primary removed from Availability Group" -ForegroundColor Green

# Backup Load Database
Backup-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $LoadServer

Write-Host "Load Database Backed up" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
$srv.KillAllProcesses($dbname)

# Restore Primary Replica Database from Load Database
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $PrimaryServer -ReplaceDatabase

Write-Host "Primary Database Restored" -ForegroundColor Green

# Backup Primary Database
Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log'

Write-Host "Primary Database Backed Up" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer
$srv.KillAllProcesses($dbname)

# Restore Secondary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile-ServerInstance $SecondaryServer -NoRecovery -ReplaceDatabase 
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Write-Host "Secondary Database Restored" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer
$srv.KillAllProcesses($dbname)

# Restore Tertiary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $TertiaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $TertiaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Write-Host "Tertiary Database Restored" -ForegroundColor Green

# Add database back into Availability Group
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName 

Write-Host "Database Added to Availability Group " -ForegroundColor Green

# Check Availability Group Status
 $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
    $AG = $srv.AvailabilityGroups[$AGName]
    $AG.DatabaseReplicaStates|ft -AutoSize

    $EndDate = Get-Date
    $Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script ended at $EndDate and took
$Time

" -ForegroundColor Green