Создание и хранение резервных копий баз данных в MS SQL. Практические советы

[@g-man]

Я бы не советовал переводить базу из полной в простую модель восстановления. В этом случае рвется цепочка бэкапов логов (вы ведь делаете их регулярно, да?) и чтобы начать новую придется сделать полный бэкапов базы (после перевода в полную модель восстановления). И сжатие при бэкапе (флаг with compression) дает нагрузку на процессор, поэтому нужно выбирать подходящее время для бэкапа. А что вы подразумевали под «журнал транзакций перестает усекаться»?

[@armid]

Извините, промахнулся. Ответ ниже.

[@armid]

вы ведь делаете их регулярно, да?

На некоторых да, на некоторых нет. К сожалению, ресурсы ограничены. И кое где БД находится в простой модели восстановления для уменьшения количества записей на диск.

придется сделать полный бэкапов базы (после перевода в полную модель восстановления)

Да забыл упомянуть, что фактически перевод происходит после полного бэкапа.

И сжатие при бэкапе (флаг with compression) дает нагрузку на процессор, поэтому нужно выбирать подходящее время для бэкапа.

Разумеется. Но конечный размер файла намного меньше. А из моего опыта, узкое горлышко зачастую именно дисковая подсистема.
Толи записать 35 ГБ (бэкап с сжатием на самой объемной БД) толи 180 ГБ — без сжатия.

А что вы подразумевали под «журнал транзакций перестает усекаться»?

После бэкапа журнала, место под транзакции попавших в бэкап должно помечаться свободным и перезаписываться. Следовательно, как минимум сразу после бэкапа прироста журнала быть не должно. Разумеется если нет массированной вставки. А если сразу же есть прирост, то я называю «журнал транзакций перестает усекаться»

[@BelAnt]

Для удаления старых резервных копий в плане обслуживания есть Maintenance Cleanup Task. Я бы не рекомендовал включать xp_cmdshell ради такой задачи.

[@armid]

xp_cmdshell иногда я использую вот как.
У нас есть БД в простой модели восстановления. Ночью делается полный бэкап, а раз в час — разностный, но с режимом «присоединение (append)». Так вот, после полного бэкапа вызывается батник с помощью процедуры xp_cmdshell который удаляет разностный бэкап.

Во общем вот такой велосипед. Если что посоветуйте как лучше.

[@nelsh]

Когда-то пробовал использовать для резеврного копирования Maintenance Plan — не понравилось. В результате много лет использовалась самопальная утилита резервного копирования на JScript

Но пару лет назад наткнулся на SQL Server Compressed Backup (http://mssqlcompressed.sourceforge.net/) — расчитанную на резервное копирование одной базы данных. Сделал для нее простую обвязку на C# — ищете msbplaunch на codeplex.com

Пользуюсь около полутора лет на нескольких SQL серверах — полет нормальный. Суммарный объем полных бэкапов по всем серверам более 10GB.

Нужно отметить, что
1) все базы находятся в режиме простой модели восстановления
2) достаточно ежедневного резервного копирования.

Также важно, что на паре серверов используется SQL 2008 R2 Web Edition — который не знает про опцию «with compression».

[@armid]

Maintenance Plan — не понравилось

А чем не понравилось?

[@nelsh]

В основном из-за того, что резервные копии потом куда-нибудь копируются — т.е. обязательно сжатие данных, которое появилось только в SQL Server 2008. Удаления устареших резервных копий вроде до сих пор нет.
Поэтому кроме Maintenance Plan нужны еще какие-нибудь внешние командные файлы, к тому же вызывать их через xp_cmdshell не всегда безопасно.

А если умеешь программировать — почему не написать утилиту, которая сделает так как тебе нравится? Кстати первая версия утилиты была написана на PowerShell 2.0 — немного жаль, что Powershell не так удобен для меня, как C#.

В качестве примера — на самом «большом» SQL Server-е полный бэкап ~20-ти баз данных выполняется примерно за полчаса — получается около 5GB архивов. Сервер работает под Hyper-V (выделено 4 ядра, 5GB памяти). Нагрузка на сервер почти не заметна.

[@armid]

А как вы сжимаете копии? Сторонним архиватором или используете некий свой алгоритм?

[@nelsh]

Цитирую свой первый коментарий:

Но пару лет назад наткнулся на SQL Server Compressed Backup (http://mssqlcompressed.sourceforge.net/) — расчитанную на резервное копирование одной базы данных. Сделал для нее простую обвязку на C# — ищите msbplaunch на codeplex.com

[@Elisy]

Отличная статья. Понравилось про сжатие бекапов. Меньше стали занимать места в 5 раз. Раньше архиватором пользовались.

[@armid]

Спасибо. Минус архиваторов еще и в том, что когда клюнет петух, придется сначала разархивировать бэкап. То есть просто сидеть и ждать )

[@pportnoy]

В общем и целом статья полезная для новичков, но есть несколько «но»:

1) Крайне не согласен с Вашими высказываниями про переключение режимов восстановления при больших INSERT'ах в БД. Если Вы хотите избежать роста лога, то для этого есть четкие процедуры — INSERT должен выполняться как BULK, а БД нужно переключать не в Simple, а в Bulk Logged. И то это нежелательно.
2) Зачем приучивать людей использовать xp_cmdshell? Не надо, это дырка в безопасности. Пользуйтесь «родными» методами, благо у последних версий MS SQL Server агент, Maintenance plans и SSIS дают очень большие возможности. В том числе и для «цикличного» хранения резервных копий.

[@armid]

Спасибо.

Зачем приучивать людей использовать xp_cmdshell?

Попробую реализовать задачу встроенными средствами. По результатам отпишусь.

[@pportnoy]

Вы можете в самом Maintenance Plan настроить удаление .dif файлов старее 1 суток.

[@NoOne]

Есть еще полезная ХП master.dbo.xp_delete_file

EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N'bak',dateadd(d,-14,getdate()),0; 

Удалит все бекапы в папке с таким расширением старше 14 дней