Меню
ГлавнаяФото и видео

Sql server express резервное копирование. Создание автоматического бэкапа SQL-базы на сервере SQL Express Edition. Восстановление базы данных из резервной копии

После изучения множества информации с разных источников, решил описать процесс настройки резервного копирования БД MS SQL Server для полной модели восстановления, какую модель использовать решать Вам, но от себя добавлю, что если в вашей БД большой поток информации (например создаются десятки, сотни или тысячи документов в 1 час), то потеря информации за день работы будет просто неприемлемой, в таком случае только полная модель обеспечит сохранность ваших данных. Данна статья предназначена для начинающих системных администраторов и содержит по моему мнению минимальный набор действий для резервного копирования БД 1С. Установка\Настройка самого SQL сервера и развертывание БД на нём в не рамках данной статьи.

Все настройки будем производить с помощью SQL Management Studio. Для начала нужно создать Устройство резервного копирования, можно и не создавать, но на мой взгляд это гораздо удобнее и правельнее. в оснастке SQL Management Studio -> Объекты сервера-> Устройства резервного копирования. Нужно указать имя устройства и файл в котором будут храниться резервные копии (лучше с расширением BAK), в дальнейшем можно посмотреть содержимое носителя, там будут перечислены все резервные копии.

Теперь можно приступать к настройки Плана обслуживания (Maintenance Plan). План Обслуживания можно создать сразу для всех БД, но удобнее для каждой БД создать свой план обслуживания.

В нашем Плане обслуживания будет три подплана: 1 - резервное копирование БД (Полное); 2 - резервное копирование БД (Разностное); 3 - Резервное копирование Журнала транзакций. У каждого подплана есть свое расписание выполнения. Раписание каждый настраивает по своим усмотрениям, в моём же случае полное копирование делается раз в неделю в воскресенье, Разностное копирование каждый день кроме воскресенья, ЖТ - журнал транзакций каждый час. При такой модели резервирования можно восстановить искомую БД на любую дату и час, причем экономим пространство на жёстком диске т.к. полное резервирование выполняется фактически раз в неделю, а в течении недели только изменения.

Настройка дневного расписания. Недельное отличается только установленной галочкой "Воскресенье" и снятыми с "понедельника" по "Субботу"

Расписание для ЖТ. Красным выделено время сохранения в течении дня, имеет смысл например, если пользователи работают с БД в определённый период, если режим работы 24х7, то оставляем по уполчанию.

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

Задачи. В каждую задачу нужно зайти и выбрать БД, для которой она будет выполняться и ряд других настроеек (если есть). Рассмотрим, какие задачи содержит недельный подплан нашего плана обслуживания.

1. "Проверка целостности БД" (Check Database Integrity Task). Следующая задача будет выполняться, только если БД не содержит ошибок. (Замем резервировать БД с ошибками?)

2. "Восстановить индекс" (Rebuild Index Task). Восстановить (Перестроить) индекс необходимо каждый день, т.к. при работе с индексами они сильно фрагментируются и при фрагментации более 25% SQL начинает заметно "тормозить". Эта операция довольно ресурсоёмка, поэтому её можно делать хотябы раз в неделю, а в дневном подплане заменить её менее ресурсоёмкую задачу "Реорганизация индекса".

3. "Обновить статистику" (Update Statistics Task). Для оптимизации... Кстати эту задачу можно выполнять несколько раз в течении дня, если ваша БД сильно нагружена.

4. После обновления статистики ОБЯЗАТЕЛЬНО нужно очистить процедурный кэш. Для этого перетаскиваем в редактор задачу "Выполнение инструкции T-SQL" и в поле "инструкция T-SQL:" написать процедуру DBCC FREEPROCCACHE . Но нужно учесть, что эта процедура очищает кэш у ВСЕХ БД, а мы обновили статистику по одной! Как очистить процедурный кэш для определённой БД, читаем . Вкратце: DBCC FLUSHPROCINDB(ID_БД)

