Сохранение SQL Backup в облачном хранилище

Постановка задачи:

Имеем SQL-базы с настроенными планами обслуживания по созданию бэкапов (полных и желательно плюс дифференциальных). Срок хранения бэкапов настроен, например, на месяц, может больше, зависит от количества свободного для бэкапов места на сервере. Нужно на экстренный случай (пожар в серверной или во всем офисе, кража или порча серверного оборудования) иметь бэкап в облачном хранилище и желательно все это бесплатно (без абонентской платы за хранилище).

Решение:

Так как бесплатные облачные хранилища не могут похвастаться безразмерным свободным пространством, выделенным каждому пользователю, то вариант настроить бэкапы в папку, синхронизируемую с облачным хранилищем, нам не подойдет. Бэкапы за месяц туда не влезут. Нам нужно как-то определять последние бэкапы из всех файлов бэкапов для каждой базы и копировать его в отдельную папку, которая уже автоматически или задачей Windows Task или SQL Job будет синхронизироваться с облаком.

Также разумно настроить бэкап следующим образом. Полный бэкап – ночью раз в неделю или месяц, разностный (дифференциальный бэкап) каждую ночь и бэкап хвоста транзакций – каждый час. Тогда не нужно будет каждый день выгружать в облако полный бэкап, что сэкономит время и трафик. Для экстренного восстановления последней копии базы за вчерашний день, нам необходим только последний полный бэкап и один последний разностный бэкап, снятый позднее полного. Так что в облаке нам нужно хранить для каждой базы один полный бэкап и один разностный.

Создаем в SQL задачу (job) пишем в ней скрипт, который выполнит описанные выше действия и ставим ее в плане обслуживания следующей после задачи снятия бэкапов.

Скрипт можно написать на PowerShell, задания SQL поддерживают этот тип скриптов. При этом те, кто знакомы с написанием скриптов на PowerShell, знают, что для запуска скрипта из оболочки PowerShell или из планировщика задач нужно исполнить танец с бубном, чтобы подписать сертификатом этот скрипт, или понизить уровень безопасности системы для разрешения запуска неподписанных скриптов, что крайне нежелательно. Без этого эти скрипты не запускаются. Но в SQL jobs код скрипта PowerShell прекрасно выполняется без всяких подписей.

В результате в папке, которую мы позже настроим на синхронизацию с облачным хранилищем, мы будем иметь последние бэкапы всех нужных нам баз. Имя файлов бэкапов будет соответствовать имени базы с суффиксом, обозначающим тип бэкапа. Таким образом каждый более свежий бэкап будет перезаписывать прошлый бэкап в этой папке, сохраняя в ней свободное место.

Для синхронизации локальной папки с облачным хранилищем я использовал условно бесплатную программу AllwaySync, которая умеет работать с различными облачными хранилищами (GoogleDrive, Dropbox, SkyDrive, Amazon и др.). Настройка синхронизации там интуитивно понятна, и рассказывать о ней я не буду. Единственно, что не нравится мне в этой программе, это то что она не перезаписывает файлы в облаке, а сначала удаляет, а потом записывает новый файл на место старого. Таким образом стандартная система версий файлов гугл диска не видит версии одного файла бэкапа. Конечно в программе есть своя поддержка сохранения версий файлов, но она просто копирует файлы в отдельную папку хранилища перед тем как записать новый, при этом расходуя свободное пространство хранилища. Если бы файл перезаписывался, то не расходуя место хранилища можно было бы иметь еще и историю бэкапов за последние 30 дней (на Google Drive и в Dropbox версионирование файлов поддерживается в пределах месяца).

После того как синхронизация настроена, заходим в настройки автоматической синхронизации, выбираем опцию «Использовать планировщик задач Windows» и из созданной задачи копируем строку запуска синхронизации вида («C:Program FilesAllway SyncBinsyncappw.exe» -e -m -l -s «41F5E385D231E69F376C3DA7A3C650B9»). Задачу планировщика не сохраняем, и галочку «Использовать планировщик» убираем, т.к. у нас все будет делать SQLAgent.

В наш Job со скриптом копирования бэкапов добавляем следующий шаг с типом CmdExec и вставляем туда строку запуска синхронизации через AllwaySync.

Так как сервис агента запускается под пользователем SQLSERVERAGENT, то необходимо скопировать файлы настроек AllwaySync  из папки пользователя, под которым мы делали настройки в такую же папку пользователя SQLАгента (C:UsersSQLSERVERAGENTAppDataRoamingSyncAppSettings\_SYNCAPP)

Осталось только назначить расписание выполнения задачи или включить ее в цепочку плана обслуживания следующей после выполнения бэкапов.

Код скрипта PowerShell:

#dest - Путь к папке, куда будут копироваться файлы бэкапов
#dbases - Список баз данных через запятую, без пробелов
$dest = "C:Dropbox"
$dbases = "Trade,Account"

if (-not (Test-Path $Dest))
{
Write-Warning("Destination folder is not found")
break
}

$sqlbases = "'" + $dbases.Replace(",","','") + "'"

$query = @“
SELECT
b.database_name AS DBName,
b.Type,
media.Physical_Device_name As FileName
FROM (SELECT
lb.database_guid,
MAX(lb.Backup_finish_date) AS Backup_finish_date,
lb.[type]
FROM
msdb..backupset lb
WHERE
lb.database_name IN ($sqlbases)
AND lb.[type] IN ('D','I')
GROUP BY
lb.database_guid,
lb.[type]) LastBackup
INNER JOIN msdb..backupset b ON b.Backup_finish_date = LastBackup.Backup_finish_date AND b.database_guid = LastBackup.database_guid
INNER JOIN msdb.dbo.backupmediafamily media ON b.media_set_id = media.media_set_id
ORDER BY
b.database_name,
b.Type
"@

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=(local);Database=msdb;Integrated Security=True;"
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

$connection.Close()

foreach ($Row In $table.Rows)
{ 
    $SourceFileName = $Row.FileName
    if (-not (Test-Path $SourceFileName -PathType Leaf))
    {
        Write-Warning("File: " + $SourceFileName + " is not found")
        Continue
    }
       
    if ($Row.Type -eq "D") {$Type = "_Full.bak"} else {$Type = "_Incr.bak"}

    $DestFileName = $dest + "" + $Row.DBName + $Type

    
    $SourceFile = Get-ChildItem -Path $SourceFileName
    if (Test-Path $DestFileName -PathType Leaf)
    {
        $DestFile = Get-ChildItem -Path $DestFileName
        if ($DestFile.LastWriteTime -ne $SourceFile.LastWriteTime)
        {
            $SourceFile.CopyTo($DestFileName, $true)
        }
        
    }
    else
    {
        $SourceFile.CopyTo($DestFileName, $true)
    } 
}