ГлавнаяРазноеРезервное копирование sql server

Копирование и восстановление баз данных в Microsoft SQL Server 2012 / 2008. Резервное копирование sql server


Сохранение резервных копий баз данных в MS SQL Server 2008 R2 на FTP-сервер

В данной статье речь пойдет о сохранении файлов резервных копий, полученных в результате работы планов обслуживания Microsoft SQL Server 2008 R2, на отдельный FTP-сервер. Эта операция позволит защитить данные от таких неприятных происшествий как выход из строя дискового накопителя, катастроф, кражи оборудования и прочего. К сожалению в программе «Среда Microsoft SQL Server Management Studio» штатными средствами не предусмотрено копирования файлов по FTP. Поэтому алгоритм автоматизации этого процесса будет следующий:

0. Оглавление

  1. Создание bat-файла для копирования на FTP-сервер (скачать все файлы одним архивом).
  2. Создание задания Агента SQL Server для запуска bat-файлов
  3. Добавление заданий в существующий план обслуживания SQL Server

1. Создание bat-файла для копирования на FTP-сервер

Согласно стратегии резервного копирования, приведенной в статье «Копирование и восстановление баз данных в Microsoft SQL Server 2008 R2», нам необходимо сохранять ежедневные полные бэкапы производственных баз данных на FTP-сервере и хранить их там 1 месяц с момента создания.

О создании «батника» для копирования произвольного файла на FTP-сервер, я уже писал в статье «Скачивание, загрузка и удаление файлов с FTP-сервера из командной строки».

Но дело в том, что имя файла, полученного в результате резервного копирования в SQL Server Management Studio, имеет примерно следующий вид:

BASE1_backup_2013_06_25_030007_5153516.bak

состоящий из имени базы, слова backup, даты и времени создания копии. Поэтому имя файла необходимо «вычислять» каждый раз от даты копирования. В данном случае — от текущей даты. Для построении имени файла резервной копии будем использовать следующий код bat-файла:

rem ***** Вычислим параметры текущей даты и имена файлов ***** set year=%date:~6% set month=%date:~3,-5% set day=%date:~0,-8% set file_name="%base_name%_backup_%year%_%month%_%day%_*.bak" set file_name_on_ftp="%base_name%_backup_%year%_%month%_%day%.bak"

Здесь переменная %date% возвращает строковое представление даты, из которого мы извлекаем все необходимые параметры и собираем из них имя файла. Следует помнить, что формат возвращаемой строки зависит от региональных настроек системы. Поэтому чтобы вышеприведенный код работал, необходимо убедиться, что переменная %date% возвращает строку вида: «09.07.2013» . Проверить это можно выполнив:

echo %date%

Таким образом, полный текст файла copy_on_ftp.bat, для копирования резервной копии базы SQL-сервера на FTP, будет следующим:

rem ***** Отключаем вывод на экран ***** echo off rem ***** Запишем все значения переменных ***** set ftp_host= set ftp_username= set ftp_pass= set file_transport=transport.txt set base_name=%1 set dir_from=%2 set dir_to=%3 rem ***** Вычислим параметры текущей даты и имена файлов ***** set year=%date:~6% set month=%date:~3,-5% set day=%date:~0,-8% set file_name="%base_name%_backup_%year%_%month%_%day%_*.bak" set file_name_on_ftp="%base_name%_backup_%year%_%month%_%day%.bak" rem ***** Создаем файл с командами ftp ***** echo open %ftp_host%>%file_transport% echo user %ftp_username% %ftp_pass%>>%file_transport% echo cd %dir_to%>>%file_transport% echo lcd %dir_from%>>%file_transport% echo put %file_name% %file_name_on_ftp%>>%file_transport% echo bye>>%file_transport% rem ***** Запускаем на исполнение ***** ftp -v -n -s:%file_transport% rem ***** Удаляем файл с командами ftp ***** del %file_transport%

Необходимо только указать параметры подключения к FTP-серверу. Имя базы данных, каталог с копией на локальном компьютере и каталог на сервере здесь задаются в качестве параметров. Соответственно, чтобы скопировать копию базы BASE1 из «C:\Backup\BASE1» на FTP-сервер в «Backup/SQL/BASE1» за сегодняшнее число, необходимо выполнить:

Аналогичным образом строятся файлы для удаления старых копий с FTP-сервера. Согласно выбранной стратегии резервного копирования, нам необходимы «батники» для удаления копий месячной del_on_ftp_last_month.bat и годичной del_on_ftp_last_year.bat давности.

2. Создание задания Агента SQL Server для запуска bat-файлов

Теперь перейдем к SQL-серверу. В моем примере это Microsoft SQL Server 2008 R2. Но для других версий SQL Server, действия будут аналогичными.

Создадим задание для запуска bat-файлов, которые будут копировать базу данных master и удалять соответствующую копию прошлого года. Для этого запустим SQL Sever Management Studio ( «Пуск» — «Все программы» — «Microsoft SQL Server 2008 R2» — «Средства SQL Server 2008 R2» ) и введем данные для авторизации.

Затем в обозревателе объектов раскроем вкладку «Агент SQL Server», кликнем правой кнопкой мыши по вкладке «Задания» и в контекстном меню выберем «Создать задание» .

Откроется окно нового задания, введем Имя и Описание и перейдем на вкладку «Шаги» .

Здесь нажмем кнопку «Создать» , чтобы добавить шаг в задание.

Напишем Имя шага, тип выберем «Операционная система (CmdExec)» и пропишем команду для копирования базы на FTP-сервер.

Затем перейдем на вкладку «Дополнительно» и укажем:

  • Действие при успехе: «Перейти к следующему шагу» ;
  • Действие при ошибке: «Завершить задание с ошибкой» ;

