Скрипты на SQL для автоматизации архивации и сжатия баз данных

Администрирование - Архивирование (backup)

7
Автоматизирует всю работу по созданию backup на Sql Server, архивированию, удалению старых. Раскладывает backup по папкам - годовые, месячные, недельные, ежедневные. Архивирует WIN RAR. Очищает старые исходя из заданной политики сроков хранения backup каждого вида.

Долгое время я создавая backup сам регулярно очищал старые, и разносил копии по разным папкам.  Решил это автоматизировать. Долго подбирал механизм, так как не все работали с расшаренными ресурсами. В итоге после долгого тестирования родился такой скрипт.

Вы определяете при помощи параметров,  сколько и каких backup вы хотите хранить. (политику хранения бекапов разного типа)

К примеру, у нас принято хранить 5 ежедневных, 4 недельных, 6 месячных, 6 годовых backup. Эти значения я и выбрал по умолчанию.

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

Запускается соданием джобы на SQL Server с запуском хранимой процедуры sp_AddBkpDay с параметрами 

@BazaName AS NVARCHAR(100), -- Имя архивируемой базы данных
@PathBkp AS NVARCHAR(200) , -- каталог с несжатыми файлами пример - '\\fserver\ms_backup\'
@PathAllBkp AS NVARCHAR(200) , -- Каталог где копятся все бекапы пример - '\\fserver\ms_backup\'
@PathWinRar AS NVARCHAR(200) ='C:\Program Files\WinRar\RAR.exe', -- Путь к файлу rar.exe
@CountBkpDay AS Int = 5, -- Кол-во  дневных бекапов
@CountBkpWeek AS Int = 4, -- Кол-во недельных бекапов
@CountBkpMonth AS Int = 6, -- Кол-во месячных бекапов
@CountBkpYear AS Int = 6 -- Кол-во годовых бекапов

Достоинства

Простой и не тебующий долгой настройки. 

7

Скачать файлы

Наименование Файл Версия Размер
Скрипты на SQL для автоматизации архивации и сжатия баз данных. :
.sql 11,33Kb
22.05.17
17
.sql 1.0.1 11,33Kb 17 Скачать

См. также

Комментарии
Сортировка: Древо
1. ADirks 179 23.05.17 10:55 Сейчас в теме
Начиная с SQL 2008 бэкапы можно сжимать на лету. Это намного эффективнее внешних архиваторов.
И рекомендую почитать http://infostart.ru/public/173494/ там много полезного.
2. v3rter 23.05.17 13:27 Сейчас в теме
Для старых версий SQL сгодится. Правда разрешать SQL-серверу запуск командной строки не самая лучшая практика, считаю, особенно когда SQL работает под системной учёткой Administartor, пароль sa - 12345, а на серваке сэкономили на антивирусе )
Запуск хранимой процедуры через sqlcmd из батника или скрипта перспективнее.
3. SergeiGer 7 24.05.17 16:19 Сейчас в теме
Для меня суть обработки в том, что она отслеживает кол-во бекапов, по разным политиками ( дневные, недельные, месячные, годовые. ), сама отслеживает и удаляет лишнее. Дальше, многие средства не работают с расшаренными ресурсами ( UNC-пути ), или как стандартные средства Sql работают только с определенными расширениями.
На форуме Миста сравнение встроенного архиватора SQL Server и rar - "ПО жизни всегда жал rar`ом, но вчера что-то решил сравнить...
Фулл бэкап базы 38 гиг rar`ом жмется в 1.8 гиг, SQL серваком жмется в 4.3 гиг. Так что какой-то алгоритм не очень у сервера, а rar по прежнему на высоте. Жмите rar`ом."
Но для не желающих сжимать RAR добавлю параметр в процедуре.
И на днях сравню сам сжатие SQL и RAR.
Еще, я для быстрого восстановления в тест храню не заархивированный последний бекап локально. Это тоже очень удобно.
Все обслуживание баз данных осуществляется при помощи джоб. Создание архивов только часть регулярного обслуживания.
5. Fox-trot 71 24.05.17 20:29 Сейчас в теме
(3) кстати о птичках раз уж речь пошла про винду, винда сама умеет сжимать содержимое папок.
и таки да, малость запоздалая инфа, современные сервера позволяют все вышеперечисленное настраивать мышкой
такие дела
7. SergeiGer 7 25.05.17 11:49 Сейчас в теме
(3)Вы правы. Но у меня есть ресурс выделенный мне под архивы, на сервере возможно даже в другой стране. И я подозреваю что он не под виндой Наверное настройка винды для хранения архивов это другая тема.
4. v3rter 24.05.17 17:00 Сейчас в теме
6. SergeiGer 7 25.05.17 11:43 Сейчас в теме
Итак попробовал. Внимание следите за руками.
Размер бекапа 49.2 гб без сжатия.
Sql сжатие стал - 19.1
Сжатие бекапа RAR ом 49.2 стал 14.9
Сжатие сжатого SQL из 19.1 стал 18.
Вывод
1) После сжатия RAR на 21 % меньше чем SQL ( 14.9 против 19.1) (Посмотрел другие базы, там выигрыш у RAR еще больше до 50%, в этой 10% занимают сканы документов)
2) Сжимать RAR после SQL бесполезно. ( из 19.1 стал 18.)

