Do you check your backups?

Question

Post reply

Do you check your backups?

higgim

SSCarpal Tunnel

Points: 4090
More actions
April 18, 2018 at 11:50 pm

#330508

Comments posted to this topic are about the item Do you check your backups?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

sqlfriend SSC Guru Points: 52781 More actions · Answer 1

Thanks for the article! Great information. Could you share the Powershel script? Thanks

iwem Valued Member Points: 70 More actions · Answer 2

Regarding the cost, don't ask if you can afford it, ask if you can afford not to
Cheers

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 3

sqlfriends - Wednesday, April 18, 2018 11:56 PM
Thanks for the article! Great information. Could you share the Powershel script? Thanks

Here you go. May need a bit of tweaking.

# CopyBackupFilesToAzure # # You need to supply the following mandatory parameters # # SourceDirectory - The directory where backups are kept. E.g E:\BACKUP\MyserverName # ServerName - The name of the server / instance # BackupType - Backuptype to copy (Options are FULL, LOG, DIFF, MySQL) # StorageAccountName - The name of the azure storage account # StorageAccountKey - The key to access the storage account # # Usage # # .\CopyBackupFilesToAzure -SourceDirectory "E:\Backup\MyServerName" -ServerName "MyServer" -BackupType "LOG" -StorageAccountName "myazurestorage" -StorageAccountKey "mykey"

param ( [Parameter(Mandatory=$true)] [string]$SourceDirectory, [Parameter(Mandatory=$true)] [string]$ServerName, [Parameter(Mandatory=$true)] [string]$BackupType, [Parameter(Mandatory=$true)] [string]$StorageAccountName, [Parameter(Mandatory=$true)] [string]$StorageAccountKey )

FUNCTION Generate-StorageContext { param ( [Parameter(Mandatory=$true)] [string]$StorageAccountName, [Parameter(Mandatory=$true)] [string]$StorageAccountKey

)

$TryCounter = 0 $StorageContext = $null WHILE ($TryCounter -lt 3 -and !$StorageContext) { $StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey -ErrorAction Stop $TryCounter = $TryCounter + 1 if (!$StorageContext) { Start-Sleep -s 45 } } Return $StorageContext

}

Function CheckForCreateContainer { param ( [Parameter(Mandatory=$true)] [object]$SContext, [Parameter(Mandatory=$true)] [string]$SContainer )

$ContainerNotThere = $null $null = Get-AzureStorageContainer -Context $SContext -Name $SContainer -ErrorAction SilentlyContinue -ErrorVariable ContainerNotThere

if ($ContainerNotThere) { $null = New-AzureStorageContainer -Name $SContainer -Context $SContext -ErrorAction SilentlyContinue Start-Sleep -Seconds 10 $ContainerNotThere = $null $null = Get-AzureStorageContainer -Context $SContext -Name $SContainer -ErrorAction SilentlyContinue -ErrorVariable ContainerNotThere } if ($ContainerNotThere) { $ContainerStatus = "Unable to find / create container" } else { $ContainerStatus = "Exists" }

Return $ContainerStatus }

Function CheckForAndTranferFileToBlobStorage { param ( [Parameter(Mandatory=$true)] [object]$SContext, [Parameter(Mandatory=$true)] [string]$SContainer, [Parameter(Mandatory=$true)] [string]$SFileName, [Parameter(Mandatory=$true)] [string]$SBackupURI, [Parameter(Mandatory=$false)] [boolean]$SOverWrite = $false

)

[object] $FileDoesNotExist if ($FileDoesNotExist) { $FileDoesNotExist.clear() }

$null = Get-AzureStorageBlob -Blob $SBackupURI -Container $SContainer -Context $SContext -ErrorAction SilentlyContinue -ErrorVariable FileDoesNotExist if ($FileDoesNotExist -or $OverWrite) { if ($CopyToAzureError) { $CopyToAzureError.clear() } if($SOverWrite) { $null = Set-AzureStorageBlobContent -File $SFileName -Container $SContainer -Blob $SBackupURI -Context $SContext -Force -ErrorAction SilentlyContinue -ErrorVariable CopyToAzureError } else { $null = Set-AzureStorageBlobContent -File $SFileName -Container $SContainer -Blob $SBackupURI -Context $SContext -ErrorAction SilentlyContinue -ErrorVariable CopyToAzureError }

if($CopyToAzureError) { Return "Copy Error [$SBackupURI] to container [$SContainer]" } else { Return "Success" } } else { Return "File In Storage" } }

#------------------------------------------------------ START ------------------------------------------------------#

$ProgressPreference = â€™SilentlyContinueâ€™

#Check we have one of the allowed backup types if($BackupType -notin "FULL", "DIFF", "LOG", "MySQL") { Write-Output "ERROR - You can only specify FULL, DIFF or LOG for BackupType" Exit 1 }

