Following last weeks post on Refreshing A Mirrored Database with PowerShell I thought I would write the script to refresh an Availability Group Database.
An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases.You can read more about Availability groups here
There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with AG. There are other scenarios but this post covers the automation of restoring an Availability Group Database from a backup.
The steps that you need to take to restore an Availability Group Database are
Remove Database from the Availability Group
Restore the Primary Replica Database
Backup the Primary Replica Database Transaction Log
Restore the Secondary and Tertiary Replica Databases with no recovery
Add the Database back into the Availability Group
Resolve Orphaned Users – Not covered in this script
Check the status
Here is my set up for this post
I have 3 servers SQL2012SER08AG1, SQL2012SER08AG2 and SQL2012SER08AG3 with 3 databases in an Availability Group called AG_THEBEARD1. SQL2012SER08AG2 is set up as a secondary replica using Synchronous-Commit Mode SQL2012SER08AG3 is set up as a read only replica using Asynchronous-Commit Mode. I have three databases in my Availability Group and today I shall use the database called TestDatabase (I have no imagination today!) to demonstrate the refresh
The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.
First we will remove the database from the Availability Group. This is achieved using the Remove-SqlAvailabilityDatabase CMDLet
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Next Restore the Primary Replica Database, Backup the Primary Replica Database Transaction Log
and Restore the Secondary and Tertiary Replica Databases with no recovery using Restore-SqlDatabase and Backup-SqlDatabase (You can also use the SMO method in the previous post if you wish)
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $PrimaryServer -ReplaceDatabase # Backup Primary Database Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log' # 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 # 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
Then add the database back to the Availability Group
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName
Finally test the status of the Availability Group
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer $AG = $srv.AvailabilityGroups[$AGName] $AG.DatabaseReplicaStates|ft -AutoSize
I also like to add some output to show the progress of the script. This can be logged using Out-File or displayed on the screen using Out-Host.
$EndDate = Get-Date $Time = $EndDate - $StartDate Write-Host " ########################################## Results of Script to refresh $DBName on $PrimaryServer , $SecondaryServer , $TertiaryServer on AG $AGName Time Script anded at $EndDate and took $Time " -ForegroundColor Green
Here are the results of my script
Here is the script
<#
.NOTES
Name: Availability Group Refresh
Author: Rob Sewell http://sqldbawithabeard.com
.DESCRIPTION
Refreshes an Availbaility group database from a backup
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 = "SQL2012Ser2012" # The Load Server
$Date = Get-Date -Format ddMMyy
$PrimaryServer = "SQL2012SER08AG1" # The Primary Availability Group Server
$SecondaryServer = "SQL2012SER08AG2" # The Secondary Availability Group Server
$TertiaryServer = "SQL2012SER08AG3" # The Tertiary Availability Group Server
$AGName = "AG_THEBEARD1" # Availability Group Name
$DBName = "TestDatabase" # Database Name
$LoadDatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\LoadTestDatabase" + $Date + ".bak" # Load database Backup location - Needs access permissions granted
$DatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\TestDatabase" + $Date + ".bak" # database Backup location - Needs access permissions granted
$LogBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\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
cd SQLSERVER:\SQL\$SecondaryServer\DEFAULT
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
cd SQLSERVER:\SQL\$TertiaryServer\DEFAULT
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
cd SQLSERVER:\SQL\$PrimaryServer\DEFAULT
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 $DatabaseBackupFile -ServerInstance $PrimaryServer
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
cd SQLSERVER:\SQL\$PrimaryServer
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
Pingback: (SFTW) SQL Server Links 05/09/14 - John Sansom
Thanks this was a nice script. I re-created Robs script below that will target all databases on a instance and refresh or create on a target availability group. http://poshcode.org/5853
Nice script, by coincidence I was doing the
exact same thing myself today!
Hi Mr. Beard,
Thank you so much for this script and documentation. Last year we upgraded our production environment from a single point of failure to a clustered 2012 AG, but no one worked through the process to routinely refresh our staging and dev environments. I’ve had success providing monthly refreshes using this procedure with some other job steps, but I’ve run into an error now the new staging cluster is being actively used.
The output from the SQL Job step indicates that after removing the DBs from the AG, the first restore command fails because the DB is in use:
Executed as user: DOMAIN\StageSQL-SQLAgent. A job step received an error at line 34 in a PowerShell script. The corresponding line is ‘Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $PrimaryServer -ReplaceDatabase’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. ‘. Process Exit Code -1. The step failed.
Your examples appear to be refreshing databases that would be in use, so I’m not sure why this would happen. Did I miss something you used in a previous post, or is there a flag or command that I could include to avoid this in the future?
When the process failed, I ended up trying to modify the script to use single_user mode, but that just created more of a hassle for my troubleshooting. Ultimately, I just temporarily disabled the user accessing the database and completed the process. I’d be thrilled to hear how you would handle this situation.
Hi Justin,
I’m glad that this script has been of use.
$srv.KillAllProcesses($dbname)
Can fail when a busy service is the one doing the connecting as it will connect between the end of the kill and before the restore starts . You have two options of the top of my head
Stop the process that is calling the database by stopping the app pool or stopping the service on the remote machine in the line before the restore or check for processes in a while loop with logic like if processes -eq 0 then restore in it
Let me here how you get on
Rob
I notice you only specify $srv.KillAllPorcesses($dbname) prior to the Secondary and Tertiary servers in your example. I think my failure is in the steps prior to that, when attempting to restore a recent production backup to the Primary server. Is there a reason you didn’t kill connections on the Primary after removing the Availability Databases?
None at all other than I knew there were no external processes connecting to the db as they had been stopped. Definitely add that code prior to the restore and let me know how you get on
Sorry for the long delay–I had to wait for the next monthly automated refresh to occur to be sure it solved the error. With the number and size of the databases involved and some other steps in the job, it’s a ~7hour process from start to finish and it requires a maintenance window. I’m happy to report adding $srv.KillAllProcesses($dbname) right after removing the AG databases and prior to the PrimaryServer restore was exactly what was missing. It wasn’t ever an issue in the development and test environment, but they don’t usually see active connections like staging. Thanks again for your help and this awesome resource.
Excellent, glad it worked
Rob
Hi
Thank you for a great script which is just what I needed. I’ m dealing with a quite big db and therefore I would be interested in the restore jobs to be executed in parallel on the secondary and tertiary to reduce execution time. FYI the backup files are copied to different locations to speed up restore time. But can I alter the script to execute in parallel?
Is this even possible and if so any ideas would be much appreciated.
Br. Zoran
Hi Zoran, That’s an excellent question. I haven’t tested this code and am writing on a phone so its not complete but I would think that you could use foreach -parallel by adding something like
Workflow Restore-ParallelDatabases {
foreach -parallel ($Server in $OtherServers) {
# Restore Database
Restore-SqlDatabase -Database $DBName -BackupFile $BackupFile -ServerInstance $Server -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $Server -RestoreAction ‘Log’ -NoRecovery -ReplaceDatabase
}
}
or you could use background Jobs – I am not so proficient in this but it would be something like
foreach$Server in $OtherServers {
Start-Job {
# Restore Database
Restore-SqlDatabase -Database $DBName -BackupFile $BackupFile -ServerInstance $Server -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $Server -RestoreAction ‘Log’ -NoRecovery -ReplaceDatabase
} -arg $Server
}
and then get the job status with Receive-Job
Please let me know how you get on
Rob
Hi Rob
First of all thank you for your quick response. You guided me in the right direction.
However I have some difficulties to get the workflow foreach – parallel to work.
This is what I have come up with so far – bear in mind that I am knew at this.
The code below works perfectly when only using the foreach statement but as soon as I uncomment the workflow foreach – parallel nothing happens.
So what am I doing wrong within the workflow? 🙂
Br. Zoran
# 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
#workflow restore-workflow
# {
$Servers = ‘STANDALONE03\i01′,’STANDALONE05\i01’
$DBName = ‘workflow’
$LoadDatabaseBackupFile = “\\STANDALONE03\backup\workflow.bak”
# The servers are processed in parallel.
ForEach ($Server in $Servers)
#ForEach -parallel ($Server in $Servers)
{
# The restore a database from backup.
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $Server -ReplaceDatabase
}
# }
Obviously that code would be in place of the secondary and tertiary restores
Hi, Thanks for your script. Really good. I’m running in a AG 2k16, and I’m receiving this error:
Backup Files removed
Remove-SqlAvailabilityDatabase : Availability-group DDL operations are permitted only when you are using the master
database. Run the USE MASTER command, and retry your availability-group DDL command.
At C:\Temp\test.ps1:60 char:1
+ Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Remove-SqlAvailabilityDatabase], SqlException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.RemoveSqlAvailabilityGrou
pDatabaseCommand
Secondary Removed from Availability Group
Primary Database Restored
Primary Database Backed Up
Secondary Database Restored
Add-SqlAvailabilityDatabase : Availability-group DDL operations are permitted only when you are using the master
database. Run the USE MASTER command, and retry your availability-group DDL command.
At C:\Temp\test.ps1:119 char:1
+ Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBNam …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Add-SqlAvailabilityDatabase], SqlException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDa
tabaseCommand
Database Added to Availability Group
Do you have any idea?
I commented the line 17 and 18 of your script.
# 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
—– FULL SCRIPT
# 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
# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)
$LoadServer = “CQCBOU1WVDEWS01” # The Load Server
$Date = Get-Date -Format ddMMyy
$PrimaryServer = “server1” # The Primary Availability Group Server
$SecondaryServer = “server2” # The Secondary Availability Group Server
#$TertiaryServer = “SQL2012SER08AG3” # The Tertiary Availability Group Server
$AGName = “DevAG2K16” # Availability Group Name
$DBName = “TestDatabase” # Database Name
$LoadDatabaseBackupFile = “\\server1\F$\MSSQL\Backup\TestDatabase” + $Date + “.bak” # Load database Backup location – Needs access permissions granted
$DatabaseBackupFile = “\\server1\F$\MSSQL\Backup\TestDatabase” + $Date + “.bak” # database Backup location – Needs access permissions granted
$LogBackupFile = “\\server1\F$\MSSQL\Backup\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
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
cd SQLSERVER:\SQL\$SecondaryServer\DEFAULT
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
#cd SQLSERVER:\SQL\$TertiaryServer\DEFAULT
#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
cd SQLSERVER:\SQL\$PrimaryServer\DEFAULT
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 $DatabaseBackupFile -ServerInstance $PrimaryServer
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
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction ‘Log’ -NoRecovery
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
cd SQLSERVER:\SQL\$PrimaryServer
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
on AG $AGName
Time Script ended at $EndDate and took
$Time
” -ForegroundColor Green
—–
Thanks,
Bruno