Мне лично необходимо иметь под рукой копию, последнюю, которую я быстро смогу развернуть на тестовом сервере. Если я буду сжимать SQL Server то текущая, не заархивированная копия будет занимать 49 гб, а не 19 ка если бы я сжимал SQL. Но время на восстановление бекапа в базу данных пройдет существенно меньше. Но есть и проигрыш . При сжатии RAR, необходимо вначале разархивировать. Что на 49 гб занимает у меня около часа. Но восcтановление старых бекапов нужно очень редко.
Я храню 16 бекапов каждой базы. Если я буду сжимать SQL, то мне необходимо на каждую базу примерно еще 3* 16 = 48 гб.
Итог.
Для небольших баз размером меньше 5 гб, я бы посоветовал включить SQLархивирование. Для больших размеров RAR ( лучше 5 (в 5 один 2 процента выигрыша по сравнению с 4)).
8. ADirks 179 30.05.17 12:26 Сейчас в теме
(6) Нормальный архиватор (rar, 7zip, и т.д.) конечно сжимает эффективнее по размеру. Но временные затраты при этом разнятся чуть ли на порядок.
У меня к примеру база ~60G (.mdf файл), бэкап делается за 5 мин, восстановление за 15. При этом бэкапы лежат на другом сервере (точнее на NAS'е). Если делать бэкап без сжатия, то это займёт намного больше времени. Точно не вспомню, но помнится бэкап делался около 30 мин, причём на локальный диск. А чтобы бэкап развернуть, так вообще часа полтора: скопировать с NAS'а, распаковать, восстановить. Ну и плюс ковсему надо иметь свободного места на диске минимум в 2 раза больше размера базы.
Сжатый бэкап у меня кстати весит ~8G, уж не знаю почему.
При таких раскладах я решил, что внешние архиваторы не слишком полезны, ограничения по объёму нынче не такие жесткие.

Да, и полный бэкап у меня делается раз в неделю. Плюс каждые 15 мин. делается бэкап лога (модель восстановления естественно full). При этом размер бэкапа с логами редко превышает 500М.
9. SergeiGer 7 22.06.17 14:32 Сейчас в теме
Хочу от себя написать. До написания этой обработки, надо было думать о бекапах. Есть ли место, все ли в порядке, С тех пор как её запустил, забыл по администрирование бекапов. Все само добавляется, а самое главное удаляется во время. Для меня критично место, а не время восстановления. Критично чтобы количество бекапов всегда соответствовало политике хранения. Базы а основном по 70 гб. По этому не пользуюсь встроенным архиватором. В обработке встроен механизм отработки ошибок, конечно не всех. Но так как мы имеем дело с файловой системой и сетью, то надо было по максимуму их предусмотреть. Короче работает. Если есть предложения, что улучшить, давайте. Можно сделать чтобы скрипт сам проверял включена ли сжатие средствами SQL и в этом случае не сжимал RAR. Но надо ли?
10. smaharbA 24.06.17 17:58 Сейчас в теме
11. user600420_yan.vitalii 18.07.17 09:28 Сейчас в теме
Здраствуйте! Приобрели и попытались воспользоваться вашим скриптом, при выполнении ругается на синтаксис
Сообщение 156, уровень 15, состояние 1, процедура sp_CleanOldFiles, строка 50
Неправильный синтаксис около ключевого слова "PROCEDURE".
В других местах где используется слова "PROCEDURE". то же самое
Я в написании скриптов не силен, подскажите в чем проблема
12. SergeiGer 7 18.08.17 13:27 Сейчас в теме
(11)Виталий здравствуйте. С удовольствием вам помогу. Посмотрел скрипт еще раз. Не вставлено разделение пакетов, как как скрипт создан из двух скриптов. Создание процедуры sp_CleanOldFiles - очищающая старые бекапы. И создание процедуры sp_AddBkpDay собственно реализующую все функции, и использующую процедуру sp_CleanOldFiles . Просьба, выделите Часть скрипта от CRE ATE PROCEDURE [dbo].[sp_CleanOldFiles] до конца создания этой процедуры. Запустите. Теперь выделите всю часть от CRE ATE PROCEDURE [dbo].[sp_AddBkpDay] и заново запустите.
Я выложил измененный скрипт. Если можно заново скачайте его и запустите. Там все исправлено.
После запуска скрипта, вам надо сделать джобу,
И запускать скрипт с параметрами по расписанию. Если будут вопросы, пишите.
Оставьте свое сообщение