5. "Резервное копирование БД" (Back Up Database Task). В этой задаче указываем какую БД мы резервируем, тип резервной копии (Для недельного подплана - Полное, для дневного - Разностное, для часового - Журнал транзакций.) Ставим переключатель в положение "Создать резервную копию баз данных в одном или нескольких файлах" и добавляем ранее созданное устройство резервного копирования. В таком случае ВСЕ копии сохраняются в один файл, который указали при создании устройства резервного копирования, если переключатель оставить в "Создать файл резервной копии для каждой базы данных", то на каждое резервное копирование будет создаваться отдельный файл и на Полное и на Разностное и на ЖТ, что очень неудобно при восстановлении, зато удобно при хранении. Не забываем укзать что нужно сжимать резервные копии!

6. "Очистка Журнала" Очищает записи, создаваемые при выполнении задач. Также можно включить задачу "Очистка после обслуживания" и настроить её для удаления текстовых логов или устаревших резервных копий.

Подплан для резервирования ЖТ, состоит из одной задачи "Резервное копирование базы данных".ЖТ для меня удобнее сохранять не в Устройство резервного копирования, а в отдельный файл, что необходимо указать в настройке задачи.

Обширный функционал Bacula Enterprise Edition, помимо прочего, позволяет быстро и просто создавать бэкапы БД под . Например, речь идет об инструменте, с помощью которого можно осуществлять резервное копирование 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 и конфигурирование БД

Восстановление базы 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.
  • Stop_at=. Условие WITH STOPAT = используется для того, чтобы указать, что точкой восстановления является дата/время.
  • Restrict_user. Условие WITH RESTRICT_USER используется для ограничения доступа к восстановленной БД. По умолчанию используется значение no.

Восстановление MS SQL на момент времени можно совершать непосредственно из плагина бэкапа MS SQL. Также можно восстанавливать файлы локально и выполнять операции из консоли управления Microsoft SQL Server Mangement Console, чтобы иметь возможность использовать больше возможностей.

LSN

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
  • Выберите последний файл полного бэкапа “data.bak” (LSN: 10)
  • Выберите инкрементальный бэкап “log-10.trn”

Или, если последний полный бэкап MS SQL Server не доступен, однако доступен предыдущий полный бэкап, то:

  • Используйте опцию восстановления 3, выберите соответствующие значения jobids
  • Выберите директорию БД “/@mssql/db29187”
  • Выберите файл полного бэкапа “data.bak” (LSN: 2)
  • Выберите инкрементальные бэкапы “log-2.trn”, “log-3.trn”, “log-10.trn”
  • Задайте параметр stop_at_mark равный “lsn:14”
  • Запустите задачу по восстановлению бэкапа

Сценарии восстановления MS SQL

Описание Where Database Пример
Восстановить файлы на диск Путь where=c:/tmp
Восстановить исходную БД where=/
Восстановить с новым именем Имя where=newdb
Восстановить с новым именем Имя database=newdb
Восстановить с новым именем и переместить файлы Имя

Таблица 1: Сценарии восстановления MS SQL

2.3.1 Восстановление базы MS SQL с исходным именем

Чтобы восстановить БД с исходным именем, параметр Where должен быть не задан (пустое значение), либо должно быть задано значение “/”, а параметру Replace должно быть присвоено значение Always , или же сначала необходимо удалить исходную БД.

Восстановление бэкапа MS SQL с новым именем

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

Если исходная БД более не доступна, то параметр where , либо поле “Plugin Options” может содержать название новой БД. Резервное копирование MS SQL от Bacula автоматически создаст БД с новым именем.

Если исходная БД все еще пока требуется, параметр where будет использоваться для перемещения файлов на диск, и необходимо будет задать название новой БД с помощью меню “Plugin Options”. В дереве восстановления необходимо выбрать файл layout.dat.

Используя каталог My Catalogue

Запустите задачу восстановления MS SQL:

Используя каталог My Catalogue, запустите задачу восстановления базы MS SQL:

Восстановление MS SQL на локальный диск

