Постановка задачи:
Имеем 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 Files\Allway Sync\Bin\syncappw.exe" -e -m -l -s "41F5E385D231E69F376C3DA7A3C650B9"). Задачу планировщика не сохраняем, и галочку «Использовать планировщик» убираем, т.к. у нас все будет делать SQLAgent.
В наш Job со скриптом копирования бэкапов добавляем следующий шаг с типом CmdExec и вставляем туда строку запуска синхронизации через AllwaySync.
Так как сервис агента запускается под пользователем SQLSERVERAGENT, то необходимо скопировать файлы настроек AllwaySync из папки пользователя, под которым мы делали настройки в такую же папку пользователя SQLАгента (C:\Users\SQLSERVERAGENT\AppData\Roaming\SyncAppSettings\_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) } }