После чего жмем «ОК» .

Аналогичным образом создадим еще один шаг, с командой удаления прошлогодней копии базы данных.

С той лишь разницей, что при успехе необходимо «Завершить задание с успехом» .

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

Сохраним задание нажав «ОК» .

3. Добавление заданий в существующий план обслуживания SQL Server

Ну и наконец осталось добавить созданное задание в план обслуживания, который делает резервную копию базы данных master.

Для этого на вкладке «Планы обслуживания» выделим необходимый план обслуживания, в «Панели элементов» найдем пункт «Задача «Выполнение задания агента SQL Server»» и перетащим его в окно графического представления плана. В появившемся списке всех существующих задач, выберем необходимую нам задачу и нажмем «ОК» .

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

Смотрите также:

tavalik.ru

SQL Server 2008: Резервное копирование. Часть 1: Теория - SQL

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

 

Начнем с того, что резервное копирование (англ. backup) — процесс создания копии данных на носителе (жёстком диске, дискете и т. д.), предназначенном для восстановления данных в оригинальном месте их расположения в случае их повреждения или разрушения, соответствующими программами — резервными дубликаторами данных.

 

Первый шаг

 

Если так уж сложилось, что по долгу своей службы вы отвечаете за сохранность баз данных и бесперебойное функционирование SQL-сервера, то наверняка первой вещью, о которой вы задумаетесь, будут бэкапы — банально по той причине, что если вдруг с вашими базами данных случается какая-нибудь беда, а у вас нет бэкапа, вас ждет не самое приятное будущее. Но вместе со светлой мыслью о необходимости бэкапов приходит и миллион самых разнообразных вопросов: с чего начать? резервировать все базы вместе, или лучше каждую отдельно? делать полный бэкап? А может, лучше резервировать только логи? Что ж, давайте попробуем разобраться.

 

В первую очередь следует определиться с двумя вопросами:

1.Насколько велика роль каждой отдельной базы данных для компании? 

2.Потери данных за какой промежуток времени можно считать допустимыми?

 

С первым вопросом все более-менее ясно — он нужен для того, чтобы определиться с глобальной стратегией. Очевидно, что чем важнее какая-то конкретная база данных, тем раньше и чаще для нее должны делаться бэкапы. Если в компании все базы данных используются примерно с одинаковой степенью интенсивности, то оптимальным вариантом скорее всего будет создание единого плана резервирования. С другой стороны, если у вас одни базы данных используются намного чаще и нагружены значительно сильнее, чем другие, возможно, стоит задуматься о том, чтобы планировать их резервирование независимо друг от друга.

 

А вот со вторым вопросом все не так гладко. Хотя бы потому, что в ответ абсолютное большинство руководителей расскажет вам о недопустимости потери данных за любой промежуток времени. Между тем, все мы прекрасно понимаем, что организация отказостойчивой инфраструктуры со сведенным к нулю шансом потери данных — дело сложное, затратное и неподходящее для небольших и средних компаний. Постарайтесь объяснить свою позицию и добиться какого-то более определенного ответа — день, час, полчаса, пять минут или любая другая цифра, с которой вы сможете работать.

 

Только тогда, когда у вас будут конкретные и однозначные ответы на эти вопросы, вы сможете правильно оценить плюсы и минусы различных подходов к созданию бэкапов.

 

Full Backup (Полный бэкап)

 

Полный бэкап — это фундамент, на котором будет держаться вся ваша система резервирования. По большому счету, это копия базы данных, которая включает в себя вообще все — таблицы, данные, индексы, триггеры, статистику, хранимые процедуры и еще много разного добра. Более того — если вы выбираете вариант динамического резервного копирования (то есть во время резервирования данных пользователи могут полноценно работать с сохраняемой БД), то все измения, которые пользователи сделают за то время, пока создается бэкап, тоже будут сохранены. Вы можете легко вернуть базу данных к тому состоянию, в котором она была в какой-то определенный момент, если у вас есть полный бэкап, сделанный в это время.

 

Главный вопрос, связанный с полными бэкапами — как часто их стоит делать? Универсального ответа нет. Понятно, что полное резервирование должно выполняться по меньшей мере раз в месяц. Если у вас не слишком большие базы данных и нет особых проблем с местом под бэкапы, возможно, резервирование раз в неделю будет более подходящим вариантом. С другой стороны, слишком частое полное резервирование будет нагружать ваш сервер и потребует неоправданно много дискового пространства для хранения бэкапов. Точная цифра зависит от размера ваших баз данных, производительности и загруженности сервера, ответа на «фундаментальный вопрос номер два» и того, как часто вы будете делать бэкапы других типов, например, бэкап лога или дифференцированный бэкап.

 

Log backup (Бэкап лога)

 

Бэкап лога отличается от полного бэкапа тем, что в него входят исключительно изменения базы данных (то есть операции INSERT, UPDATE и DELETE) с момента последнего бэкапа, будь то полный бэкап, дифференцированный или предыдущий бэкап лога. Поскольку объем сохраняемых данных крайне мал, такой тип резервирования намного быстрее, требует меньше ресурсов и занимает меньше места на диске. Недостатков, увы, тоже хватает. В первую очередь, бэкап лога бесполезен, если у вас нет хотя бы одного полного бэкапа. Объясняется это тем, что в таком логе не сохраняется никакая информация о таблицах, индексах, хранимых процедурах и так далее. Вторым существенным недостатком является то, что если с момента последнего полного бэкапа вы успели сделать сотню бэкапов лога, а потом случилась беда, то прежде, чем вы восстановите заветный сотый, вам потребуется восстановить не только полный бэкап, но и предыдущие девяносто девять бэкапов лога, да к тому же в правильном порядке. Согласитесь, приятного в такой перспективе не очень много. Еще одна немаловажная особенность заключается в том, что бэкап лога доступен только для тех баз данных, у которых указан FULL или BULK LOGGED режим восстановления.

 