Если указать where=c:/path/ , файлы будут восстановлены на локальный диск, и администратор базы данных MS SQL сможет использовать процедурное расширение TSQL для консоли управления Microsoft SQL Server Mangement Console для восстановления БД. Команды SQL, необходимые для восстановления БД, перечислены в описании Job output как показано на рисунке ниже.

2. Просмотр информации о событиях резервного копирования и восстановления для базы данных

Для того чтобы узнать, когда производилось создание резервных копий конкретной базы данных, а также восстановление базы данных из резервной копии, можно воспользоваться стандартным отчетом «» (Backup and Restore Events). Для формирования данного отчета необходимо в Обозревателе объектов (Server Oblects) кликнуть правой кнопкой мыши по соответствующей базе данных, в контекстном меню выбрать «Отчеты » (Reports) — «Стандартный отчет » (Standart Reports) — «События резервного копирования и восстановления » (Backup and Restore Events).

Сформировавшийся отчет содержит в себе следующие данные:

  • Среднее время, затрачиваемое на операции резервного копирования (Average Time Taken For Backup Operations)
  • Успешные операции резервного копирования (Saccessful Backup Operations)
  • Ошибки операции резервного копирования (Backup Operation Errors)
  • Успешные операции восстановления (Saccessful Restore Operations)

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

Помогла ли Вам данная статья?

Script для динамического резервного копирования всех баз данных на сервере. Затем создайте пакетный файл в соответствии со статьей. Полезно создавать два командных файла: один для полного резервного копирования и один для резервного копирования. Затем создайте две задачи в планировщике заданий, один для полного и один для diff.

-- // Copyright © Microsoft Corporation. All Rights Reserved. -- // This code released under the terms of the -- // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE GO /****** Object: StoredProcedure . ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Microsoft -- Create date: 2010-02-06 -- Description: Backup Databases for SQLExpress -- Parameter1: databaseName -- Parameter2: backupType F=full, D=differential, L=log -- Parameter3: backup file location -- ============================================= CREATE PROCEDURE . @databaseName sysname = null, @backupType CHAR(1), @backupLocation nvarchar(200) AS SET NOCOUNT ON; DECLARE @DBs TABLE (ID int IDENTITY PRIMARY KEY, DBNAME nvarchar(500)) -- Pick out only databases which are online in case ALL databases are chosen to be backed up -- If specific database is chosen to be backed up only pick that out from @DBs INSERT INTO @DBs (DBNAME) SELECT Name FROM master.sys.databases where state=0 AND name=@DatabaseName OR @DatabaseName IS NULL ORDER BY Name -- Filter out databases which do not need to backed up IF @backupType="F" BEGIN DELETE @DBs where DBNAME IN ("tempdb","Northwind","pubs","AdventureWorks") END ELSE IF @backupType="D" BEGIN DELETE @DBs where DBNAME IN ("tempdb","Northwind","pubs","master","AdventureWorks") END ELSE IF @backupType="L" BEGIN DELETE @DBs where DBNAME IN ("tempdb","Northwind","pubs","master","AdventureWorks") END ELSE BEGIN RETURN END -- Declare variables DECLARE @BackupName varchar(100) DECLARE @BackupFile varchar(100) DECLARE @DBNAME varchar(300) DECLARE @sqlCommand NVARCHAR(1000) DECLARE @dateTime NVARCHAR(20) DECLARE @Loop int -- Loop through the databases one by one SELECT @Loop = min(ID) FROM @DBs WHILE @Loop IS NOT NULL BEGIN -- Database Names have to be in format since some have - or _ in their name SET @DBNAME = "["+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+"]" -- Set the current date and time n yyyyhhmmss format SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),"/","") + "_" + REPLACE(CONVERT(VARCHAR, GETDATE(),108),":","") -- Create backup filename in path\filename.extension format for full,diff and log backups IF @backupType = "F" SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, "[",""),"]","")+ "_FULL_"+ @dateTime+ ".BAK" ELSE IF @backupType = "D" SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, "[",""),"]","")+ "_DIFF_"+ @dateTime+ ".BAK" ELSE IF @backupType = "L" SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, "[",""),"]","")+ "_LOG_"+ @dateTime+ ".TRN" -- Provide the backup a name for storing in the media IF @backupType = "F" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" full backup for "+ @dateTime IF @backupType = "D" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" differential backup for "+ @dateTime IF @backupType = "L" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" log backup for "+ @dateTime -- Generate the dynamic SQL command to be executed IF @backupType = "F" BEGIN SET @sqlCommand = "BACKUP DATABASE " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH INIT, NAME= """ +@BackupName+""", NOSKIP, NOFORMAT" END IF @backupType = "D" BEGIN SET @sqlCommand = "BACKUP DATABASE " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH DIFFERENTIAL, INIT, NAME= """ +@BackupName+""", NOSKIP, NOFORMAT" END IF @backupType = "L" BEGIN SET @sqlCommand = "BACKUP LOG " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH INIT, NAME= """ +@BackupName+""", NOSKIP, NOFORMAT" END -- Execute the generated SQL command EXEC(@sqlCommand) -- Goto the next database SELECT @Loop = min(ID) FROM @DBs where ID>@Loop END

