Lab6_SQL (Лабораторная №6)

Задание 6

Администрирование SQL сервера.

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

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

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

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

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

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

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

2. Создание учетных записей для пользователей.

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

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

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

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

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

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

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

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

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

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

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

1. Begin Tran1

2. Update Tran1

3. Begin Tran2

4. Update Tran2

5. Commit Tran1

6. Checkpoint

7. Update Tran2

8. Commit Tran2

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь у вас есть полная резервная копия базы данных 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. Созданная нами полная резервная копия приведена в списке доступных

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

Дифференцированное резервное копирование

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

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

Запуск процесса дифференцированного резервного копирования практически не отличается от полного. Мы выполним дифференцированное резервное копирование базы данных Sales на постоянном устройстве резервного копирования, созданном нами ранее.

1. Откройте SQL Server Management Studio и разверните элемент Databases своего сервера баз данных.

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

3. В диалоговом окне Back Up убедитесь, что для создания резервной копии выбрана база данных Sales, после чего в списке Backup Type выберите значение Differential.

4. В поле Name оставьте имя по умолчанию, а в поле Description введите описание Differential Backup of Sales.

5. В разделе Destination убедитесь, что выбрано устройство SalesFull (рис. 7).

6. На странице Options установите переключатель в положение Append To Тhe Existing Backup Set, чтобы случайно не записать новую информацию по­ верх уже существующей полной резервной копии (рис. 8).

Рис.7. Установка дифференцированного резервного копирования

■

Рис. 8. Параметры дифференцированного резервного копирования

1. Установите флажок Verify Backup When Finished.

2. Чтобы приступить к резервному копированию, щелкните на кнопке ОК.

Теперь удостоверимся в том, что и дифференцированная, и полная резервные ко­пии находятся на устройстве SalesFull.

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

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

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

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

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

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

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