Для того, чтобы можно было создавать бэкапы лога, ведение этого самого лога должно быть включено. Хотя данная опция работает по умолчанию и предоставляет немало замечательных возможностей, есть ситуации, когда разумнее будет отказаться от ее использования. Во-первых, лучше отключить ведение лога, если вы собираетесь добавить в базу или изменить в ней очень много данных. Такая операция может занять не один час, но с отключенным протоколированием выполнится намного быстрее. Как только данные будут добавлены, можно смело делать полный бэкап БД и включать режим ведения лога. Вторым пикантным моментом является то, что за логом нужно очень пристально следить — если память, которая доступна для записи лога, заканчивается, система останавливает все выполняющиеся транзакции до тех пор, пока память не будет освобождена. Чтобы избежать такой печальной ситуации, требуется делать бэкап лога как можно чаще — тогда как только вы делаете более свежий бэкап, система помечает сохраненную порцию лога как доступную для перезаписи, тем самым восстанавливая занятое дисковое пространство.

 

Как часто делать бэкапы лога? Опять же, универсального ответа нет. С одной стороны, чем чаще вы делаете бэкап лога, тем большую гибкость получаете при восстановлении и тем меньше данных теряете в случае каких-то происшествий. С другой стороны, если вы делаете полный бэкап раз в сутки, а бэкап лога раз в минуту, то при худших раскладах вам придется восстановить почти полторы тысячи бэкапов, прежде чем база вернется к тому состоянию, которое можно считать последним рабочим. Мало того, что такая перспектива сама по себе не вдохновляет на подвиги, так еще и в это время над вами будут стоять руководители, директора и все, кому не лень, спрашивая о том, когда же всё будет готово и почему так долго. Думаю, бэкап лога чаще, чем раз в 30 минут, практически всегда окажется плохой идеей. Существует мнение, что бэкап лога реже, чем раз в час, тоже не имеет смысла, но это заявление кажется мне несколько спорным. В любом случае, если вы будете держаться в этих пределах, скорее всего, это станет неплохим решением.

 

Differential Backup (Дифференцированный бэкап)

 

Дифференцированный бэкап — это что-то среднее между полным бэкапом и бэкапом лога. В нем сохраняются изменения, сделанные с момента последнего полного бэкапа по настоящее время. Главным его преимуществом по сравнению с бэкапом лога является то, что для полного восстановления базы данных вам достаточно восстановить только лишь полный и последний дифференцированный бэкапы. Недостатком же является то, что вы не можете восстановить промежуточное состояние базы данных на какой-то момент времени — история изменения БД в дифференцированном бэкапе не сохраняется. Как и бэкап лога, такой бэкап бесполезен, если у вас нет полной копии базы данных.

 

Вместо постскриптума

 

Напоследок я хотел бы дать вам несколько советов, которым не нашлось места в рамках основного повествования, но которые слишком важны, чтобы о них молчать.

•Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах.

•Используйте опцию BACKUP WITH CHECKSUM, чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему.

•Если у вас проблемы с дисковым пространством, доступным для хранения бэкапов, используйте опцию BACKUP WITH COMPRESSION. Сжатие позволяет уменьшить итоговый размер файла с бэкапом в несколько раз, обычно в 3-5. Как и в случае с проверкой контрольной суммы, такая операция очень серьезно влияет на производительность, особенно для больших баз данных.

•Создавайте отдельный файл для каждого бэкапа, не храните все бэкапы в одном файле. В таком случае, если с этим файлом что-то случится, вы потеряете один бэкап, а не все.

•Естественно, не храните бэкап на том же диске, на котором хранится ваша БД.

•Если существует угроза, что кто-то посторонний будет иметь доступ к вашим бэкапам, используйте парольную защиту или шифрование.

•Автоматизируйте процесс создания бэкапов. Это просто, и вы будете уверены, что у вас всегда есть свежая копия базы данных.

•Бэкапы бесполезны, если вы не можете их использовать. Поэтому регулярно тренируйтесь в восстановлении баз данных, чтобы при необходимости вы могли в стрессовой ситуации сделать все быстро и безошибочно.

 

aquablog.3dn.ru

Создаем резервное копирование (бекап) базы данных MS SQL Server

Всем привет дорогие друзья. В прошлой статье я рассказала Вам о том, как можно остановить или запустить службу при помощи командной строки, так вот, в данной статье я обещал рассказать, как можно настроить бекап базы данных MS SQL Server по определенному времени.

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

Вроде бы все понятно, теперь нам необходимо открыть текстовый документ, например Блокнот, он точно у всех есть, и наполнить его следующим кодом:

net stop MSSQL$VSdotNet set sFolderName=%date%_%time:~0,-6% 7za a D:\backup\"%sFolderName::=-%".zip "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\name_db.mdf" 7za a D:\backup\"%sFolderName::=-%".zip "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\name_db.ldf" net start MSSQL$VSdotNet

То есть, тут понятно. Мы узнали имя службы сервера, останавливаем ее, задаем имя переменной для архива (оно будет содержать дату и время), а затем производим архивацию по указанному пути двух файлов. Тут еще необходимо предусмотреть один нюанс. Нам необходимо чтобы формировался zip-архив, поэтому в папке system32 должен находиться Windows-архиватор 7za.exe, который я приложу в конце статьи.

Далее Вы сохраняете данный файл с расширением bat, называете его, как хотите и затем добавляете его в назначенные задания (Пуск-Все программы-Стандартные-Служебные-Назначенные задания). Там настраиваете время, дату, задаете имя и так далее. Все очень просто, можете проверить.