$ContainerName = $ServerName.ToLower() + "-" + $BackupType.ToLower()

#Generate the storage context Write-Output "Generating Storage Context For Storage Account [$StorageAccountName]" $StorageContext = Generate-StorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

#Check to see if the container exists for the storage account. If not, create it. Write-Output "Checking for container [$ContainerName] in Storage Account [$StorageAccountName] (Will attempt create if container not present)" $ContainerStatus = CheckForCreateContainer -SContext $StorageContext -SContainer $ContainerName

if ($ContainerStatus -notin "Exists","Created") { Write-Output "-----------------------------------------------------------------------------------" Write-Output "Container does not exist for the specified storage account and could not be created" Write-Output "-----------------------------------------------------------------------------------" Exit 1 }

#Set the fileextention and directory parts switch ($BackupType) { FULL {$FileExtension = '.bak'} DIFF {$FileExtension = '.bak'} LOG {$FileExtension = '.trn'} MYSQL {$FileExtension = '.SQL'} }

switch ($BackupType) { FULL {$DirectoryPart = '*\FULL'} DIFF {$DirectoryPart = '*\DIFF'} LOG {$DirectoryPart = '*\LOG'} MYSQL {$DirectoryPart = '*\MySQL*'} }

#Get the list of files based on the type of backup file and backup type $FileListing = Get-ChildItem -Path $SourceDirectory -Recurse | where {!$_.psIsContainer -and $_.Extension -eq "$FileExtension" -and $_.DirectoryName -like $DirectoryPart}

#Get file listing, check if in Azure storage account and tranfer if not there foreach ($File in $FileListing) { $SourceFileName = $File.FullName $StorageAccountSubDirectory = (((($File.DirectoryName.Replace("$SourceDirectory","")).SubString(1).ToLower()) -replace "\\","") -replace $BackupType,"") $StorageURI = ($StorageAccountSubDirectory + "`\" + ($File.Name)).replace("\\","\$BackupType\").ToLower() $FileCopyStatus = CheckForAndTranferFileToBlobStorage -SContext $StorageContext -SContainer $ContainerName -SFileName $SourceFileName -SBackupURI $StorageURI Write-Output "Processing File [$SourceFileName] : Status [$FileCopyStatus]" }

Clive Strong SSChampion Points: 11222 More actions · Answer 4

Nice, we don't use Azure, but backups are tested regularly for creating internal data sets as well as scheduled tests from tape.

As well as verifying the backups can be restored, it's also useful to test your own ability to restore rather than the automated verification. Under pressure, you'll be glad you can put together all the restore commands!

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 5

Thank you very much for this article.
Would it be possible for you to post a general ball-park estimate of the costs ?

Just something like x$ per database per GB per month

TIA
Henrik

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 6

henrik staun poulsen - Thursday, April 19, 2018 3:31 AM
Thank you very much for this article.
Would it be possible for you to post a general ball-park estimate of the costs ?
Just something like x$ per database per GB per month
TIA
Henrik

Hi

For the virtual machine testing the backups in the UK with SQL Standard and a Premium 128GB managed OS disk running an L4 VM (4Core, 32GB Ram, 678GB SSD Temp storage) running on average 104 minutes per day it comes out at around Â£60 per month.

For our backup storage this depends. Based on a 5GB database backup and keeping 31 daily backups, 104 weekly backups and 96 monthly backups we would be looking at Â£18 per month. This is stored using the Hot Tier. When the archive tier comes to UK storage accounts we are estimating around a 60% reduction in costs as we will make use of the Cool and Archive Tiers.

k.schouten Mr or Mrs. 500 Points: 526 More actions · Answer 7

We do manual restore tests.
I wrote a script to random select the database and the point in time to restore. Otherwise we might select the databases that are easy to restore, or only the most important databases.

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 8

k.schouten - Thursday, April 19, 2018 4:11 AM
We do manual restore tests.
I wrote a script to random select the database and the point in time to restore. Otherwise we might select the databases that are easy to restore, or only the most important databases.

We tend to restore all our databases on a daily basis to ensure all our daily backups for all our databases are able to be restored. I get a good old manual restore help reassure you the automation is doing it's job 🙂

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 9

hi,

Thank you very much for your reply. Not massively expensive, so it might be an attractive solution for my manager.

Best regards
Henrik

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 10

We are finding it very cost effective and even more so once the Archive tier comes into play. More cost effective than a NAS / File Server, backups are off site and 3 copies held locally in the Azure DC (Standard LRS)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

We use Commvault. I have a few scripts that check backupset to make sure everything is backed up. We do test restores every so often and Commvault verifies that the backups are good.