SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do you check your backups?


Do you check your backups?

Author
Message
higgim
higgim
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3045 Visits: 2893
Comments posted to this topic are about the item Do you check your backups?
sqlfriends
sqlfriends
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37206 Visits: 4604
Thanks for the article! Great information. Could you share the Powershel script? Thanks
iwem
iwem
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
Regarding the cost, don't ask if you can afford it, ask if you can afford not to
Cheers
higgim
higgim
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3045 Visits: 2893
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
Clive Strong
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7105 Visits: 6669
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
henrik staun poulsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4401 Visits: 1296
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
higgim
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3045 Visits: 2893
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
k.schouten
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 114
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
higgim
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3045 Visits: 2893
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 Smile

henrik staun poulsen
henrik staun poulsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4401 Visits: 1296
hi,

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

Best regards
Henrik




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search