Конечно, можно настроить резервное копирование базы данных при помощи Microsoft SQL Server Management Studio Express, но мне больше по душе данный метод. Также можете прочитать статью о том, как можно создать БД в MS SQL Server.

Скачать архиватор для system32

Также советуем почитать на PressDev.RU

pressdev.ru

Создание устройства резервного копирования в MS SQL Server 2012

При выполнении операций резервного копирования и восстановлений баз данных в MS SQL Server удобно использовать логические устройства резервного копирования. Преимущество заключается в более коротком имени устройства в противовес указанию полного пути расположения файла и имени файла с резервной копией. Использование логического устройства резервного копирования может быть полезным, если планируется запись набора резервных копий по одному и тому же пути или на один накопитель на магнитной ленте. Они также особенно полезны для указания ленточных устройств резервного копирования. О создании логического устройства резервного копирования в MS SQL Server 2012 и пойдет речь в данной статье (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен).

0. Оглавление

  1. Создание устройства резервного копирования
  2. Использование устройства резервного копирования
  3. Просмотр содержимого устройства резервного копирования

1. Создание устройства резервного копирования

Подключаемся к MS SQL Server c помощью программы  «SQL Server Management Studio». В Microsoft Windows Server 2012 R2 ее можно найти в списке всех программ.

В Microsoft Windows Server 2008 R2 в меню «Пуск» (Start) — «Microsoft SQL Server 2012» — «Среда SQL Server Management Studio».

Вводим адрес сервера или его псевдоним, данные для авторизации и нажимаем «Соединить» (Connect).

Слева, в обозревателе объектов (Object Explorer), раскрываем вкладку «Объекты сервера» (Server Oblects), кликаем правой кнопкой мыши по вкладке «Устройства резервного копирования» (Backup Devices) и в контекстном меню выбираем пункт «Создать устройство резервного копирования…» (New Backup Device…).

В появившемся окне вводим имя устройства резервного копирования и назначение устройства — ленту или файл на жестком диске. Учитывая, что все наборы резервных копий будут сохраняться в указанном файле на диске или на ленте, необходимо позаботиться о наличии свободного места на данных устройствах. Выбрав необходимые параметры, нажимаем «ОК».

После чего только что созданное устройство появится в списке устройств резервного копирования в обозревателе объектов SQL Server.

2. Использование устройства резервного копирования

Теперь при создании резервной копии базы данных вместо указания пути расположения и имени файла резервной копии можно будет просто выбрать созданное ранее устройство резервного копирования.

Причем в зависимости от того, какие настройки установлены на вкладке «Параметры» (Options) раздела «Переписать носитель» (Restore options) окна создания резервной копии, зависит будет ли данная резервная копия добавлена к существующим резервным наборам данных или же все существующие на устройстве резервные наборы данных будут перезаписаны.

При восстановлении базы данных необходимо будет просто выбрать данное устройство резервного копирования из списка.

Причем необходимая резервная копия будет автоматически найдена в устройстве резервного копирования в соответствии с выбранной базой данных и периодом для восстановления.

3. Просмотр содержимого устройства резервного копирования

Для того, чтобы просмотреть какие наборы резервных копий содержатся в устройстве резервного копирования достаточно вызвать свойства данного устройства (в обозревателе объектов (Object Explorer) кликнуть правой кнопкой мыши по данному устройству и в контекстном меню выбрать «Свойства» (Properties) или просто дважды кликнуть по нему левой кнопкой мыши). Далее в окне свойств набора необходимо перейти на вкладку «Содержимое носителя» (Media Contents) где в таблице будут расположены все резервные копии, содержащиеся в данном устройстве резервного копирования.

Смотрите также:

  • Удаление компонент MS SQL Server 2012

    Может случиться так, что в процессе установки MS SQL Server 2012 были установлены компоненты, необходимость в которых со временем отпала (или были установлены по ошибке). В этом случае, в целях…

  • Лицензирование MS SQL Server 2012

    В данной статье будут рассмотрены способы лицензирования Microsoft SQL Server 2012. Будет приведен краткий обзор каждого из способов лицензирования, а также указаны возможные варианты лицензирования для различных выпусков MS SQL Server…

  • Редакции MS SQL Server 2012

    Ниже приводится список существующих редакций Microsoft SQL Server 2012, а также приводится краткий обзор каждой из них.         Microsoft SQL Server 2012 предлагается в нескольких специально разработанных редакциях:…

tavalik.ru

Копирование и восстановление баз данных в Microsoft SQL Server 2012 / 2008

В данной статье я постарался привести сжатые теоретические выкладки, необходимые для понимания процесса резервного копирования и создания плана резервного копирования в Microsoft SQL Server (справедливо для Microsoft SQL Server 2012 и Microsoft SQL Server 2008 R2).

 

0. Оглавление

1. Причины потери данных

Можно выделить 5 основных причин потери данных:

  1. Программные ошибки — Возникновение условий, приводящих к аварийному завершению системы. Поскольку такие ошибки основываются на дефектах программной логики, система баз данных не может выполнить восстановление в подобных ситуациях. Поэтому восстановление должен проводить сам программист, выполнив обработку таких исключений.
  2. Ошибки администратора (человеческий фактор) — Случаи, в которых пользователь с большими полномочиями может неумышленно (или умышленно) разрушить данные. Необходимо постараться создать такой режим работы, который сделает подобную ситуацию маловероятной, однако совсем исключать такую возможность нельзя.
  3. Выход из строя компьютера (сбой системы) — Возникает в результате ошибок в оборудовании и программном обеспечении. В этом случае содержимое оперативной памяти компьютера может быть потерянно. В качестве защиты, можно рекомендовать использование резервного сервера, зеркальное отображение баз данных и пр.
  4. Отказ дискового накопителя — Физическое разрушение жесткого диска. Рекомендуется использование технологий RAID для хранения файлов баз данных, кроме того необходимо, чтобы файлы резервных копий хранились на дисковом носителе, отличным от устройства, на котором располагаются файлы баз данных.
  5. Катастрофы (пожар, наводнение, землетрясение) или кража — Обойти эту ситуацию станет возможным, если устройство, содержащее необходимую для восстановления данных информацию, будет храниться отдельно от основного оборудования и не будет потеряно в результате катастроф или краж.

