План обслуживания «на каждый день» – Часть 3: Автоматическое создание бекапов
Существует великое количество постов, в которых настойчиво призывают к одной простой истине – нужно делать бекапы на постоянной основе. Но люди всегда будут делиться на две категории: кто еще не делает бэкапы, и кто их уже делает. Первая категория, которая пренебрегает такими советами, часто можно встретить на профильных форумах с примерно одинаковыми вопросами:
– у меня полетели диски/кто-то удалил мою базу… как мне восстановить мои данные? – у вас есть свежий бекап? – нет
Чтобы не стать героем такой ситуации, нужно потратить минимум усилий. Во-первых, выделить дисковый массив, на который складывать резервные копии. Поскольку, хранить бекапы вместе с файлами БД – явно не наш выбор. Второе… это создать план обслуживания по резервному копированию баз данных.
Что мы и сделаем далее, а после обсудим некоторые тонкости связанные с бекапами. Создадим таблицу, в которой будут записываться сообщения об ошибках при создании резервных копий:
Скрипт для резервного копирования баз данных на каждый день я использую такой:
Если на сервере настроен компонент Database Mail, то в скрипт можно добавить уведомление по почте о возникших проблемах:
Собственно, на этом этапе, рабочий скрипт для автоматического создания резервных копий готов. Остается его создать job, который бы по расписанию запускал этот скрипт.
Владельцев Express редакций нужно отдельно упомянуть, поскольку в SQL Server Express edition нет возможности использовать SQL Server Agent. Какая бы печалька не пришла после этих слов, на самом деле, все решаемо. Проще всего создать bat файл с примерно похожим содержанием:
Далее открыть Task Scheduler и создать в нем новую задачу.
Вторая альтернатива – использовать сторонние разработки, которые позволяют запускать задачи по расписанию. Среди них можно выделить SQL Scheduler – удобный и бесплатный тул. Инсталлятор у меня потерялся, поэтому буду благодарен, если кто-то поделиться рабочей ссылкой для читателей.
Теперь поговорим о полезных мелочах связанных с бекапами.
Возможность сжатия бекапов появилась впервые в SQL Server 2008. Вспоминаю с ностальгией время, когда работая на 2005 версии мне приходилось 7Zip-ом сжимать бекапы. Теперь же все стало намного проще.
Но нужно помнить, что сжатие бекапов будет использоваться только если выполнять команду BACKUP с параметром COMPRESSION или включить сжатие по умолчанию следующей командой:
К слову будет сказано, что сжатые бекапы имеет некоторые преимущества: нужно меньше места для их хранения, восстановление БД из сжатых бекапов обычно выполняется чуточку быстрее, также они быстрее создаются, поскольку требуют меньшего количества I/O операций. Минусы, кстати, тоже есть – при работе со сжатыми бекапами нагрузка на процессор увеличивается.
Этим запросом можно вернуть размер последнего FULL бекапа со сжатием и без:
Обычно сжатие достигает 40-90%, если не брать во внимание бинарные данные:
Если модифицировать предыдущий запрос, то можно мониторить для каких баз делались резервные копии:
Если у Вас SQL Server 2005, то эту строку:
backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN . нужно поменять на:
backup_size = s.backup_size / 1048576.0 Результаты этого запроса могут помочь предотвратить многие проблемы:
Можно сразу увидеть, что для всех ли БД есть FULL бекапы за актуальную дату.
Далее можно посмотреть на время создания бэкапа. Зачем спрашивается? Предположим, что раньше бекап базы DB_Dev занимал 5 секунд, а потом стал занимать 1 час. Причин этого может быть много: диски не справляются с нагрузкой, данные в базе выросли до неприличных объемов, полетел диск в RAID и скорость записи снизилась.
Если у базы стоит модель восстановления FULL или BULK_LOGGED, то желательно время от времени делать бекап лога, чтобы не обрекать сервер на муки постоянного роста LDF файла. Степень заполнения файла данных и лога для баз данных можно посмотреть этим запросом:
Результаты запроса на моем локальном инстансе:
Еще хотел показать пару интересных трюков, которые могут облегчить жизнь. Если при выполнении команды BACKUP указать несколько путей, то конечный файл с бекапом будет разрезан на куски примерно одинакового размера.
Однажды мне это пригодилось, когда пришлось копировать бекап на флешку с файловой системой FAT32, в которой есть ограничение на максимальный размер файла.
Еще одна интересная возможность – создавать копию бекапа. Из личного опыта скажу, что доводилось встречать людей, которые вначале создавали бекап в дефолтной папке, а потом руками или скриптом копировали на дисковую шару. А нужно было просто использовать такую команду: