Restore SQL Database to an AG with Powershell

Article
1/17/2024

#the following powershell script tries to automate the process of restoring SQL Database to an AG (Availability Group)

#Microsoft Dynamics Axapta connects to this Availabilty groups. the script invokes a command to stop the service remotly.

#the script checks for the latest database backup file from a shared location, compares the LastWriteTime to the local database backup file. if the shared backup file

#is newer it copies it to the local location.

#the scripts determines the primary replica and the secondary replicas. remove the database from AG from the primary server. then attempts a database restore as a job

#on all servers at the same time.

#the script then adds the database to the Availability Databases, and starts the service on the AOS for MS Dynamics

#load assemblies

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#read values from XML file AG_Config_v1.0.xml

[xml]$xmlconfigfile = get-content C:\scripts\AG_Config_v1.0.xml

$ag_server1 = $xmlconfigfile.configuration.ag_server1.name

$ag_server2 = $xmlconfigfile.configuration.ag_server2.name

$ag_server3 = $xmlconfigfile.configuration.ag_server3.name

$ag_instance1 = $xmlconfigfile.configuration.ag_server1.instname

$ag_instance2 = $xmlconfigfile.configuration.ag_server2.instname

$ag_instance3 = $xmlconfigfile.configuration.ag_server3.instname

$ag_availabilitygroup = $xmlconfigfile.configuration.ag_availabilitygroup.name

$ag_database = $xmlconfigfile.configuration.ag_database.name

$ag_data_path = $xmlconfigfile.configuration.ag_data_path.name

$ag_log_path = $xmlconfigfile.configuration.ag_log_path.name

$aos_server1 = $xmlconfigfile.configuration.aos_server1.name

$aos_service = $xmlconfigfile.configuration.aos_server1.aos_service.name

$backupfile_loc1 = $xmlconfigfile.configuration.backupfile_loc1.name

$backupfile_loc2 = $xmlconfigfile.configuration.backupfile_loc2.name

$BackupFileShare = $xmlconfigfile.configuration.backupfile_share.name

$db_data_logical = $xmlconfigfile.configuration.db_data_logical.name

$db_log_logical = $xmlconfigfile.configuration.db_log_logical.name

#determine if we have the latest database backup file on the local computer 1, if not copy from shared location

$db_backup_share = gci $BackupFileShare | sort LastWriteTime | select -last 1

$db_backup_file1 = gci $backupfile_loc1| sort LastWriteTime | select -last 1

if ($db_backup_share.LastWriteTime -gt $db_backup_file1.LastWriteTime){

Start-Job -Name cpjob1 -ScriptBlock {Copy-Item $args[0] $args[1]} -ArgumentList $BackupFileShare$db_backup_share,$backupfile_loc1

}

else {

echo "no copy"

}

#if ($db_backup_share.LastWriteTime -gt $db_backup_loc.LastWriteTime) {echo true } else {echo false}

#determine if we have the latest database backup file on the local computer 2, if not copy from shared location

$db_backup_file2 = gci $backupfile_loc2| sort LastWriteTime | select -last 1

if ($db_backup_share.LastWriteTime -gt $db_backup_file2.LastWriteTime){

Start-Job -Name cpjob2 -ScriptBlock {Copy-Item $args[0] $args[1]} -ArgumentList $BackupFileShare$db_backup_share,$backupfile_loc2

}

else {

echo "no copy"

}

#wait on copy jobs to finish.

echo "waiting for copy jobs to complete"

Wait-Job -Name cpjob1

Wait-Job -Name cpjob2

#determine the path of the database backup file. this variable will be used with the restore-sqldatabase cmdlet

#we select the latest backupfile based on the lastwritetime being greater than the current date -1

#we rerun the gci to get the last modified backup file after the copy process.

$db_backup_file1 = gci $backupfile_loc1| sort LastWriteTime | select -last 1

$BackupFilePath = "$backupfile_loc1$db_backup_file1"

#determine the owner node in the WFCS for the SQL AG resource. this will help us to determine the primary and secondary replicas in our AG

#supply the output to $owner1 variable and then get the name of the owner node, also assign the result to varriable $val1

$owner1 = Get-ClusterGroup -name $ag_availabilitygroup | Select-Object OwnerNode

$val1 = $owner1.OwnerNode.Name

#We have 3 servers in our AG, sinthis series of if statements checks the name of the owner node and defines the value of the "SQL instance name" varriable.

#also it determines the secondary 1 and secondary 2 server names and instance names

if ($val1 -like $ag_server1) {

$ServerPrimary = "$ag_server1\ag_instance1"

$Server2nd1 = "$ag_server2\ag_instance2"

$Server2nd2 = "$ag_server3\ag_instance3"

}

else {

if ($val1 -like $ag_server2) {

$ServerPrimary = "$ag_server2\ag_instance2"

$Server2nd1 = "$ag_server1\ag_instance1"

$Server2nd2 = "$ag_server3\ag_instance3"

}

else {

$ServerPrimary = "$ag_server3\ag_instance3"

$Server2nd1 = "$ag_server1\ag_instance1"

$Server2nd2 = "$ag_server2\ag_instance2"

}}

#define the path of the AG, this path will be used with cmdlet add-sqlavailabilitydatabase

$MyAgPrimaryPath = "SQLSERVER:\SQL\ServerPrimary\AvailabilityGroups\ag_availabilitygroup"