Необходимо постараться создать систему резервного копирования, позволяющую восстановить данные в любой из описанных выше ситуаций.

2. Типы резервного копирования

Существует 2 режима создания резервных копий:

  1. Статическое резервное копирование — режим, при котором в процессе создания копии только одна активная сессия, поддерживаемая системой, является той сессией, которая создает резервную копию. Другие пользовательские процессы во время выполнения копирования недопустимы.
  2. Динамическое резервное копирование — режим, при котором копирование баз данных может выполняться без остановки сервера баз данных, удаления пользователей или даже закрытия файлов. Пользователи могут и не знать, что выполняется процесс резервного копирования.

MS SQL Server поддерживает оба режима создания резервных копий.

3. Модели восстановления баз данных

Выбор модели восстановления базы данных определяет объем данных, который может быть потерян во время разрушения базы данных, а также скорость использования, размер резервной копии протокола транзакций и период времени, необходимый для резервного копирования протокола. MS SQL Server поддерживает три модели восстановления:

  1. Полная модель восстановления — модель, при которой все операции записываются в протокол транзакций. Поэтому эта модель предоставляет полную защиту против сбоев внешних устройств.
    • Преимущества:
      1. Есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
      2. Возможно восстановить данные на любой момент времени.
      3. Возможно восстановить данные на отметку в протоколе. Отметки в протоколе соответствуют заданной транзакции и добавляются только если эта транзакция подтверждается.
      4. Протоколируются все операции, связанные с изменением индекса. В этом случае пересоздание индекса выполняется быстрее, потому что не надо создавать их заново.
    • Недостатки:
      1. Соответствующий протокол транзакций может быть очень большим по объему, и файлы на диске, содержащие этот протокол, могут увеличиваться в размере очень быстро. В связи с этим возможно заполнение протокола транзакций.
      2. Протокол транзакций должен быть защищен от сбоев внешних устройств. По этой причине использование технологии RAID для защиты протокола транзакций строго рекомендуется.
      3. Требуется значительно больше времени на резервное копирование.
    • Заключение:Данная модель восстановления рекомендована для производственных баз данных, если позволяет аппаратная часть сервера баз данных.
  2. Модель восстановления с неполным протоколированием — То же, что и полная модель восстановления, за тем исключением, что не ведется протоколирование массовых или bulk-операций. А резервные копии протокола транзакций содержат в этом случае результат такой операции.
    • Преимущества:
      1. Как и с полной моделью восстановления, есть возможность восстановить базу данных с последней подтвержденной транзакции, которая была сохранена в файле протокола.
      2. Возможно восстановить данные на любой момент времени, если не выполнялось объемных операций.
      3. Возможно восстановить данные на отметку в протоколе, если не было объемных операций.
      4. Объемные операции выполняются намного быстрее, чем под полной моделью восстановления, так как они не протоколируются.
      5. Для резервной копии протокола требуется гораздо меньше памяти, чем в случае полного восстановления.
    • Недостатки:
      1. Те же, что и при полной модели восстановления.
      2. Не протоколируется операции с изменением индекса. При восстановлении, индексы потребуется создать заново.
      3. Восстановление с резервной копии протокола дольше, нежели при полной модели восстановления.
      4. Нет возможности восстановить данные на момент времени или на отметку в протоколе в случае объемных операций.
    • Заключение:Модель восстановления с неполным протоколированием используется для производственных баз данных в тех случаях, когда периодически происходят крупномасштабные или объемные bulk-операции.
  3. Простая модель восстановления — В простой модели восстановления протокол транзакций усекается, если появляется точка восстановления. Но это не означает, что вообще не существует протоколирования, содержимое протокола используется во время создания контрольной точки, где все транзакции протокола подтверждены или для них выполнен откат.
    • Преимущества:
      1. Производительность всех объемных операций очень высокая.
      2. Низкие требования к объему памяти протокола.
    • Недостатки:
      1. Данные возможно восстановить только на момент последнего резервного копирования, а значит недопустимы восстановления на конкретный момент времени или на отметку в протоколе. Все изменения с последнего резервного копирования должны быть восстановлены вручную.
    • Заключение:Рекомендуется использовать простую модель восстановления для производственных баз, только в тех случаях, когда сервер баз данных не обладает достаточным объемом памяти или когда аппаратная часть сервера баз данных ниже рекомендуемой.

4. Методы резервного копирования

