SQL сервер. Резервное копирование и восстановление БД

Задание 6

С помощью утилиты SQL Server Management Studio для БД Sales выполните:

· полное резервное копирование;

· дифференцированное резервное копирование;

· резервное копирование журнала транзакций;

· стандартное восстановление;

· восстановление базы данных в состояние определенного момента времени.

Описание работы

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

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

Для упрощения и ускорения процесса восстановления работоспособности SQL Servera наряду с пользовательскими БД должны копироваться и системные БД:

¨ БД Masterхранит информацию обо всех БД на сервере. Наличие копии Masterзначительно упрощает процесс восстановления после сбоев. Изменяется после создания любого пользовательского объекта.

¨ БД Msdb хранит информацию о заданиях, предупреждениях и операторах БД, используемую службой SQL Server Agent. Если у Вас нет копии Msdb, при сбое системы придется перестроить все системные БД, заново создать все задания, предупреждения и операторов.

¨ БД Model позволяет создать стандартную конфигурацию для всех новых БД. Так как при перестроении Master или Msdb изменения, внесенные в Model, теряются, то при наличии страховочной копии Вы восстановите Ваш доработанный вариант Model.

Принцип процесса восстановления данных

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

Server создает в журнале транзакций контрольные точки при копировании зафиксированных транзакций из журнала транзакций в базу дан­ных (копирование результатов транзакций выполняется автоматически, примерно через 5 минут).

Каждая строка журнала транзакций имеет порядковый номер регистрации (LSN). Журнал транзакций выглядит примерно так:

147 Begin Tran1

148 Update Tran1

149 Begin Tran2

150 Update Tran2

151 Commit Tran1

152 Checkpoint

153 Update Tran2

154 Commit Tran2

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

1. SQL Server сохраняет запись LSN самой последней открытой транзакции (в данном случае запись 149 Begin Tran 2, поскольку она еще не была подтверждена на момент установки последней контрольной точки).

2. SQL Server архивирует все страницы базы данных, которые содержат информацию.

3. SQL Server извлекает все записи журнала транзакций, которые были созданы во время процесса архивирования, т.е. все строки журнала транзакций со значением LSN больше записанного в начале сессии резервирования данных (в нашем случае 149 и более). Благодаря такому подходу пользователи могут выполнять различные операции с базой данных во время ее восстановления.

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

Встроенных устройств не существует, их нужно создавать вручную, в качестве устройства можно задать файл.

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

1. Откройте утилиту SQL Server Management Studio. Разверните элемент сервера и группу Server Objectes.

2. На панели Object Explorer щелкните правой кнопкой мыши на элементе Backup Devices и выберите в контекстном меню пункт New Backup Device.

3. В текстовом поле Device Name диалогового окна Backup Device введите SalesFull.Обратите внимание на то, что имя файла и путь заполняются автоматически, так что убедитесь, что на выбранном устройстве достаточно места (рис. 1).

4. Щелкните на кнопке ОК, и устройство будет создано.

Выполнение полного резервного копирования

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

Рис. 1. Выбор места размещения резервной копии

Для создания базового уровня (так называется полное резервное копирование в лю­бой стратегии резервирования) мы скопируем базу данных на постоянное устройство резервного копирования, созданное ранее.

1. Откройте утилиту SQL Server Management Studio и найдите элемент Databases.

2. Щелкните правой кнопкой мыши на базе данных Sales и выберите в контекстном меню пункт Properties, а в открывшемся окне страницу Options.

3. В раскрывающемся списке Recovery Model выберите элемент Full, чтобы можно было позже выполнить резервное копирование журнала транзакций и посмотрите размер копируемых файлов на странице Files (рис. 2).

Рис..2. Выбор модели восстановления при резервном копировании

4. Для применения изменений щелкните на кнопке ОК.

5. Щелкните правой кнопкой мыши на имени базы данных Sales и выберите в контекстном меню пункт Tasks=> Back Up.

6. Убедитесь, что в диалоговом окне Backup выбрана база данных Sales и полный тип резервирования.

7. В поле Name оставьте имя, предложенное программой по умолчанию, а в поле Description введите Full Backup of Sales.

8. В разделе Destination уже может быть отображено некоторое дисковое устройство. В таком случае выберите это устройство и щелкните на кнопке Remove.

9. Щелкните на кнопке Add.

10. В диалоговом окне Select Backup Destination установите переключатель в

положение Backup Device, выберите в списке элемент SalesFull и щелкните на кнопке ОК .

11.Теперь ваша резервная копия должна быть упомянута в разделе Destination (рис. 4). Переключитесь на страницу Options.

Рис.4. Устройство резервного копирования показано в блоке Destination

12. На странице Options установите переключатель в положение Overwrite All Existing Backup Sets. Этот режим инициализирует новое устройство или перезаписывает его содержимое.

13. Чтобы проверить соответствие резервной копии реальной базе данных, установите флажок Verify Backup When Finished (рис. 5).

14. Для выполнения резервного копирования щелкните на кнопке ОК.

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

1. Откройте SQL Server Management Studio и на панели Object Explorer в группе Server Objects раскройте элемент Backup Devices.

2. Щелкните правой кнопкой мыши на устройстве SalesFull и выберите в контекстном меню пункт Properties.

3. На странице Media Contents вы должны увидеть полную резервную копию базы данных Sales (рис. 6).

4. Чтобы вернуться в окно SQL Server Management Studio, щелкните на кнопке ОК.

Рис.5. Страница параметров полного резервного копирования

Рис.6. Созданная нами полная резервная копия приведена в списке доступных

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