SQL server 2014 Log Shipping Manual Fail Over

sql-failover

Function Failover{ 

[CmdletBinding()]
Param (
       [Parameter(Mandatory=$True)] #Enforce the use of id 
	   [string]$id
      )

# Variables declaration
$Master = "PRIMARY"
$Slave = "SECONDARY"
$SourcePath = "c:\scriptslib"
$DRMasterfolder = "C:\LogShippingSECONDARYToPRIMARY"
$DRSlavefolder = "C:\LogShippingSECONDARYToPRIMARY"
$LSiddir = "Microsoft.PowerShell.Core\FileSystem::\\$Master\LogShippingPRIMARYToSECONDARY\user_$id"

# Create clients's Disaster Recovery (DR) folder on primary server
$DRiddir2 = New-Item -Path $DRSlavefolder -name "user_$id" -ItemType Directory -ErrorAction Continue
    Write-Host "user_$id folder for Disaster Recovery created on $Slave" -ForegroundColor Black -BackgroundColor Green
# Create clients's Disaster Recovery (DR) folder on secondary server
$DRiddir = New-Item -Path "Microsoft.PowerShell.Core\FileSystem::\\$Master\LogShippingSECONDARYToPRIMARY" -name "user_$id" -ItemType Directory -ErrorAction Continue
    Write-Host " Client $id folder for Disaster Recovery created on $Master " -ForegroundColor Black -BackgroundColor Green

  		
# Getting all sql files and copy them to the destination folder
$gflz = Get-ChildItem $SourcePath\* -Include __04*, __05*, __06*, __07*, __10* -ErrorAction Stop
# Copy command from source library to newly created folder
Copy-Item $gflz $DRiddir2 -ErrorAction Stop
    Write-Host " SQL files copy succeed. " -ForegroundColor Black -BackgroundColor Green

# Replace user_clientiddb, PRIMARY et 2, paths 
$cpflz = @(Get-ChildItem -Force $DRiddir2\* -Include *.sql)
    if ($cpflz -ne $null) {               
        Foreach ($flz in $cpflz) {
            (Get-Content $flz.PSPath) | ForEach-Object { $_ -replace 'user_clientid', "user_$id" `
            -replace 'path', "$DRMasterfolder" -replace 'vpth', "$DRSlavefolder" } | Set-Content $flz.PSPath
        }
        Write-Host " SQL files modified" -ForegroundColor Black -BackgroundColor Green 
     }
     Else { Write-Host " None files were modified to match $id " -ForegroundColor Black -BackgroundColor Red }

# Edited SQL script copy to secondary(TEST/DR) server
$scptrn = Get-ChildItem $DRiddir2
Copy-Item $scptrn.FullName $DRiddir -ErrorAction Continue
    Write-Host " SQL files copy succeed on $Master " -ForegroundColor Black -BackgroundColor Green

# Disable backup LogShipping job on primary (PROD DB) 
Invoke-Sqlcmd -ServerInstance "$Master" -Username 'ls_test' -Password 'Azerty123456' -InputFile "$DRiddir\__07_DR_Disable_BackupJob.sql" -ErrorAction Stop
    Write-Host " Log Shipping Backup job Disabled on $Master " -ForegroundColor Black -BackgroundColor Green

# FailOver - Get the newest Transaction log file.
(Set-Location $DRSlavefolder)
($latesttrn = Get-ChildItem $LSiddir -Filter *.trn | sort LastWriteTime | select -Last 1)
$rlatesttrn = Rename-Item $latesttrn.FullName user_$id.trn -ErrorAction Stop
Copy-Item $rlatesttrn $DRiddir2 -ErrorAction Stop
    Write-Host " The latest user_$id.trn copy successful to $Slave " -ForegroundColor Black -BackgroundColor Green 
	
# Put user_clientid on primary server (PROD DB) offline.
Invoke-Sqlcmd -ServerInstance "$Master" -Username 'ls_test' -Password 'Azerty123456' -InputFile "$DRiddir2\__10_DR_TailLogs_Backup.sql"
    Write-Host "user_$id is now OFFLINE on $Master " -ForegroundColor Black -BackgroundColor Green

# Put user_clientid on secondary (TEST/DR DB) online.
Invoke-Sqlcmd -ServerInstance "(local)" -InputFile "$DRiddir2\__10_DR_TailLogs_Restore.sql"
    Write-Host "user_$id is now ONLINE on server $Slave " -ForegroundColor Black -BackgroundColor Green

# Disable Copy and restore LogShipping jobs on secondary ( TEST/DR DB)
(Set-location $DRiddir2)
Invoke-Sqlcmd -ServerInstance "(local)" -InputFile "$DRiddir2\__07_DR_Disable_CopyJob.sql" -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance "(local)" -InputFile "$DRiddir2\__07_DR_Disable_RestoreJob.sql" -ErrorAction Stop
    Write-Host " Log Shipping jobs disabled on $Slave" -ForegroundColor Black -BackgroundColor Green

# Create transaction logs backup job on for the new primary database (which is normaly the customer's TEST/DR DB)
Invoke-Sqlcmd -ServerInstance "(local)" -InputFile "$DRiddir2\__04_On_Secondary_DR_Backup_Job.sql " -ErrorAction Stop
    Write-Host " Backup job for user_$id on $Slave is up and running " -ForegroundColor Black -BackgroundColor Green 

# Create and enable Copy and Restore (DR) job on secondary database (Which is normaly the customer's PROD DB )
Invoke-Sqlcmd -ServerInstance "$Master" -Username 'ls_test' -Password 'Azerty123456' -InputFile "$DRiddir\__05_On_Primary_DR_Copy_Job.sql " -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance "$Master" -Username 'ls_test' -Password 'Azerty123456' -InputFile "$DRiddir\__06_On_Primary_DR_Restore_Job.sql " -ErrorAction Stop
    Write-Host " Copy and Restore job for user_$id on $Master are up and running " -ForegroundColor Black -BackgroundColor Green 
}

Failover -id 17h12frpp # To test the function