MS SQL Server предоставляет 4 различных метода резервного копирования:

  1. Полное копирование базы данных (Full) — При полном резервном копировании создается резервная копия всей базы данных целиком, она включает в себя схему всех таблиц, соответствующие файловые структуры, а также содержит все данные из этой базы на момент завершения резервного копирования. Это осуществляется благодаря тому, что полное копирование захватывает состояние базы данных на момент начала копирования. А затем, если копирование выполняется динамически, система записывает любые действия, которые имеют место в процессе создания копии.
    • Преимущества:Быстрая скорость восстановление базы данных.
    • Недостатки: Полное резервное копирование требует больше времени и требует больше пространства для хранения, чем другие методы копирования.
    • Заключение:Для небольших баз данных, которые можно скопировать быстро, лучше всего применять полное резервное копирование. Но для больших баз требуется кроме полных резервных копий, создавать также и дифференцированные (разностные) копии баз данных.
  2. Дифференцированное (разностное) резервное копирование (Differential) — В этом случае создается копия только частей баз данных, которые менялись с момента последнего полного копирования баз данных. Эта полная резервная копия называется основой для разностной копии. Как и в случае полного резервного копирования, любые действия, имеющие место во время создания копии, также копируются.
    • Преимущества:Этот тип резервного копирования минимизирует время, требуемое для копирования, т. к. количество копируемых данных значительно меньше, чем при полном копировании.
    • Недостатки: Для восстановления необходимо загрузить сначала полную копию баз данных, а затем разностную, что занимает больше времени. И, соответственно, нет смысла применять разностное копирование без полного.
    • Заключение:Используется на больших базах данных в связке с полным резервным копированием.
  3. Резервное копирование протокола транзакций (Transaction log) — Данный вид копирования применяется при полной модели восстановления (или при неполном протоколировании) баз данных, и учитывает только изменения, записанные в протокол транзакций. Поэтому такая форма резервного копирования не основывается на физических страницах баз данных, а только на логических операциях.
    • Преимущества:
      1. Самая быстрая скорость создания копии.
      2. В отличии от дифференцированных резервных копий, где возможно восстановить базу данных только на момент последнего копирования, позволяет восстановить базу данных на конкретный момент времени.
      3. Правильное «закрытие» протокола транзакций перед началом новой порции действий с этим протоколом. Одна из наиболее общих ошибок системы возникает, когда переполняется протокол транзакций. Если память, используемая для протокола транзакций, заполняется на 100%, то система должна остановить все выполняющие транзакции, пока память не будет освобождена. Эта проблема может быть устранена только при частом выполнении резервного копирования протокола транзакций: каждый раз происходит «закрытие» порции существующего протокола и сохранение на другом внешнем устройстве. Эта порция протокола становится повторно используемой, следовательно, система восстанавливает дисковое пространство.
    • Недостатки: Более долгий процесс восстановления, чем при дифференцированном резервном копировании, где требуется полная копия базы данных и последняя разностная копия, в данном случае потребуется полная копия и все существующие копии протоколов транзакций.
    • Заключение:Рекомендуется применять на производственных базах данных в связке с полным резервным копированием.
  4. Резервное копирование файлов или файловых групп — Данный метод позволяет копировать указанные файлы баз данных вместо копирования всей базы данных. Отдельные файлы могут быть восстановлены из копии, позволяя выполнить восстановление после сбоя, который повлиял лишь на небольшое подмножество файлов баз данных.
    •  Заключение:Копирование файлов базы данных рекомендуется выполнять только когда база данных является очень большой, и нет достаточно времени для полного копирования базы данных.

5. Какие базы данных и как часто копировать?

  1.  База данных master является наиболее важной базой данных системы, потому что она содержит информацию обо всех базах данных в этой системе. Поэтому резервное копирование базы данных master должно происходить на регулярной основе. Кроме того, рекомендуется создавать копию каждый раз, когда выполняются действия, приводящие к модификации базы данных master. Вот некоторые из них:
    • Выполнение операторов и хранимых процедур;
    • Создание, изменение и удаление базы данных;
    • Изменения протокола транзакций.
  2. Следует выполнять резервное копирование всех производственных баз данных на регулярной основе. Дополнительно, необходимо делать резервную копию после того как с базами данных были выполнены следующие изменения:
    • После создания базы данных;
    • После создания индексов;
    • После создания протокола транзакций;
    • После выполнения непротоколируемых операций (операции, которые не записываются в протокол транзакций).

6. Пример стратегии резервного копирования

Ниже приводится некоторый план резервного копирования, в реальной организации:

И некоторые характеристики связанные с выполнением резервного копирования базы данных DB1:

Аппаратная часть:

    • Процессор Opteron 8220 8×1.0 Ghz
    • ОЗУ 24 Гб
    • HDD 250 Гб RAID 1+0

Программное обеспечение:

Базы данных:

    • Общий объем баз данных: ~ 95 Гб
    • Объем базы данных master: ~ 5 Мб
    • Объем базы данных DB1: ~ 23 Гб
    • Модель восстановления базы данных DB1: Полная
    • Прирост базы данных DB1 в день: ~ 200 Мб

Резервные копии базы данных DB1:

    • Время создания полной резервной копии: ~ 5 мин.
    • Время создания копии протокола транзакций: ~ 4 сек.
    • Размер полной резервной копии (с сжатием): ~ 1,6 Гб
    • Размер копии протокола транзакций: ~ 20 Мб

Необходимое дисковое пространство для плана резервного копирования DB1:

    • Хранение полных резервных копий (1 месяц): ~ 50 Гб
    • Хранение копий протокола транзакций (1 месяц): ~ 10 Гб
    • Хранение полных копий от 1-ого числа каждого месяца (1 год): ~ 20 Гб
    • Итого размер дискового пространства: ~ 80 Гб
    • Итого размер дискового пространства в % от размера базы: ~ 350 %

Время восстановления базы данных DB1:

    • Время восстановления полной резервной копии: ~ 5 мин.
    • Время восстановления копии протокола транзакций: ~ 5 сек.

Смотрите также:

Запись опубликована в рубрике Microsoft SQL Server 2008, Microsoft SQL Server 2012 с метками Backup, SQL, Безопасность. Добавьте в закладки постоянную ссылку.

tavalik.ru

Резервное копирование БД под Microsoft SQL Server 2008R2

Резервному копированию я уже уделил ни одну тему. Настала очередь MSSQL Server. Я буду использовать следующее программное обеспечение: Windows Server 2008R2, Microsoft SQL Server 2008R2. На самом деле все достаточно просто. Мы создадим задание для резервного копирования и расписание для его выполнения.

