Everyone who has ever installed a SQL Server has also set up a backup procedure (hopefully :))
But, besides backing up your valuable data, how many of you are actually testing what has been backed up? Do you have a disaster recovery procedure?
If you do – how often do you do it? How much time does it take for an engineer to do it?
Recently I started working with a new team, where I found out that Operations have to go through “database restoring” procedure for each client at least 2 times per year – Great 🙂 Good thing to do. But all manually, which takes at least 2 hours per check 🙁 .
Powershell automation to the rescue.
Since we have 2 environments – Production and Acceptance – that are not connected in any way (when it comes to a networking part) I had to find a way of moving all those big backups from one to another.
Since we have an Azure subscription, I decided to create an environment that I will use for this. All I needed was one Service Principal Name (we want to be secure) and Blob storage.
I will not go through the creation of these 2 but need to tell you roles that you need to assign to SPN:
- Storage Account Key Operator Service Role
- Storage Blob Data Contributor
Also, here are some prerequisites that Servers must have:
- The latest version of PowerShell – 5.1 – can be acquired here.
- Installed Az module (install-module Az)
- AZCopy setup locally – added to Windows environment PATH
- CredentialManager Module installed – https://www.powershellgallery.com/packages/CredentialManager/2.0
And finally, the script itself. It is well commented but in case you need some help, just ask 😉
/** <# .Synopsis Copies data to Blob storage .DESCRIPTION It uses SPN to connect to Azure subscription and then moves local data to a target blob storage .EXAMPLE Copy-Local2Blob .NOTES Author: Milos Katinski Twitter: @MilosKatinski Email: milos.katinski@outlook.com Blog: http://tech-trainer.info/ #> function Copy-Local2Blob { [CmdletBinding()] param ( # In this example parameters are not mandatory because I was hardcoding them, but that you can always change # Subscription ID [Parameter(Mandatory = $false, Position = 1, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [ValidateNotNullOrEmpty()] [Alias("Subscription")] [string]$subscriptionId = "YOUR SUBSCRIPTION ID", # Name of the resource group [Parameter(Mandatory = $false, Position = 2, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [ValidateNotNullOrEmpty()] [Alias("Name")] [string]$ResourceGroup = "NAME OF THE RG WHERE STORAGE ACC IS", # Name of the Storage Account [Parameter(Mandatory = $false, Position = 3, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [ValidateNotNullOrEmpty()] [Alias("Storage")] [string]$storageAccountName = "STORAGE ACCOUNT NAME", # Name of the Container [Parameter(Mandatory = $false, Position = 4, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [ValidateNotNullOrEmpty()] [Alias("Container")] [string]$storageContainerName = "CONTAINER NAME" ) BEGIN { # Here I was using the CredentialManager module - listed in server prerequisistes $appCred = Get-StoredCredential -Target "NAME OF THE STORED CRED" Connect-AzAccount -ServicePrincipal -SubscriptionId $subscriptionId -Tenant "YOUR TENANT ID" -Credential $appCred # Number of unique databases that we have in backup location $DBcount = 2 # Here you should enter your local environment data - where you keep backups $backupLocation = "D:\Backup" $tempbackup = "D:\Temp\backup" If (!(test-path $tempbackup)) { New-Item -ItemType Directory -Force -Path $tempbackup } # I like to log most of the things that my scripts do $logLocation = "C:\Scripts\Logs" If (!(test-path $logLocation)) { New-Item -ItemType Directory -Force -Path $logLocation } $LogFile = "C:\Scripts\Logs\BackupJob-$(Get-Date -Format "M-dd-yyyy").Log" # Here we are creating the SAS Token so we could actually access Blob storage $storageAccountKey = (Get-AzStorageAccountKey -ResourceGroupName $ResourceGroup -AccountName $storageAccountName).Value[0] $destinationContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey $containerSASURI = New-AzStorageContainerSASToken -Name $storageContainerName -Context $destinationContext -ExpiryTime(get-date).AddSeconds(3600) -FullUri -Permission rlw } PROCESS { # Filtering backup location for only bak files created in last 24H and that have a specific string in name $statuslocal = @(Get-ChildItem $backupLocation -Recurse -Include $("*.bak") | Where-Object { $_.LastWriteTime -gt $(Get-Date).AddDays(-1) -and $_.Name -like 'INSERT NAME*'}) if ($statuslocal.Count -lt $DBcount) { Write-Verbose "Backup issue found on $env:computername" $ErrorMessage = "SQL Backup files are not found on $env:computername. Please review SQL backup and log files." # Send-EmailSendGrid is our internal helper function - If you don't manage to find example online, send me a request and I will email it to you Send-EmailSendGrid -EmailTo "milos.katinski@outlook.com" -Subject "[Warning] SQL Backup" -Body $ErrorMessage Add-Content -Path $LogFile -Value "$(Get-Date) - One or more backup files not found in $localPath." Add-Content -Path $LogFile -Value "$(get-date) - *** Logging end ***" exit 0 } else { foreach ($file in $statuslocal){ Copy-Item -Path $file.FullName -Destination $tempbackup } Write-Verbose "Moving local data to blob" azcopy copy $tempbackup $containerSASURI --recursive Add-Content -Path $LogFile -Value "$(Get-Date) - Backups are copied to Blob storage." } } END { # Cleanup Remove-Item -Path "D:\Temp\backup" -Recurse -Force Add-Content -Path $LogFile -Value "$(Get-Date) - Temp Backup Storage is cleaned up." Add-Content -Path $LogFile -Value "$(get-date) - *** Logging end ***" } } # Function end */
In the next post, I will cover how to download this data to the Acceptance environment.
Stay tuned …
Cheers!