Рассмотрим, как организовать две наиболее часто встречающиеся задачи администрирования SQL Server’а:
- Автоматическое резервное копирование баз данных;
- Удаление старых резервные копии.
Планирование резервных копий базы данных
- Откройте SQL Management Studio и подключитесь к требуемой базе данных. Убедитесь, что SQL Server Agent работает;
- Разверните узел Management – Maintenance (для этого у Вас должна быть роль «SYSADMIN») – щелкните правой кнопкой и выберите «New Maintenance Plan»;
- Введите имя нового плана обслуживания;
- Щелкните по иконке календаря справа в единственной строке. В открывшемся окне сконфигурируйте время выполнения задания. Выберите такое время, когда база данных меньше загружена;
- Из раздела Toolbox перетащите задачу Backup Database Task в основную область;
- Дважды щелкните по Backup Database Task – откроется окно настроек задачи резервного копирования – задайте нужные настройки;
- Щелкните ОК – теперь резервные копии будут создаваться в соответствии с запланированным временем;
Удаление старых резервных копий
Так как файлы резервных копий будут создаваться часто, то в скором времени свободного места на жестком диске у Вас поубавится. Поэтому Вам нужно будет удалять устаревшие файлы резервных копий. Продолжим конфигурировать план обслуживания:
- Из панели Toolbox перетащите в основную область задачу Maintenance Cleanup Task;
- Дважды щелкните по Maintenance Cleanup Task, чтобы открыть окно свойств. В нем Вы должны определить расположение резервных копий, их расширение и определить возраст файлов подлежащих удалению. Хорошей практикой является хранение бэкапов до одного месяца;
- Жмите ОК и сохраняете план обслуживания;
- Дальше можете либо дождаться следующего времени выполнения плана обслуживания, либо выполнить его вручную (правой кнопкой мыши по плану обслуживания в Object Explorer).
Рекомендуется настроить регулярное резервное копирование базы данных (на случай аппаратных или программных сбоев), причем лучше всего с сохранением резервных копий за последние несколько дней, например семь (за последнюю неделю).
Для этого можно использовать либо встроенный в SQL Server планировщик заданий - «SQL Server Agent» (в бесплатную версию не входит), либо стандартный «Планировщик Windows» в сочетании с утилитой SQLCMD.EXE, которая позволяет выполнять запросы к SQL Server из командной строки. В планировщике необходимо создать как минимум семь заданий (по одному на каждый день недели), каждое из которых будет (раз в неделю) заменять один из семи файлов, содержащих соответствующую резервную копию базы данных.
Кроме того, файлы резервных копий рекомендуется хранить не только на жестком диске компьютера, где установлен SQL Server, но и дублировать их на ленту или жесткий диск другого компьютера в сети. Для этого можно использовать либо специальное ПО, которое позволяет делать резервные копии всего диска, либо с помощью того же планировщика копировать файлы на ленту или другой компьютер (вторым шагом).
С помощью «Планировщика Windows» (для бесплатной версии)
Чтобы создать задание в «Планировщике Windows» надо:
Запустить программу «Блокнот» (Пуск->Все программы->Стандартные->Блокнот) и ввести следующие две строки, после чего сохранить их в виде командного файла (*.BAT):
SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "D:\BACKUP\ AltaSVHDb_monday.bak" WITH INIT, NOFORMAT, SKIP, NOUNLOAD"
XCOPY D:\BACKUP\ AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y
где «(local)» - имя сервера (в случае установки именованного экземпляра SQL Server надо указать имя полностью: «ИМЯ_КОМПА\SQLEXPRESS»), «AltaSVHDb» - имя базы данных, «D:\BACKUP\ AltaSVHDb_monday.bak» - имя файла для создания в нем резервной копии (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться дополнительное копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ).
Запустить мастер планирования заданий (Панель управления->Назначенные задания->Добавить задание) и нажать кнопку «Далее»:
Нажать кнопку «Обзор» и указать путь к командному файлу (*.BAT), созданному на шаге a):
Указать имя для задания, выбрать вариант запуска «еженедельно» и нажать кнопку «Далее»:
Поставить галочку возле нужного дня недели, а в поле «Время начала» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью), затем нажать кнопку «Далее»:
Ввести имя пользователя и пароль (дважды) учетной записи ОС, от имени которой будет выполняться задание, и нажать кнопку «Далее»:
Внимание! Чтобы задание успешно выполнялось необходимо предоставить указанной здесь учетной записи (домена или локального компьютера) права записи в вышеупомянутую папку «\\BACKUP_SERVER\Folder» , а также настроить доступ к самому SQL Server.
Нажать кнопку «Готово»
Примечание . Чтобы проверить работоспособность созданного задания, необходимо в списке заданий (Панель управления->Назначенные задания) нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Выполнить», затем убедиться, что файл резервной копии БД успешно создался по тем путям, которые были указаны на шаге a).
С помощью «SQL Server Agent» (в бесплатную версию не входит)
Чтобы создать задание в «SQL Server Agent» надо:
Запустить утилиту SQL Server Management Studio и подключиться к серверу под учетной записью администратора.
В левой части окна нажать правой кнопкой мыши на разделе «Объекты сервера/Устройства резервного копирования» и в контекстном меню выбрать пункт «Создать устройство резервного копирования»:
В поле «Имя устройства» ввести имя, которое будет ассоциироваться с файлом резервной копии БД, при необходимости изменить путь в поле «Файл» и нажать «ОК»:
В левой части окна нажать правой кнопкой мыши на разделе «Агент SQL Server/Задания» и в контекстном меню выбрать пункт «Создать задание»:
В поле «Имя» ввести имя задания:
На странице «Шаги» нажать кнопку «Создать»:
В появившемся окне ввести имя в поле «Имя шага», проверить, что в поле «Тип» выбрано «Сценарий Transact-SQL (T-SQL)», а в поле «Команда» ввести строку:
BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD
где «AltaSVHDb» - имя базы данных, «AltaSVHDb_monday» - имя устройства резервного копирования, созданного на шаге c) (будет различаться по дням недели):
В предыдущем окне нажать кнопку «ОК», в результате на странице «Шаги» должна появиться строка:
Чтобы файл резервной копии БД сразу копировался на другой компьютер в сети необходимо повторить пункты f) - h), в окне «Создание шага задания» выбрав в поле «Тип» значение «Операционная система (CmdExec)», а в поле «Команда» указав строку:
XCOPY D:\MSSQL\BACKUP\AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y
где «D:\MSSQL\BACKUP\AltaSVHDb_monday.bak» - путь, указанный на шаге c) (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ):
Примечание . Чтобы копирование файла успешно выполнялось необходимо запускать «SQL Server Agent» под учетной записью домена Windows, для которой предоставлены права записи в вышеупомянутую папку (см. также «SQL2005_installation.doc» или «SQL2008_installation.doc»), а также настроен доступ к самому SQL Server (см. раздел «Настройка прав доступа к БД», включить эту учетную запись надо в роль «sysadmin» на странице «Серверные роли», а на страницах «Сопоставление пользователей» и «Защищаемые объекты» ничего не делать).
На странице «Расписания» нажать кнопку «Создать»:
Ввести имя в поле «Имя», проверить, что в поле «Тип расписания» выбрано значение «Повторяющееся задание», а в поле «Выполняется» - «Еженедельно». Поставить галочку возле нужного дня недели (остальные снять), а в поле «Однократное задание» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью):
В предыдущем окне нажать кнопку «ОК», в результате на странице «Расписания» должна появиться строка:
Нажать кнопку «ОК».
Примечание . Чтобы проверить работоспособность созданного задания, необходимо в разделе «Агент SQL Server/Задания» нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Запустить задание на шаге», в появившемся окне выбрать первый шаг данного задания и нажать «ОК». Далее появится окно отображающее ход выполнения задания. Если выполнение задания закончится с ошибкой, то подробное описание ошибки можно увидеть вызвав пункт «Просмотр журнала» того же контекстного меню.
Разностное резервное копирование основано на самой последней предыдущей полной резервной копии данных. В разностной резервной копии сохраняются только те изменения, которые были произведены с момента создания последней полной резервной копии.Рекомендации:
- Используйте разностные копии БД, если создание полной копии БД занимает большой промежуток времени
- Периодически делайте полную копию БД, чтобы уменьшить объемы создаваемых разностных копий.
- После создания полной копии БД, все предыдущие разностные копии теряют свою актуальность.
Приведу небольшой пример из практики, почему мы стали использовать разностную копию. Со временем у нашего клиента разрослась база данных до таких размеров, что создание полной резервной копии занимало 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 системные БД:
- msdb – потому что, там хранятся настроенные задачи и другие
- 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 раз в месяц |
Хранится всегда только последний экземпляр БД |
- Используйте опцию BACKUP WITH CHECKSUM
чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему. - Не выполняйте резервное копирование файлов на тот же физический диск, на котором хранится база данных или протокол транзакций.
- Если вы используете MS SQL 2008 или выше, рекомендую вам использовать сжатие резервных копий средствами SQL. Следующий код включит сжатие по умолчанию: USE master; GO EXEC sp_configure ‘backup compression default’, "1"; RECONFIGURE WITH OVERRIDE;
- держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой.
- Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах. DBCC CHECKDB ("Имя базы данных") WITH NO_INFOMSGS, ALL_ERRORMSGS; Примечание: на практики мы использовали данную проверку, только перед выполнением полной резервной копии.
- Выполняйте периодически обновление статистики и реорганизации индексов БД
Используем приложение
Несколько нюансов по приложению:- Все тексты и запросы в коде вынесены в ресурсы, мне так было проще
- При вводе параметров соединения и других настроек, они сохраняются в файл. Для Express и Standart используются разные файлы (dbStandart, udExpress) в них хранится класс UserData
- Для выполнения некоторых операций могут потребоваться права администратора
- На данный момент не работает соединение с БД под доменной учетной записью
- Программа не обладает суперкрасивым интерфейсом
1. Настройка уведомления администратора
Мне было лень каждый раз заходить на сервер и проверять, сработала ли задача или произошла какая-то ошибка. Да и хотелось иметь возможность получать другие уведомления, не только о выполнения задач.Для данной цели используется DatabaseMail MS SQL (для версии Standart и выше)
В своем приложение я сделал специальный раздел для автоматизации данной задачи
При нажатии появится форма для заполнения информации необходимой для создания профиля рассылки писем:
Приложение автоматически настроено на стандартный 25 SMTP порт для адреса с которого отправляются письма. При необходимости его можно изменить в процедуре sysmail_add_account_sp
Пользователь и пароль требуются на случай, если у почтового сервиса настроена аутентификация.
Имя оператора в системе указывается для того, чтобы у нас нормально создался профиль в DatabaseMail. Пишите любое название, которое будет для вас понятным. Ниже приведен пример заполненной формы.
- Меняются системные параметры MS SQL.
- Создается DatabaseMail Profile
- Активируется в SQL Agente профиль
- Создается DatabaseMail Account
- Добавляется DatabaseMail Account к Database Mail Profile
- Создается DatabaseMail Operator
2.Дополнительные уведомления для администратора
В программе предусмотрены 2 задачи, применяемые к БД:- проверка целостности БД. Для проверки базы данных использовалась стандартная процедура DBCC CHECKDB .
- информирование о свободном месте в файловых группах.
- Вторая задача была реализована с помощью запроса к системной таблице dbo.sysfiles
- Вот пример данного запроса, который выполнялся к базе:
Ответ с сервера приходит на почту администратора в виде html разметки. Данный синтаксис возможен благодаря следующей стандартной функции MS SQL FOR XML .
Так же пока я искал, как преобразовать в возвращаемый результат выполнения запросов в html текст, наткнулся на следующую страницу , где человек создал целую процедуру для этих целей
Настроить эти операции можно с помощью соответствующего пункта в меню программы:
Появиться окно для указания почтового ящика, на который необходимо высылать html текст отчета:
3.Решение проблем при настройке DatabaseMail
В MS SQL 2008 я столкнулся с проблемой при настройке SQL Server Agent. Симптомы следующие, после настройки невозможно запустить SQL Agent. В основном это решается с помощью установки update на SQL сервер.Если данные обновления не помогают, необходимо скачать fix. Его можно найти на данном сайте конечную ссылку не могу указать сейчас, для того что бы дойти до пакета фикса, нужно будет ответить на ряд вопросов.
Если есть проблемы с модулем DatabaseMail. После настройки данного модуля с помощью приложения, необходимо зайти в SQL Agent и просмотреть журнал событий. Если там будут ошибки «невозможно подключиться к почтовому ящику». Значит есть проблема, даже если через проверку отправляется письмо.
Исправляется это следующими манипуляциями:
- Management Studio - SQL Server Agent - Properties.
- Alert System
- Уберите галочку с Enable mail profile
- Нажмите OК
- Зайдите снова и поставьте галочку
- Перезагрузите SQL Server Agent.
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 .
Теги: Добавить метки
Несмотря на то, что в наших предыдущих материалах мы уже касались вопроса резервного копирования баз 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 часов.
Теги:
А также: бэкап SQL, бэкап 1С.
Серверная 1С содержит данные в базе данных, которая находится на SQL сервере. Сегодня мы рассматриваем вариант MS SQL 2005/2008.
Чтобы данные не были потеряны в случае сгоревшего диска сервера или других форс-мажорных ситуаций – необходимо с самого начала делать бэкапы (backup).
Делать ручками каждый день Backup SQL базы 1С конечно никто не хочет. Для этого есть автоматические средства. Познакомимся с ними.
Настройка Backup SQL
Настройка Backup SQL для базы 1С ничем не отличается от настройки бэкапа для любой другой базы данных.
Для настройки запустите MS SQL Management Studio. Эта программа находится в группе программ MS SQL.
Добавление задания бэкапа SQL базы 1С
Задания автоматического бэкапа баз SQL находятся в ветке Management / Maintenance plans.
Чтобы добавить новое задание бэкапа щелкните на группу Maintenance plans правой кнопкой мыши и выберите New Maintenance Plan.
Введите название задания. Название имеет значение только для Вас. На всякий случай лучше использовать английские символы.
Настройка задания бэкапа SQL базы 1С
Откроется редактор заданий. Обратите внимание – задания могут делать различные операции с базой данных, а не только бэкапы.
Список вариантов операций выведен слева внизу. Выберите Back Up Database Task двойной кнопкой мыши или просто перетащите вправо.
Обратите внимание на стрелочку. Вы можете перетащить несколько различных или одинаковых операций и связать их стрелочками. Тогда будет выполняться сразу несколько заданий в определенной Вами последовательности.
В окне настройки выберите нужные базы SQL 1С (можно сразу несколько или по одной).
Выберите место сохранения бэкапа базы SQL 1С. Необходимо выбрать физически другой винчестер. Организационно можно поставить галочку «Создать подпапки».
Теперь настроим расписание backup. Расписание backup по-умолчанию добавилось само. Но Вы можете добавить несколько расписаний (например, одно – ежедневное, одно – еженедельное и т.п.). Нажмите кнопку настройки расписания backup.
На скриншоте пример ежедневного Backup SQL базы 1С в 3 ночи.
Чтобы расписание backup в списке было красиво-понятным, его можно изменить.
Сохранение задания бэкапа SQL базы 1С
Нажмите записать. Задание появится слева в списке.
Это важно! Проверьте правильность создания задания Backup SQL базы. Для этого нажмите на задании правой кнопкой и выберите Execute.
В результате должен появится файл бэкапа по указанному пути. Если что-то не так – удалите задание (Del) и начните с начала.
Загрузка...