Для начала следует убедиться что SQL Server agent (Агент SQL Server) установлен в вашей системе. Если его нет, то придется его до установить. Теперь запустим консоль управления Вашим SQL Сервером (Среда Microsoft SQL Server Management Studio) и перейдем по дереву в Агент SQL Server -> Задания. Раскроем список. Скорее всего там уже присутствует задание syspolicy_purge_history.

Правой кнопкой мыши по папке «Задания» и выбираем «Создать задание»

Откроется окно «Мастер» для создания задания. В поле «Имя» введите название Вашего задания. Желательно осмысленное, а не что то типа (Задание 1, Задание 2 и.т.д). Выберите владельца задания. В моем случае я выбираю встроенную учетную запись sa.

Перейдем на вкладку «Шаги». И нажмем кнопку «Создать» внизу окна.

Откроется мастер создания шагов для вашего задания. Шаг может быть как один, так и несколько. В нашем случае будет всего один шаг. Введите его имя «Шаг 1» и тип «Сценарий Transact-SQL (T-SQL)».

Выберите базу данных в поле «База данных». Теперь в поле ввода «Команда» введем нашу команду для резервного копирования Вашей базы данных:

BACKUP DATABASE [ИМЯ_ВАШЕЙ_БД] TO DISK = N’Полный_Путь_хранения_резервной_копии.bak’ WITH NOFORMAT, NOINIT, NAME = N’Полная База данных Резервное копирование’, SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

Жмем «Ок» и созданный Вами шаг появится в списке шагов задания.

Переходим на вкладку «Расписания».

Думаю, тут разобраться не составит труда. Я делаю ежедневное выполнение задания в 0 часов 0 минут. То есть ровно в полночь написанный сценарий будет выполнен. На этом все. Так же советую заглянуть во вкладки «Предупреждения» и «Уведомления».

Для того что бы посмотреть состояние и время последнего выполнения зайдите в категорию «Монитор активности заданий». Там можно увидеть информацию по выполнению заданий. Когда последний раз выполнялось, завершено ли успешно и т.д.

О восстановлении базы данных под SQL Server 2008R2 я напишу в другой статье.

melfis.ru

Резервное копирование баз данных Microsoft SQL Server.

Несмотря на то, что в наших предыдущих материалах мы уже касались вопроса резервного копирования баз Microsoft SQL Server, читательский отклик показал необходимость создания полноценного материала с более глубокой проработкой теоретической части. Действительно, выполненные с упором на практические инструкции статьи позволяют быстро настроить резервное копирование, но не объясняют причины выбора тех или иных настроек. Постараемся исправить этот пробел.

Модели восстановления

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

Как известно, база данных MS SQL состоит из двух частей: собственно, базы данных и лога транзакций к ней. База данных содержит пользовательские и служебные данные на текущий момент времени, лог транзакций включает в себя историю всех изменений базы данных за определенный период, располагая логом транзакций мы можем откатить состояние базы на любой произвольный момент времени.

Для использования в производственных средах предлагается две модели восстановления: простая и полная. Существует также модель с неполным протоколированием, но она рекомендуется только как дополнение к полной модели на период крупномасштабных массовых операций, когда нет необходимости восстановления базы на определенный момент времени.

Простая модель предусматривает резервное копирование только базы данных, соответственно восстановить состояние БД мы можем только на момент создания резервной копии, все изменения в промежуток времени между созданием последней резервной копии и сбоем будут потеряны. В тоже время простая схема имеет небольшие накладные расходы: вам необходимо хранить только копии базы данных, лог транзакций при этом автоматически усекается и не растет в размерах. Также процесс восстановления наиболее прост и не занимает много времени.

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

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

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

Виды резервных копий

Полная копия базы данных - как следует из ее названия, представляет собой содержимое базы данных и часть активного лога транзакций за то время, которое формировалась резервная копия (т.е. сведения обо всех текущих и незавершенных транзакциях). Позволяет полностью восстановить базу данных на момент создания резервной копии.

Разностная копия базы данных - полная копия имеет один существенный недостаток, она содержит всю информацию базы данных. Если резервные копии нужно делать довольно часто, то сразу возникает вопрос неэкономного использования дискового пространства, так как большую часть хранилища будут занимать одинаковые данные. Для устранения этого недостатка можно использовать разностные копии базы данных, которые содержат только изменившуюся со времени последнего полного копирования информацию.

Обращаем внимание, разностная копия - это данные от момента последнего полного копирования, т.е. каждая последующая разностная копия содержит в себе данные предыдущей (но при этом они могут быть изменены) и размер копии будет постоянно расти. Для восстановления достаточно одной полной и одной разностной копии, обычно последней. Количество разностных копий следует выбирать исходя из прироста их размера, как только размер разностной копии сравнится с размером половины полной, имеет смысл сделать новую полную копию.

Резервная копия журнала транзакций - применяется только при полной модели восстановления и содержит копию журнала транзакций начиная с момента создания предыдущей копии.

Важно помнить следующий момент - копии журнала транзакций никак не связаны с копиями базы данных и не содержат информацию предыдущих копий, поэтому для восстановления базы вам необходимо иметь непрерывную цепочку копий того периода, в течении которого вы хотите иметь возможность откатывать состояние базы. При этом момент последнего успешного копирования должен быть внутри этого периода.

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

Журнал транзакций

Для понимания процессов восстановления и назначения разных видов резервных копий следует более подробно рассмотреть устройство и работу журнала транзакций. Транзакция - это минимально возможная логическая операция, которая имеет смысл и может быть выполнена только полностью. Такой подход обеспечивает целостность и непротиворечивость данных при любых ситуациях, так как промежуточное состояние операции недопустимо. Для контроля над любыми изменениями в базе предназначен журнал транзакций.