$MyAgSecondaryPath1 = "SQLSERVER:\SQL\Server2nd1\AvailabilityGroups\ag_availabilitygroup"

$MyAgSecondaryPath2 = "SQLSERVER:\SQL\Server2nd2\AvailabilityGroups\ag_availabilitygroup"

#stop MS Dynamics AOS service on remote server

Invoke-Command -ComputerName $aos_server1 -ScriptBlock { Get-Service -Name "$($args[0])" | Stop-Service } -ArgumentList $aos_service

#the first step in restoring database from backup file to an AG is to remove the database from the Avaialbility Databases

Remove-SqlAvailabilityDatabase -Path SQLSERVER:\sql\ServerPrimary\availabilitygroups\ag_availabilitygroup\availabilitydatabases\ag_database

#restore the database on the primary replica, from Full Backup File, with Recovery, with over write, and relocate the files. also we want to run the command as a job so we can restore -

#on other servers in the AG at the same time.

Start-Job -InitializationScript {

#load assemblies

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

#Need SmoExtended for backup

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

} -Name JobP -ScriptBlock {

$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $($args[0])

$serverConn.ConnectionContext.StatementTimeout = 0

#the below variables are necessary incase you have you data and log on a different location than the default.

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[5])", "$($arg[3])")

$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[6])", "$($arg[4])")

Restore-SqlDatabase -InputObject $serverConn -Database $($args[2]) -BackupFile "$($args[1])" -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase

} -ArgumentList $ServerPrimary,$BackupFilePath,$ag_database,$ag_data_path,$ag_log_path,$db_data_logical,$db_log_logical

#restore the database on the 1st Secondary replica, from Full Backup File, without Recovery, with over write, and relocate the files. also we want to run the command as a job so we can restore -

#on other servers in the AG at the same time.

Start-Job -InitializationScript {

#load assemblies

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

#Need SmoExtended for backup

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

} -Name JobS1 -ScriptBlock {

$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $($args[0])

$serverConn.ConnectionContext.StatementTimeout = 0

#the below variables are necessary incase you have you data and log on a different location than the default.

$RelocateData2 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[5])", "$($arg[3])")

$RelocateLog2 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[6])", "$($arg[4])")

Restore-SqlDatabase -InputObject $serverConn -Database $($args[2]) -BackupFile "$($args[1])" -NoRecovery -RelocateFile @($RelocateData2,$RelocateLog2) -ReplaceDatabase

} -ArgumentList $Server2nd1,$BackupFilePath,$ag_database,$ag_data_path,$ag_log_path,$db_data_logical,$db_log_logical

#restore the database on the 2nd Secondary replica, from Full Backup File, without Recovery, with over write, and relocate the files. also we want to run the command as a job so we can restore -

#on other servers in the AG at the same time.

Start-Job -InitializationScript {

#load assemblies

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

#Need SmoExtended for backup

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

} -Name JobS2 -ScriptBlock {

$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $($args[0])

$serverConn.ConnectionContext.StatementTimeout = 0

#the below variables are necessary incase you have you data and log on a different location than the default.

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[5])", "$($arg[3])")

$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($args[6])", "$($arg[4])")

Restore-SqlDatabase -InputObject $serverConn -Database $($args[2]) -BackupFile "$($args[1]) " -NoRecovery -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase

} -ArgumentList $Server2nd2,$BackupFilePath,$ag_database,$ag_data_path,$ag_log_path,$db_data_logical,$db_log_logical

#these wait job cmdlets are necessary so that the next set of cmdlets to readd the database to AG databases don't run before the previous jobs are finished.

Wait-Job -Name JobP

Wait-Job -Name JobS1

Wait-Job -Name JobS2

#add the Database to the Availabilty Databases. you run the same command on all nodes in the AG

Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database "$ag_database"

Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath1 -Database "$ag_database"

Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath2 -Database "$ag_database"

#start MS Dynamics AOS service on remote server

Invoke-Command -ComputerName $aos_server1 -ScriptBlock { Get-Service -Name "$($args[0])" | Start-Service } -ArgumentList $aos_service

#below is the xml configuration file content. just copy the text below to notepad then save the file under the name AG_Config_v1.0.xml

<?xml version="1.0" encoding="US-ASCII"?>

<ag_server1>

<name>name of database server</name>

<instname>name of instance</instname>

</ag_server1>

<ag_server2>

<name>name of database</name>

<instname>name of instance</instname>

</ag_server2>

<backupfile_loc1>

<name>location of backup file on server1</name>

</backupfile_loc1>

<backupfile_loc2>

<name>location of backup file on server 2</name>

</backupfile_loc2>

<backupfile_share>

<name>location of the backup share</name>

</backupfile_share>

<ag_availabilitygroup>

</ag_availabilitygroup>

<ag_database>

<name>name of database</name>

</ag_database>

<ag_data_path>

<name>datapath\datafilename.mdf</name>

</ag_data_path>

<ag_log_path>

<name>logpath\logfilename.LDF</name>

</ag_log_path>

<aos_server1>

<name>AX2009TESTDB</name>

<aos_service>

</aos_service>

</aos_server1>

<db_data_logical>

<name>logical data name from backup file</name>

</db_data_logical>

<db_log_logical>

<name>logical log name from backup file</name>

</db_log_logical>

</configuration>

Share via

Restore SQL Database to an AG with Powershell

Additional resources