Методика многоуровневого резервного копирования SQL баз данных 1С (и не только)

Предисловие

Компания использует в работе около 20 продуктивных баз данных 1С, часть из них типовые, часть собственной разработки. Разработка и доработка ведется собственными силами, конфигурации хранятся в хранилище 1С и в GIT, и каждому разработчику при необходимости создается копия продуктивной БД, так что общее количество баз перевалило за сотню. Продуктивные базы расположены на нескольких серверах, часть из которых расположены за пределами локальной сети на относительно тонких каналах. Размер баз данных тоже разный, от нескольких гигабайт до сотен гигабайт. Разработчикам в случае необходимости получить копию какой-нибудь БД она, естественно, нужна как можно скорее, поэтому нужно, чтобы бэкап каждой базы был свежим, находился близко к серверам с базами для разработки и развернулся автоматически (об автоматизации развертывания когда-нибудь потом). Организации нужно, чтобы хранилась достаточно долгая история по каждой базе данных, и в случае каких-то непредвиденных обстоятельств любую БД можно было развернуть в кратчайшие сроки. IT-отделу нужно, чтобы все эти резервные копии хранились надежно, занимали места не больше, чем действительно необходимо, а файловые операции не слишком загружали каналы передачи данных.

Для решения этих задач и была разработана данная методика, работающая как-то так:

• Существует два сервера, один для первичных (текущих) копий, другой для длительного хранения;
  • На первом сервере есть каталог, доступный по сети учетным записям, от имени которых выполняются серверы SQL; Этот сервер может быть как Windows, так и Linux с Samba, и любой NAS - от него требуется лишь предоставить в сеть каталог с достаточным пространством и возможность правильно настроить безопасность.
  • На втором (только Windows) служба Server запрещена совсем, для дополнительной безопасности (файлы по сети недоступны)
• Для каждой базы данных существует свой каталог резервных копий, свое задание агента SQL и свое время запуска;
• Резервная копия базы данных создается средствами SQL
  • Если хранилища бэкапов доступны серверу SQL по тонкому каналу, копия делается в локальный каталог, чтобы сократить время операции резервного копирования, затем программой robocopy копируется в первичное хранилище;
  • Если сервер и хранилище в быстрой сети, копия создается прямо в каталоге на первичном хранилиище;
• Когда созданная копия оказалась целиком в первичном хранилище, сервер SQL через powershell и службу WinRM обращается к серверу долговременного хранения и дает ему задание забрать только что созданный файл. Для получения файла запускается скрипт Powershell, лежащий локально в каталоге на этом сервере. Скрипт копирует файл в каталог базы данных в указанную в параметрах папку, после чего обрабатывает все остальные файлы в этой папке, оставляя несколько дневных копий, несколько недельных и несколько месячных. Количество копий каждого уровня и еще некоторые настройки можно задавать в файле, лежащем в каждом каталоге БД;
• Если настроена служба DatabaseMail, при ошибке задания отправляется уведомление оператору SQL.

Систему можно запустить на любой редакции SQL Server. Если отсутствует агент SQL, вместо него используется планировщик задач Windows, который запускает файл CMD, выполняющий те же операции при помощи sqlcmd и powershell.
Подходит не только для баз данных 1С, мы используем для других тоже, в том числе на редакциях Express.

Чтобы запустить всю эту кухню, необходимо:

• Настроить сервер № 2:
  • Создать каталог C:\sqlagent\, положить в него скрипты BackupFileProcessing.ps1 и PreparePSSessionConfiguration.ps1
    Если не нравится каталог, можно положить куда угодно, изменив путь в коде (он настраивается в файле AddDailyBackupJobV2.sql)
  • запретить службу Server
  • разрешить WinRM, настроить безопасность WinRM при помощи скрипта, входящего в архив - PreparePSSessionConfiguration.ps1
  • подготовить каталог для хранения бэкапов (каталогов может быть несколько, для разных баз можно задавать их в параметрах вызова скрипта BackupFileProcessing.ps1
• Настроить сервер № 1:
  • Создать папку, открыть к ней доступ по сети, предоставить права на запись учетным записям серверов SQL.
• Настроить сервер SQL:
  • Для всех редакций, кроме Express:
    • Если хотите получать оповещения на почту, настроить Database Mail;
    • открыть в SSMS файл AddDailyBackupJobV2.sql, настроить параметры в начале скрипта (имя базы данных, время запуска, и т.п.), выполнить скрипт, он создаст задание агента, на этом настройка закончена.
  • Для редакций Express:
    • выбрать или создать каталог, в который положить файлы SQLBackup.cmd и SQLBackup.sql;
    • Отредактировать файл SQLBackup.cmd, вписав туда имя вторичного сервера (возможно, позже сделаю поудобнее, добавлю в параметры вызова);
    • Создать задание в планировщике Windows (пример параметров запуска в файле Start.cmd)

Эпилог

Разрабатывалось для нужд своей компании. Публикуется скорее для себя, чтобы потом вспомнить, что делалось и зачем. В коде есть комментарии, надеюсь, они помогут тем, кто захочет это внедрить. Если кому-то захочется ругаться - прошу воздержаться, просто не качайте и не используйте. Времени на ответы мало, захожу редко.
Писалось, тестировалось и используется на SQL Server 2016. По моим представлениям, должно работать от 2012 до 2019, на более развернутое тестирование нет времени.

Последние версии файлов доступны тут. Поблагодарить автора можно, скачав файл из публикации.