При выполнении любой операции в журнал транзакций добавляется запись о начале транзакции, каждой записи присваивается уникальный номер (LSN) из неразрывной последовательности, при любом изменении данных в журнал вносится соответствующая запись, а после завершения операции в журнале появляется отметка о закрытии (фиксации) транзакции.

При каждом запуске система анализирует журнал транзакций и откатывает все незафиксированные транзакции, одновременно с этим происходит накат изменений, которые зафиксированы в журнале, но не были записаны на диск. Это дает возможность использовать кэширование и отложенную запись, не опасаясь за целостность данных даже при отсутствии систем резервного питания.

Та часть журнала, которая содержит активные транзакции и используется для восстановления данных называется активной частью журнала. Она начинается с номера, который называется минимальным номером восстановления (MinLSN).

В простейшем случае MinLSN - это номер записи первой незавершенной транзакции. Если посмотреть на рисунок выше, то открыв синюю транзакцию мы получим MinLSN равную 321, после ее фиксации в записи 324, номер MinLSN изменится на 323, что будет соответствовать номеру зеленой, еще не зафиксированной, транзакции.

На практике все немного сложнее, например, данные закрытой синей транзакции могут быть еще не сброшены на диск и перемещение MinLSN на 323 сделает восстановление этой операции невозможной. Для того, чтобы избежать таких ситуации было введено понятие контрольной точки. Контрольная точка создается автоматически при наступлении следующих условий:

  • При явном выполнении инструкции CHECKPOINT. Контрольная точка срабатывает в текущей базе данных соединения.
  • При выполнении в базе данных операции с минимальной регистрацией, например, при выполнении операции массового копирования для базы данных, на которую распространяется модель восстановления с неполным протоколированием.
  • При добавлении или удалении файлов баз данных с использованием инструкции ALTER DATABASE.
  • При остановке экземпляра SQL Server с помощью инструкции SHUTDOWN или при остановке службы SQL Server (MSSQLSERVER). И в том, и в другом случае будет создана контрольная точка каждой базы данных в экземпляре SQL Server.
  • Если экземпляр SQL Server периодически создает в каждой базе данных автоматические контрольные точки для сокращения времени восстановления базы данных.
  • При создании резервной копии базы данных.
  • При выполнении действия, требующего отключения базы данных. Примерами могут служить присвоение параметру AUTO_CLOSE значения ON и закрытие последнего соединения пользователя с базой данных или изменение параметра базы данных, требующее перезапуска базы данных.

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

Усечение журнала транзакций

Журнал транзакций, как и любой журнал, требует периодической очистки от устаревших записей, иначе он разрастется и займет все доступное место. Учитывая, что при активной работе с базой размер лога транзакций может значительно превышать размер базы, то этот вопрос актуален для многих администраторов.

Физически файл журнала транзакций является контейнером для виртуальных журналов, которые последовательно заполняются по мере роста лога. Логический журнал, содержащий запись MinLSN является началом активного журнала, предшествующие ему логические журналы являются неактивными и не требуются для автоматического восстановления базы.

Если выбрана простая модель восстановления, то при достижении логическими журналами размера равного 70% физического файла происходит автоматическая очистка неактивной части журнала, т.н. усечение. Однако это не приводит к уменьшению физического файла журнала, усекаются только логические журналы, которые после этой операции могут использоваться повторно.

Если количество транзакций велико и к моменту достижения 70% размера физического файла не окажется неактивных логических журналов, то размер физического файла будет увеличен.

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

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

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

В этом случае самое время вспомнить то, о чем мы говорили в начале статьи, если затраты на полную модель превышают затраты на восстановление следует отдать предпочтение простой модели.

Простая модель восстановления

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

Резервное копирование выполнялось раз в сутки и последняя копия была создана ночью с 21-го на 22-е. Сбой происходит вечером 22-го до создания очередной копии. В этом случае нам потребуется последовательно восстановить полную и последнюю разностные копии, при этом данные за последний рабочий день будут утеряны. Если по каким-либо причинам копия от 21-го также окажется повреждена, то мы можем восстановить предыдущую копию, потеряв еще день работы, в тоже время повреждение копии за 20-е число никак не помешает успешно восстановить данные на вечер 21-го, при наличии соответствующей копии.

Полная модель восстановления

Рассмотрим аналогичную ситуацию, но с применением полной модели восстановления. Резервные копии у нас также делаются ежесуточно, по принципу полная + разностные, а также несколько раз в сутки копируется лог транзакций.

Процесс восстановления в этом случае будет более сложен. Прежде всего потребуется создать вручную резервную копию заключительного фрагмента журнала (показана красным), т.е. часть журнала с момента прошлого создания копии и до аварии.

Если этого не сделать, то восстановить базу можно будет только до состояния на момент создания последней копии журнала транзакций.

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

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

Если последняя разностная копия будет повреждена, то в случае с простой моделью это приведет к потере еще одного рабочего дня, полная модель позволяет восстановить предпоследнюю копию, после чего нужно будет восстановить всю цепочку копий лога транзакций от момента предпоследней копии и до сбоя. Глубина восстановления зависит только от глубины непрерывной цепочки логов.

С другой стороны, если одна из копий лога транзакций будет повреждена, скажем, предпоследняя, то восстановить данные мы сможем только на момент последней резервной копии + период в неповрежденной цепочке копий журналов. Например, если журналы делались в 12, 14 и 16 часов и поврежден журнал, созданный в 14 часов, то располагая суточной копией мы сможем восстановить базу до момента окончания непрерывной цепочки, т.е. до 12 часов.

источник: http://interface31.ru/tech_it/2015/02/rezervnoe-kopirovanie-baz-dannyh-m...

fadmin.ru