Содержание
- 1 Как создать архив базы SQL сервера
- 2 Создаем архив базы SQL сервера через запрос
- 3 Автоматическое создание backup на SQL сервере
- 4 понедельник, 10 марта 2014 г.
- 5 Введение в резервное копирование и восстановление базы данных MS SQL Server 2008, 2008 R2, 2012 и 2014
- 5.1 Резервное копирование MS SQL Server 2008, 2008 R2, 2012 и 2014
- 5.1.1 Полное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014
- 5.1.2 Дифференциальное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014
- 5.1.3 Резервное копирование журнала транзакций MS SQL 2008, 2008 R2, 2012 и 2014
- 5.1.4 Настройка резервного копирования MS SQL и конфигурирование БД
- 5.2 Восстановление базы MS SQL из бэкапа
- 5.1 Резервное копирование MS SQL Server 2008, 2008 R2, 2012 и 2014
Продолжаем разговаривать о резервном копировании и сегодня мы научимся создавать архив базы Microsoft SQL Server 2008. Рассматривать все будем как обычно на примерах с использованием, как графического интерфейса, так и с использованием SQL запроса, а также настроим автоматическое создание backup с помощью батника.
К вопросу о важности резервного копирования базы данных мы возвращаться не будем, так как мы уже не раз поднимали эту тему, например в материалах:
И в последней статье я говорил, что мы рассмотрим возможность создания архива на СУБД MS SQL Server 2008, поэтому сейчас мы займемся именно этим.
И так как теории уже было много сразу перейдем к практике, а именно к созданию backup базы.
Примечание! Как видно их названия статьи архив мы будем делать на СУБД Microsoft SQL 2008 с использованием Management Studio. Сервер располагается локально. ОС Windows 7.
Как создать архив базы SQL сервера
Давайте определимся, что архив мы будем делать тестовой базы с названием «test». С начала через графический интерфейс, и в процессе этого запишем сценарий, для того чтобы в дальнейшем можно было просто запустить его и уже не отвлекаться на ввод всякого рода параметров.
Открываем Management Studio, раскрываем «Базы данных», выбираем нужную базу, щелкаем правой кнопкой мыши по ней, выбираем Задачи->Создать резервную копию
У Вас откроется окно «Резервное копирование базы данных», где Вы можете задать параметры архивирования. Я всего лишь задал имя «Резервного набора данных», а также изменил название архива и путь, так как по умолчанию он будет создаваться в папку Program Files, например, у меня по умолчанию был путь
C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackup
Для примера я изменил его на C: emp и назвал архив test_arh.bak
Также если перейти на вкладку «Параметры», то можно задать настройку перезаписывать все наборы данных, сейчас объясню что это. Если Вы оставите все как есть, т.е. добавлять к существующему набору данных, то у Вас файл с резервной копией будет один, но с несколькими экземплярами наборов данных, т.е. при восстановлении Вы просто выберете нужный Вам набор. А если поставите «Перезаписывать все существующие резервные наборы данных», то набор всегда будет один, тогда в этом случае Вам необходимо будет создавать архивы (допустим ежедневные) с разными названиями. Я поставил перезаписывать, так как допустим, в дальнейшем, я планирую создавать архивы за каждый день с указанием даты в названии этих архивов, чтобы оперативно в случае необходимости скопировать нужный мне backup за определенную дату в любое место.
И кстати, на этом моменте, после ввода всех параметров, можно создать сценарий, для того чтобы записать его и в дальнейшем использовать. Для этого просто вверху нажимаем «Сценарий».
И в результате этого действия у Вас откроется окно запросов, в котором и будет код данного сценария. К нему вернемся чуть позже, а пока жмем «ОК» и после завершения операции у Вас появится окно, в котором будет указан результат выполнения резервного копирования, если все хорошо, то будет следующее сообщение
Создаем архив базы SQL сервера через запрос
Если Вы все сделали, как указанно выше (т.е. нажали «Сценарий»), то у Вас открылось окно запросов, в котором собственно и есть сам запрос создания архива, но мы его немного переделаем, так как я сказал, что мы планируем запускать его каждый день, поэтому чтобы название было соответствующее, мы напишем вот такую SQL инструкцию.
И теперь если мы запустим его, то у нас создастся резервная копия базы данных с названием test_arh_Текущая дата.bak
Автоматическое создание backup на SQL сервере
Для этих целей в MS SQL 2008 существует специальная возможность под названием «Планы обслуживания», где как раз можно настроить расписание по созданию резервной копии баз данных, но я предлагаю для этих целей использовать bat-файл чтобы его настроить в планировщике и чтобы он каждый день запускался и делал backup базы данных.
Для этого скопируйте SQL инструкцию, которую мы рассмотрели выше, и вставляйте ее в блокнот (рекомендую Notepad++), затем сохраняете с расширением .sql т.е. этот сценарий будет выполняться на MS Sql 2008. Затем нам останется написать батник, для того чтобы он подключился к SQL серверу и выполнил наш сценарий. Также в блокноте пишем:
где, я создал переменную cur_date для того чтобы в ней хранить текущую дату, затем подключаюсь к локальному серверу, через утилиту osql, которая использует ODBC и выполняю наш сценарий (я его назвал test.sql), а также записываю лог, где и как раз нам понадобилась наша переменная, все, сохраняем с расширением .bat, создаем задание в планировщике и можно сказать забываем про процесс архивирования базы данных, ну только периодически проверяем, создался ли архив или нет.
Для основ этого совсем достаточно, теперь Вы знаете, как можно создавать резервные копии баз данных на 2008 SQL сервере, в следующей статье мы рассмотрим, как можно восстановить базу данных на MS SQL Server 2008. А пока все! Удачи!
Разговоры про IT и не только
понедельник, 10 марта 2014 г.
Резервное копирование базы данных MS SQL 2008 R2
«Кто владеет информацией – тот владеет миром» – Майер Амшель Ротшильд
Самое ценной сущностью в любом бизнесе является информация. Потеря информации может привести к непредсказуемым последствиям, в основном финансовым. Поэтому одной из главной задач IT специалистов является резервное копирование всей IT инфраструктуры. Это относится и базам данных MS SQL Server.
Для того, чтобы обеспечить сохранность информации в используемых базах данных, а также снизить время для восстановления работоспособности, необходимо производить периодическое резервное копирование SQL-серверов.
Разберем на простейшем примере: необходимо настроить резервное копирование базы данных на отдельный диск.
- Открываем Microsoft SQL Server Management Studio. В навигационном меню справа открываем вкладку "Управление". Там видим вкладку "Планы обслуживания". Нажимаем правой кнопкой мыши ->"Создать план обслуживания" и даем имя нашему плану (Рис.1):
Введение в резервное копирование и восстановление базы данных MS SQL Server 2008, 2008 R2, 2012 и 2014
Обширный функционал Bacula Enterprise Edition, помимо прочего, позволяет быстро и просто создавать бэкапы БД под Windows. Например, речь идет об инструменте, с помощью которого можно осуществлять резервное копирование MS SQL Server. Сделать бэкап MS SQL пользователь может, создавая резервные копии специфических баз данных MS SQL больших объемов, используемых платформой Windows, при меньших затратах на ПО сторонних производителей, с возможностью восстановления данных до определенного момента времени (PITR-восстановление) на сетевой и локальный диск.
Скрипт Bacula Systems для создания бэкапов MS SQL Server характеризуется крайней эффективностью, достигаемой за счет реализации современной, высоконадежной архитектуры. Более того, ПО позволяет сделать бэкап MS SQL Server, использовать самые различные возможности по созданию резервных копий MS SQL.
Скрипт бэкапа MS SQL Bacula Systems функционирует независимо от VSS. Это значит, что инструмент резервного копирования MS SQL не использует снапшоты VSS для создания бэкапов. Поэтому пользователь может задать следующее значение “Enable VSS = no” в Bacula FileSet. Эффективное создание бэкапов MS SQL Server и их восстановление с помощью данного решения достигаются за счет использования Microsoft API для SQL Server. Благодаря этому Bacula Systems может поддерживать работу механизмов обеспечения защиты и все типы проверки подлинности, реализованные в Microsoft SQL Server.
Резервное копирование журнала транзакций MS SQL и восстановление MS SQL на момент времени: ПО Bacula Enterprise Edition позволяет восстанавливать блоки данных MS SQL или конкретные настройки до определенного момента времени. Благодаря реализации моделей полного восстановления и восстановления с неполным протоколированием вы сможете восстанавливать MS SQL, используя PITR-восстановление, либо использовать LSN для восстановления системы до конкретного состояния. Вы можете восстанавливать определенное состояние базы данных MS SQL на любой конкретный момент времени с точностью до секунды. В случае бэкапа журнала транзакций MS SQL, при восстановлении состояние БД будет восстанавливаться из различных выбранных бэкапов.
Краткий обзор функций автоматического бэкапа и восстановления MS SQL с Bacula Enterprise
Компания Bacula Systems создала плагин для резервного копирования MS SQL Server для совместного использования с Bacula Enterprise Edition. Бэкап MS SQL Server с Bacula обладает следующими функциями:
- Поддержка полного и дифференциального резервного копирования MS SQL
- Поддержка инкрементального резервного копирования MS SQL
- Резервное копирование MS SQL на сетевой и локальный диск
- Резервное копирование MS SQL по расписанию
- Создание бэкапов на уровне базы данных MS SQL Server
- Возможность включать/исключать БД из процедуры создания бэкапов
- Поддержка создания бэкапов БД «только для чтения»
- Восстановление MS SQL бэкапов на диск
- Отправка потока резервной копии напрямую в Storage Daemon
- Восстановление MS SQL на момент времени
Обзор и настройка резервного копирования MS SQL 2008, 2008 R2, 2012 и 2014
В данном документе представлены решения для Bacula Enterprise Edition 8.4 и более поздних версий, которые не поддерживаются ранними версиями ПО. Резервное копирование базы MS SQL был протестировано и поддерживается MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. Возможна работа резервного копирования MS SQL от Bacula с SQL Express.
Глоссарий резервного копирования MS SQL 2008, 2008 R2, 2012 и 2014
- MS SQL означает Microsoft SQL Server.
- Журнал транзакций (transaction log). Любая база данных MS SQL Server имеет журнал транзакций, в который записываются все транзакции и модификации БД, выполненные в ходе таких транзакций. Журнал транзакций – важный элемент БД. В случае отказа системы журнал транзакций может потребоваться для восстановления БД до рабочего состояния. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms190925.aspx.
- Дифференциальное резервное копирование базы данных MS SQL Server. Дифференциальный бэкап основан на последнем полном бэкапе БД. В ходе выполнения дифференциального бэкапа захватываются только те данные, которые были изменены с момента создания последнего полного бэкапа. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en- us/library/ms175526.aspx.
- Полное резервное копирование базы данных MS SQL Server. В ходе полного бэкапа БД создается резервная копия всей базы данных. Бэкап включает часть журнала транзакций с целью восстановления полной БД из резервной копии. Полные бэкапы БД содержат БД на момент завершения создания резервной копии. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en- us/library/ms186289.aspx.
- Бэкап «только для копирования» (CopyOnly). Бэкапы «только для копирования» представляют собой бэкапы MS SQL, независящие от обычной последовательности создания традиционных резервных копий SQL Server. Иногда полезно создавать бэкапы для особых нужд, не влияя на общий процесс резервного копирования и восстановления БД. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms191495.aspx.
- VDI (Интерфейс виртуального устройства) – это технология Microsoft, позволяющая создавать именованный канал между программами.
- стандартные маски задают наборы строк с подстановочными знаками. Например, стандартная маска production* будет включать строки production1 и production2.
- строка
- целое число.
- LSN Каждая запись в журнале транзакций MS SQL Server обозначается с помощью уникального регистрационного номера транзакции (LSN). Более подробную информацию вы найдете по ссылке https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.
Резервное копирование MS SQL Server 2008, 2008 R2, 2012 и 2014
Полное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014
В ходе полного резервного копирования базы данных MS SQL сохраняются файлы БД и журнал транзакций, что позволяет полностью защитить базу MS SQL на случай отказа носителя. В случае повреждения одного или более файлов восстановление базы MS SQL из бэкапа позволит восстановить все совершенные транзакции. Также будет произведен откат всех транзакций, находившихся в процессе выполнения. В данном режиме производится создание бэкапов БД master и mbdb.
Дифференциальное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014
Дифференциальный бэкап базы MS SQL Server основан на самом последнем полном бэкапе базы данных MS SQL. В ходе создания дифференциального бэкапа MS SQL захватываются только те данные, которые были изменены с момента создания последнего полного бэкапа MS SQL. Для функции дифференциального бэкапа MS SQL крайне важна последовательность бэкапов. Если по какой-то причине полный бэкап, на который ссылается MS SQL, не доступен, дифференциальные бэкапы базы данных MS SQL Server нельзя будет использовать. Резервное копирование MS SQL от Bacula использует определенные методы для решения данной проблемы. Поэтому, в случае возникновения сложностей, статус дифференциального бэкапа БД может быть автоматически повышен до полного бэкапа.
Резервное копирование журнала транзакций MS SQL 2008, 2008 R2, 2012 и 2014
Функция создания бэкапа журнала транзакций MS SQL реализуется на инкрементальном уровне с помощью ПО Bacula. БД MS SQL должна быть сконфигурирована с помощью моделей полного восстановления и восстановления с неполным протоколированием. Если MS SQL использует простую модель восстановления, файл журнала транзакций будет «урезаться» после каждой контрольной точки, и бэкап журнала транзакций не позволит реализовать восстановление до выбранной конкретной точки, т.е. PITR-восстановление. Можно будет восстановить базу данных MS SQL полностью, но нельзя будет выбрать контрольную точку. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms189275.aspx.
Настройка резервного копирования MS SQL и конфигурирование БД
Необходимо всегда создавать резервную копию БД master. Если БД master будет так или иначе повреждена, например, в результате отказа носителя, возможно, не получится запустить инстанс MS SQL. В таком случае, необходимо восстановить БД master, и только потом восстановить из резервной копии саму БД. Возможно создание только полных бэкапов базы MS SQL. Более подробную информацию вы найдете по ссылке https://technet.microsoft.com/en-s/library/aa213839%28v=sql.80%29.aspx.
Восстановление базы MS SQL из бэкапа
Вы можете использовать все стандартные способы запуска процедуры восстановления базы MS SQL из бэкапа. Однако вы должны убедиться в том, что, в случае восстановления дифференциальных данных, будет также восстановлен полный предыдущий бэкап базы MS SQL. В таком случае восстановление происходит автоматически, если вы запускаете его в консоли bconsole с помощью вариантов восстановления 5 или 12. В сгенерированной файловой структуре вам необходимо отметить восстановление полных БД или инстансов БД.
Варианты восстановления базы MS SQL из бэкапа
ПО Bacula Enterprise Edition позволяет пользователям использовать множество вариантов восстановления MS SQL и применять самые различные способы «отката» БД. Наиболее часто используемые варианты восстановления описаны ниже:
- параметр Where: В случае с Bacula Enterprise Edition, данный параметр позволяет администратору восстанавливать БД в конкретном месте.
- параметр Replace: Используется для того, чтобы определить, как ПО Bacula должно вести себя с текущей БД при восстановлении. Резервное копирование MS SQL от Bacula также позволяет использовать еще несколько опций при восстановлении, например:
- Instance: Поскольку MS SQL использует несколько инстансов, бэкап базы MS SQL от Bacula позволяет выбирать, какой из инстансов следует восстанавливать. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа. По умолчанию, используется инстанс с именем “MSSQLSERVER”.
- Database. Данная опция указывает имя БД для восстановления и она использует значение, заданное в момент создания БД. Данный параметре является опциональным. По умолчанию резервное копирование баз данных SQL Server использует параметр Where для определения имени новой БД. Если обоим параметрам Where и Database назначены валидное имя БД, то параметр Database будет использоваться.
- User. Имя пользователя, используемое для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
- Password. Пароль, используемый для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
- Domain. Домен, используемый для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
- Recovery. Параметр позволяет определить, будет ли произведен откат БД к предыдущему состоянию при восстановлении или нет. По умолчанию при восстановлении БД будет произведет откат к предыдущему состоянию.
- Stop_before_mark. Условие WITH STOPBEFOREMARK =
используется для того, чтобы указать, что запись в журнале транзакций, которая находится непосредственно перед флагом, и является точкой восстановления. Точкой восстановления может служить дата и время, номер LSN или имя флага mark_name.
Stop_at_mark. Условие WITH STOPATMARK =
используется для того, чтобы показать, что помеченная транзакция является точкой восстановления. STOPATMARK перемещается вперед к флагу и включает повтор помеченной транзакции. Точкой восстановления может служить дата и время, номер LSN или имя флага mark_name.
В программе BWeb Management Suite, созданной Bacula Systems, настройка резервного копирования MS SQL находятся на вкладке восстановления.
Рисунок 1: Вкладка восстановления БД при использовании программы BWeb Management Suite
Восстановление MS SQL на момент времени
Данный вопрос относится только к тем БД SQL, которые используют модели полного восстановления и восстановления с неполным протоколированием. В случае модели восстановления с неполным протоколированием, если бэкап журнала содержит изменения, сделанные во время операций массовой обработки данных, то невозможно будет выполнить восстановление на какой-либо момент времени в пределах этого бэкапа. БД должна быть восстановлена до конца журнала транзакций, бэкап которого был создан. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms179451.aspx.
Восстановление MS SQL на момент времени можно совершать непосредственно из плагина бэкапа MS SQL. Также можно восстанавливать файлы локально и выполнять операции из консоли управления Microsoft SQL Server Mangement Console, чтобы иметь возможность использовать больше возможностей.
LSN номера используются для создания последовательности восстановления MS SQL с целью отслеживания момента времени, до которого данные были восстановлены. При восстановлении MS SQL из бэкапа данные восстанавливаются до LSN номера, соответствующего моменту времени, в который был выполнен бэкап. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms190925.aspx.
LSN номер записи в журнале, в момент которой возникло конкретное событие по созданию бэкапа и восстановлению, можно просмотреть одним из следующих способов:
- При выводе описания заданий по созданию бэкапа с помощью ПО Bacula
- В названии файла журнала
- В таблице msdb.backupset
- В таблице msdb.backupfile
При выполнении задания по созданию бэкапа базы MS SQL при выводе описания задания отобразится следующая информация о LSN номерах:
Номер First LSN соответствует последнему LSN номеру последнего бэкапа журнала транзакций. Таким бэкапом может являться самый первый полный бэкап или последний бэкап (инкрементальный).
Номер Last LSN соответствует последней транзакции, зарегистрированной в журнале.
В случае бэкапа журнала транзакций (инкрементального), название файла, связанного с этой БД, в задании по созданию инкрементального бэкапа будет выглядеть следующим образом:
Число в названии, в нашем случае 42000162001, соответствует последнему LSN номеру предыдущего задания (по созданию полного или инкрементального бэкапа).
Рисунок 2: Первый номер LSN, последний номер LSN и номера LSN в названии файлов
Как показано в примере на рисунке 2, если администратору необходимо восстановить базу данных MS SQL в состояние, соответствующем LSN номеру 14, можно выполнить следующие действия:
- В меню восстановления БД используйте опцию 5
- Выберите директорию БД “/@mssql/db29187”
- Выберите последний файл полного бэкапа “data.bak” (LSN: 10)
- Выберите инкрементальный бэкап “log-10.trn”
- Задайте параметр stop_at_mark равный “lsn:14”
- Запустите задачу по восстановлению бэкапа
Или, если последний полный бэкап MS SQL Server не доступен, однако доступен предыдущий полный бэкап, то:
-
Используйте опцию восстановления 3, выберите соответствующие значения job > Сценарии восстановления MS SQL
| Описание | Where | Database | Пример |
| Восстановить файлы на диск | Путь | where=c:/tmp | |
| Восстановить исходную БД | where=/ | ||
| Восстановить с новым именем | Имя | where=newdb | |
| Восстановить с новым именем | Имя | database=newdb | |
| Восстановить с новым именем и переместить файлы | Имя |
