Резервирование БД MS SQL 2008
Возникла необходимость автоматизировать создание резервных копий БД MS SQL. Честно говоря, раньше с этим зверем не работал и как-то сторонился 
Но нужно, значит - нужно! С чего начать? Думаю, с основных понятий, т.е. типов backup-ов. Их бывает несколько, но мы рассмотрим цепочку и варианты реализации для, более-менее, серъёзной БД с кучей таблиц и данных, мало того, запись ведётся чуть не каждую минуту. Каждая команда о создании backup-а - дополнительная нагрузка на сервер.
Итак, типы backup-ов:
- Полное резервирование Full Backup Полное резервирование обычно затрагивает всю вашу систему и все файлы. Применяют полное резервирование, в зависимости от важности и объёма данных.
- Дифференциальное резервирование Differential Backup При дифференциальном резервировании каждый файл, который был изменен с момента последнего полного резервирования, копируется каждый раз заново. Дифференциальное резервирование ускоряет процесс восстановления. Все, что вам необходимо, это последняя полная и последняя дифференциальная резервная копия. Т.е. - диф.копия - это все изменения, начиная от момента создания Полного резервирования по момент создания диф.копии.
- Добавочное резервирование Incremental Backup При добавочном ("инкрементальном") резервировании происходит копирование только тех файлов, которые были изменены с тех пор, как в последний раз выполнялось полное или добавочное резервное копирование. Последующее добавочное резервирование добавляет только файлы, которые были изменены с момента предыдущего добавочного резервирования. В среднем, добавочное резервирование занимает меньше времени, так как копируется меньшее количество файлов. Однако, процесс восстановления данных занимает больше времени, так как должны быть восстановлены данные последнего полного резервирования, плюс данные всех последующих добавочных резервирований. При этом, в отличие от дифференциального резервирования, изменившиеся или новые файлы не замещают старые, а добавляются на носитель независимо.
- Пофайловый метод Система пофайлового резервирования запрашивает каждый индивидуальный файл и записывает его на носитель.
- Метод отображающего дублирования диска Обычный аппаратный RAID. Он помогает лишь в случае выхода из строя винчестера. ИМХО, при использовании БД - иметь обязательно.
- Метод зеркалирования данных Метод, при котором данные зеркалируются на другой сервер.
Давайте рассмотрим нежелательную ситуацию. А именно: по какой-то причине вышла из строя БД. Что есть у нас? Полная копия, диференциальная копия на вчера, но на сегодня тоже есть данные, неужели нужно было делать диф.копирование каждый час? - Нет! Есть Журнал транзакций.
Журнал транзакций - журнал, в который записываются все транзакции и все изменения базы данных, выполняемые каждой транзакцией. Т.е. любое действие с БД пошагово запысается в журнал. Каждая запись отмечается СУБД на предмет завершённости транзакции, выполнена или нет. С его помощью, можно восстановить состояние БД не только после сбоя, а и при непредвиденных действиях с данными. Откатить до определённого времени. Как и с БД, с журналом транзакций нужно проводить резервное копирование, полное, дифференциальное, инкрементное. Для восстановления части журнала транзакций после сбоя в промежутке между созданием резервных копий, нужно выполнить резервирование заключительного фрагмента журнала, который, по-сути, является точкой финализации резервного копирования. Выполняется после сбоя, как точка обратного отсчёта.
Итак, для восстановления БД после сбоя нам нужны - актуальная полная копия БД, дифференциальная копия БД и копия журнала транзакций.
Для самой базы данных существует 3 модели восстановления - простая, полная и модель с неполным протоколированием. Рассмотрим:
- Простая модель (Simple) - используется только полное резервирование. Нет диф. резервирования, как и резервирования журнала транзакций. Полные копии нужно создавать как можно чаще. Актуально для БД, используемых "только для чтения".
- Модель полного восстановления (Full) - наиболее применяемая модель, при которой доступны все функции резервного копирования данных и их восстановление. Поддерживает восстановление отдельных страниц данных. Происходит полное протоколирование транзакций сохраниние журнала транзакций.
- Модель с неполным протоколированием (Bulk-Logged) - предназначена, как дополнение к полной модели полного восстановления. Не поддерживает протоколирование большинство массовых операций, соответственно - не поддерживает восстановление БД до определённого момента времени.
Рассмотрим наиболее актуальную цепочку создания резервных копий: Полное резервирование - раз в неделю, Дифференциальное резервирование - раз в день, Резервирование журнала транзакций - раз в час.
Есть несколько вариантов создания резервных копий:
- С помощью встроенного планировщика задач MS SQL
- С помощью языка Transact-SQL
- С помощью sqlcmd и Планировщика задач ОС
- Вручную (что нас не устраивает, ибо тру админ должен постоянно бездельничать)
Рассмотрим первый вариант, как наиболее юзабельный. Для этого используется Windows Server 2008 R2 Enterprise и MS SQL Server 2008 Eng.
Итак, допустим, что у нас есть БД TECH:
Переходим к инструменту создания Джобы:
Трём правую клавишу мышака и вызываем Мастера Джобу:
Выбираем галочку "Отдельное выполнение каждого задания", мы ведь выполняем только одно действие 
Мастер без тюрбана, но ведь не в размере тюрбане главное )) Выбираем тип желания, в нашем случае - полное резервирование:
Мастер Джоба, как оказалось, чуточку немного еврей, поэтому переспрашивает ещё раз:
"Параметры дополнительные выбрать стоит, о юнный паддаван!":
здесь выбираем БД, срок хранения резервной копии, адрес (лента или диск), путь сохранения и главное - планировщик заданий!
"Не стоит о базе данных забывать при выборе своём. Сконцентрируй силу и выбери БД":
"Слишком быстро ты спешишь задание создать, нажать на кнопку стоит, что внизу с названием Shedule - Define".
Собсно, планировщик заданий, где выбираем тип (повторение, единожды и т.д.), день, время, тип старта:
Вот и всё, создали. Мастер Джоба крут и зелен. Смотрим в Maintance Plans состояние:
Для параноиков, не бойтесь в этом признаться зеркалу, стоит заглянуть в душу SQL Server Agent - Job Activity Monitor, Мастер Джоба подробно покажет всё:
Теперь, при удовлетворении заданных условий, должен создаться полный бэкап БД. По такому же принципу, создаётся диф.резервирование и резервирование журнала транзакций (эти подпункты разположены ниже "Полное резервирование" в списке выбора заданий).
Крутите уши MSSQL-ю, как Вам удобно, не отвертите
В следующей статье - создание с помощью Transact-SQL и пара примеров.
В статье использовались данные с форума, также - выжатая суть из Microsoft.TechNet.
- Войдите на сайт для отправки комментариев
Версия для печати
-- это в батнике по расписанию
sqlcmd -S SERVERNAME -U sa -P PASSWORD -i "C:\Users\Администратор\Desktop\scripts\резервное копирование какой-то базы.sql"
-- это сам скрипт "резервное копирование какой-то базы.sql"
DECLARE @disk NVARCHAR(2) , -- буква диска
@separate NVARCHAR(1), -- разделитель (\)
@extension NVARCHAR(4) , -- расширение файла
@del_extension NVARCHAR(4) , -- расширение файла для удаления
@subdir NVARCHAR(512) , -- директории с разделением
@date NVARCHAR(11) , -- дата для именования файликов
@DeleteDate DATETIME, -- дата для удаления
@pathName NVARCHAR(512) , -- путь для сохранения
@name NVARCHAR(512) , -- просто название
@base_name NVARCHAR(20), -- имя базы с которой работаем
@hour_full_backup INTEGER; -- час , когда делаем полную копию
SET @base_name = 'db01'; -- имя базы
SET @disk = 'D:';
SET @separate = '\';
SET @extension = '.bak';
SET @del_extension = 'bak';
SET @subdir = '\admin\Backup\mssql\'+@base_name;
SET @date = datename(DW,Convert(varchar(12), GETDATE(), 112));
SET @DeleteDate = DateAdd(day, -6, GetDate());
SET @hour_full_backup = 8;
SET @pathName = @disk+@separate+@subdir+@separate;
SET @name = @base_name+'_'+@date+'_'+Convert(varchar(12), GETDATE(), 108);
EXECUTE master.dbo.xp_create_subdir @pathName;
if (DATEPART(hh,GETDATE()) = @hour_full_backup)
BEGIN
--удаляем старые файлики
EXECUTE master.sys.xp_delete_file 0,@pathName,@del_extension,@DeleteDate,1;
SET @pathName = @disk+@separate+@subdir+@separate+@date+@extension;
BACKUP DATABASE @base_name TO DISK = @pathName WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
USE [mosquitoff]
DBCC SHRINKFILE (N'db01') WITH NO_INFOMSGS
DBCC SHRINKFILE (N'db01_log' , 1, TRUNCATEONLY) WITH NO_INFOMSGS
-- оптимизация индексов пользовательских баз с обновлением статистики
-- это уже не к теме , но полезно
USE [master]
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
--
END
else
BEGIN
SET @pathName = @disk+@separate+@subdir+@separate+@date+@extension;
BACKUP DATABASE @base_name TO DISK = @pathName WITH DIFFERENTIAL, NOINIT, NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
END
в результате файлы резервных копий будут "человекоудобными" и будут ложится хоть по сети , чего не получается через интерфейс studio.
другое дело муся - одна строчка и бекап говов =)))
Для правильного бэкапа со всеми описаниями и параметрами одной строчкой и в мусе не обойдёшься. Да и речь идёт о запланированном бэкапе, просто забекапить на T-SQL - тоже строчка в 4 слова, но это не то, что хотели
30 3 * * * /usr/local/bin/pg_dump -U user base | /usr/bin/gzip -c > /backup/file.gz
примерно так - бекап по расписанию
вопрос в другом: будет ли блокировка mssql2008 при бекапе?
ЗЫ я с mssql раньше не работал, но вот пришлось