И пакетный файл может выглядеть так:

Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="F"" >> c:\Dropbox\backup\DB\full.log 2>&1

Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="D"" >> c:\Dropbox\backup\DB\diff.log 2>&1

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

Разностное резервное копирование основано на самой последней предыдущей полной резервной копии данных. В разностной резервной копии сохраняются только те изменения, которые были произведены с момента создания последней полной резервной копии.
Рекомендации:
  1. Используйте разностные копии БД, если создание полной копии БД занимает большой промежуток времени
  2. Периодически делайте полную копию БД, чтобы уменьшить объемы создаваемых разностных копий.
  3. После создания полной копии БД, все предыдущие разностные копии теряют свою актуальность.
Более подробно о рекомендациях по частоте созданию разностных резервных копий, можно прочитать .

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

Пример SQL для создания резервной разностной копии БД с проверкой копии по завершению (отличается от полного копирования флагом DIFFERENTIAL вместо него нужно использовать NOFORMAT ).

Declare @pathBackup as varchar(55) set @pathBackup = N"C:\Backup\[Имя файла БД]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + ".bak" BACKUP DATABASE [Имя базы данных] TO DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N"Полная База данных Резервное копирование", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId as int declare @pathBackup as varchar(55) set @pathBackup = N"C:\Backup\[Имя файла БД]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + ".bak" select @backupSetId = position from msdb..backupset where database_name=N"[Имя базы данных]" and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N"[Имя базы данных]") if @backupSetId is null begin raiserror(N"Ошибка верификации. Сведения о резервном копировании для базы данных "[Имя базы данных]" не найдены.", 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO

3.Системные базы данных
Помимо основной базы и связанных с ней файлов, я настоятельно рекомендую делать копии и системных баз данных. Начнем с того, что рассмотрим какие базы существуют в MS SQL. Их всего 5:

Я выбрал резервировать только 2 системные БД:

  1. msdb – потому что, там хранятся настроенные задачи и другие
  2. master – хранятся все произведенные настройки SQL Server.
Данная информация все равно не сильно критична и ее можно восстановить руками, но зачем тратить лишнее время, когда можно просто взять из резервной копии.
4. План бекапирования
На основе выше описанного составим наш план резервного копирования данных. Он может отличаться от того, что потребуется вам, все зависит от требований к восстановлению БД. Когда я подготавливал план, мне пришлось учесть, что необходимо восстановить данные максимально и потеря данных составляла не больше одного часа.

Мы будем делать следующие резервные копии:

  • Полная копия основной БД, чаще чем раз в неделю нет необходимости
  • Разностная копия основной БД, каждый день
  • Копии журнала транзакций основной БД, каждый час
  • Копия системной БД master, раз в неделю
  • Копия системной БД msdb, раз в неделю
В итоге у нас получился следующий план резервного копирования данных:
День недели
Время
Действия
Частота
Описание
Понедельник - Пятница
 С 8-00 до 21-00
 Резервные копии

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

 Каждый час
 После выполнения резервной копии БД идет сжатие и усечение журнала транзакций
Суббота - Воскресенье
 С 8-00 до 18-00
Понедельник - Воскресенье
 22-00
 Разностная копия основной БД
 1 раз в день
 После успешного выполнения разностной копии удаляются все старые копии журнала транзакций
Суббота
 12-00
 Проверка БД
 1 раз в день
 Проверка БД Дело на целостность.
Суббота
 18-00
 Создание полной копии БД
 1 раз в день
 По завершению данной операции идет уведомление на почту.

Если создание резервной копии прошло удачно, удаляется

  • старая полная резервная копия
  • все старые разностные копии
  • все старые журналы транзакций
Понедельник - Воскресенье
 23-30
 Создание копии системной базы master
 1 раз в день
Воскресенье
 12-30
 Создание копии системной базы msdb
 1 раз в месяц
 Хранится всегда только последний экземпляр БД
  1. Используйте опцию BACKUP WITH CHECKSUM
    чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему.
  2. Не выполняйте резервное копирование файлов на тот же физический диск, на котором хранится база данных или протокол транзакций.
  3. Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию: USE master; GO EXEC sp_configure ‘backup compression default’, "1"; RECONFIGURE WITH OVERRIDE;
  4. держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой.
  5. Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах. DBCC CHECKDB ("Имя базы данных") WITH NO_INFOMSGS, ALL_ERRORMSGS; Примечание: на практики мы использовали данную проверку, только перед выполнением полной резервной копии.
  6. Выполняйте периодически обновление статистики и реорганизации индексов БД

Используем приложение

Несколько нюансов по приложению:
  • Все тексты и запросы в коде вынесены в ресурсы, мне так было проще
  • При вводе параметров соединения и других настроек, они сохраняются в файл. Для Express и Standart используются разные файлы (dbStandart, udExpress) в них хранится класс UserData
  • Для выполнения некоторых операций могут потребоваться права администратора
  • На данный момент не работает соединение с БД под доменной учетной записью
  • Программа не обладает суперкрасивым интерфейсом
1. Настройка уведомления администратора
Мне было лень каждый раз заходить на сервер и проверять, сработала ли задача или произошла какая-то ошибка. Да и хотелось иметь возможность получать другие уведомления, не только о выполнения задач.

Для данной цели используется DatabaseMail MS SQL (для версии Standart и выше)
В своем приложение я сделал специальный раздел для автоматизации данной задачи

При нажатии появится форма для заполнения информации необходимой для создания профиля рассылки писем:

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

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

  1. Меняются системные параметры MS SQL.
  2. Создается DatabaseMail Profile
  3. Активируется в SQL Agente профиль
  4. Создается DatabaseMail Account
  5. Добавляется DatabaseMail Account к Database Mail Profile
  6. Создается DatabaseMail Operator
Более подробно описано в следующей и, частично, я брал отсюда . Естественно, данные действия можно выполнить с помощью SSMS .
2.Дополнительные уведомления для администратора
В программе предусмотрены 2 задачи, применяемые к БД:
  1. проверка целостности БД. Для проверки базы данных использовалась стандартная процедура DBCC CHECKDB .
  2. информирование о свободном месте в файловых группах.
  3. Вторая задача была реализована с помощью запроса к системной таблице dbo.sysfiles
  4. Вот пример данного запроса, который выполнялся к базе:
Select NAME = left(a.NAME,15), a.FILEID, = convert(decimal(12,2),round(a.size/128.000,2)), = convert(decimal(12,2),round(fileproperty(a.name,"SpaceUsed")/128.000,2)), = convert(decimal(12,2),round((a.size-fileproperty(a.name,"SpaceUsed"))/128.000,2)) , FILENAME = a.FILENAME From dbo.sysfiles a
Ответ с сервера приходит на почту администратора в виде html разметки. Данный синтаксис возможен благодаря следующей стандартной функции MS SQL FOR XML .

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

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

3.Решение проблем при настройке DatabaseMail
В MS SQL 2008 я столкнулся с проблемой при настройке SQL Server Agent. Симптомы следующие, после настройки невозможно запустить SQL Agent. В основном это решается с помощью установки update на SQL сервер.

Если данные обновления не помогают, необходимо скачать fix. Его можно найти на данном сайте конечную ссылку не могу указать сейчас, для того что бы дойти до пакета фикса, нужно будет ответить на ряд вопросов.
Если есть проблемы с модулем DatabaseMail. После настройки данного модуля с помощью приложения, необходимо зайти в SQL Agent и просмотреть журнал событий. Если там будут ошибки «невозможно подключиться к почтовому ящику». Значит есть проблема, даже если через проверку отправляется письмо.

Исправляется это следующими манипуляциями:

  1. Management Studio - SQL Server Agent - Properties.
  2. Alert System
  3. Уберите галочку с Enable mail profile
  4. Нажмите OК
  5. Зайдите снова и поставьте галочку
  6. Перезагрузите SQL Server Agent.
Проверьте учетную запись для SQL Agent service. Если это доменная учетная запись измените ее на системную или наоборот. Все должно заработать.
4.Настраиваем резервное копирование с помощью приложения для SQL Standart:
Выбираем версию Standart. Настраиваем уведомления. (см. раздел, настройки уведомления):

Соединяемся с БД, заполняя данные для соединения и указываем БД, для которой будет применяться Job:

Выбираем настройку резервного копирования:

Указываем пути для сохранения копий БД. Если указанные папки не существует, то программа попытается их создать (нужны соответствующие права).

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

5.Настраиваем резервное копирование с помощью приложения для SQL Express:
Так как в SQL Express отсутствует SQL Agent, задачу по автоматизации резервного копирования пришлось решить другим путем. В указанной пользователем папке создается bat файле в котором описан SQL запрос, отвечающий за создание резервной копии. В случае необходимости можно редактировать его напрямую. По мимо этого должен работать стандартный планировщик Windows, в нем создается задача, которая будет запускать раз в сутки в указанное время.

Для этого запускаем приложение. Выбираем пункт MS SQL Express:

Появляется форма для заполнения параметров:

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

Единственный минус данного подхода в том, что приходится храниться в открытом виде пароль для соединения с БД.

6.Удаление задач из БД.
Если необходимо удалить все задачи из БД (например, захотели изменить пути сохранения БД). Для этого используем соответствующий пункт в меню программы. Из SQL Agent будут удалены все задачи с определенным начальным префиксом (в моем случае King):

7.Удаление копий БД
В некоторых задачах, настроено удаление старых копий БД. Для этого я использую процедуру master.dbo.xp_delete_file. Пример использования: Удалит все файлы с расширением bak из указанной папки, дата создания которых превышает 14 дней.
EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N"bak",dateadd(d,-14,getdate()),0;
И вот еще один более подробный пример и информация о том, какие параметры принимает данная функция .

Как восстанавливать резервные копии

Из-за нехватки времени модуль восстановления еще не реализован, возможно в будущем я его добавлю, а пока просто кратко опишу как можно будет восстановить базу.

С помощью SQL скрипта. Для восстановления базы данных используется команда RESTORE .

Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:
RESTORE DATABASE [Имя базы данных] FROM DISK = "Z:\SQLServerBackups\back.bak" WITH REPLACE
В случае, если необходимо восстановить последовательно сначала полную копию, разностные копии и журналы транзакций, тогда необходимо написать следующий SQL скрипт.

RESTORE DATABASE TEST_DB –восстанавливаем полную копию FROM test_db_full WITH NORECOVERY; GO RESTORE DATABASE TEST_DB –восстанавливаем разностную копию FROM test_db_diff WITH FILE = 1, NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №1 FROM test_db_tran_1 WITH FILE = 1, WITH NORECOVERY; GO RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №2 FROM test_db_tran_2 WITH FILE = 1, WITH NORECOVERY; GO RESTORE DATABASE TEST_DB WITH RECOVERY; GO
Для восстановления БД можно использовать так же и SSMS .

Теги:

  • Backup MS SQL
  • резервное копирование MS SQL
Добавить метки