Полная модель восстановления

Восстановление базы данных через интерфейс среды SQL Server Management Studio

Во многих случаях самый простой способ восстановить базу данных – это воспользоваться интерфейсом SQL Server Management Studio. SQL Server Management Studio использует историю создания резервных копий, которая хранится в базе данных msdb, чтобы показать нам, какой способ восстановления базы данных является наилучшим..

Реализуем сценарий восстановления базы данных через интерфейс среды SQL Server Management Studio

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

ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
—Создаем полную резервную копию базы данных
BACKUP DATABASE AdventureWorks
TO DISK = "T:\BACKUPS\ADVFULL.BAK"
WITH INIT;
—Имитируем выполнение транзакции
UPDATE AdventureWorks.Person.Contact
SET EmailAddress = "kim@testbackup.com"
WHERE ContactID=3;
—Создаем разностную резервную копию
BACKUP DATABASE AdventureWorks
TO DISK = "T:\BACKUPS\ADVDIFF.BAK"
WITH INIT,Differential;

Чтобы восстановить базу данных, откройте Object Explorer (Обозреватель объектов), выбрав команду Object Explorer (Обозреватель объектов) в меню View (Вид) или нажав клавишу F8.

В дереве объектов разверните экземпляр SQL Server, откройте папку Databases (Базы данных) и щелкните правой кнопкой мыши на базе данных AdventurеWorks. В контекстном меню выберите команды Tasks, Restore, Database (Задачи, Восстановить, База данных).

В открывшемся диалоговом окне Restore Database (Восстановление базы данных) вы увидите, что самые последние наборы резервных копий уже выбраны для восстановления, как показано на рис. 5.1. Чтобы завершить восстановление, просто нажмите кнопку OK.Примечание. Убедитесь, что с базой данных AdventurеWorks не открыто ни одного соединения, поскольку в процессе восстановления соединения с базой данных не разрешены. (рис 5.1) Диалоговое окно Восстановление базы данных

На экране должно появиться сообщение, информирующее об успешном восстановлении базы данных.

Откройте окно New Query (Новый запрос) и проверьте успешность восстановления данных из обеих резервных копий, для чего введите и выполните следующий код:

USE AdventureWorks;
GO
SELECT EmailAddress
FROM Person.Contact
WHERE ContactID =3;

В панели результатов должно отобразиться kim@testbackup.com.

Восстановление базы данных при простой стратегии резервного копирования с использованием T-SQL

Представьте себе, что вы создали резервную копию, описанную в предыдущем примере. Чтобы восстановить базу данных при помощи T-SQL, используется инструкция RESTORE DATABASE. Синтаксис этой инструкции аналогичен инструкции BACKUP. Необходимо указать имя базы данных и путь к устройству резервного копирования.

Восстанавливаем данные из полной резервной копии при помощи инструкций T-SQL

USE MASTER
GO
RESTORE DATABASE AdventureWorks
FROM DISK='T:\BACKUPS\ADVFULL.BAK'

SELECT EmailAddress
FROM AdventureWorks.Person.Contact
WHERE ContactID = 3

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

Восстанавливаем данные из разностных резервных копий при помощи инструкций T-SQL

USE MASTER
GO
RESTORE DATABASE AdventureWorks
FROM DISK='T:\BACKUPS\ADVFULL.BAK'
WITH NORECOVERY

USE MASTER
GO
RESTORE DATABASE AdventureWorks
FROM DISK='T:\BACKUPS\ADVDIFF.BAK'

SELECT EmailAddress
FROM AdventureWorks.Person.Contact
WHERE ContactID = 3

Восстановление базы данных при полной стратегии резервного копирования с использованием T-SQL

В полной стратегии мы имеем комбинацию полной резервной копии и резервных копий журнала транзакций. Далее речь пойдет о восстановлении данных из этих резервных копий при помощи инструкций T-SQL.

Восстанавливаем данные из полной резервной копии и резервных копий журнала транзакций

ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
—Создаем полную резервную копию базы данных
BACKUP DATABASE AdventureWorks
TO DISK = "T:\BACKUPS\ADVFULL.BAK"
WITH INIT;
—Имитируем выполнение транзакции
UPDATE AdventureWorks.Person.Contact
SET EmailAddress = "AfterFull@test.com"
WHERE ContactID=3;
—Создаем резервную копию журнала транзакций
BACKUP LOG AdventureWorks
TO DISK = "T:\BACKUPS\ADVLOG1.BAK"
WITH INIT;
—Имитируем выполнение транзакции
UPDATE AdventureWorks.Person.Contact
SET EmailAddress = "AfterLog@test.com"
WHERE ContactID=3;

—Выполняем резервное копирование последних записей журнала транзакций
BACKUP LOG AdventureWorks
TO DISK = "T:\BACKUPS\ADVLOG2.BAK"
WITH INIT, NO_TRUNCATE;

— Переключаемся на базу данных master db
USE master
GO
— Восстанавливаем данные из полной резервной копии
RESTORE DATABASE AdventureWorks
FROM DISK='T:\BACKUPS\ADVFULL.BAK'
WITH REPLACE, NORECOVERY;
— Восстанавливаем данные из первой резервной копии журнала транзакций
RESTORE LOG AdventureWorks
FROM DISK = "T:\BACKUPS\ADVLOG1.BAK"
WITH NORECOVERY;
— Восстанавливаем данные из второй резервной копии журнала транзакций
RESTORE LOG AdventureWorks
FROM DISK = "T:\BACKUPS\ADVLOG2.BAK";

SELECT EmailAddress
FROM AdventureWorks.Person.Contact
WHERE ContactID = 3

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

Системные базы данных master, msdb, и model являются ядром установки SQL Server. Без системных баз данных SQL Server не будет функционировать должным образом или, при повреждении базы данных master, вообще не будет работать. Следовательно, крайне важно иметь резервные копии этих баз данных, чтобы быть готовым к возможным сбоям системы. Для системных баз данных обычно используется простая модель, при которой создаются полные резервные копии базы данных по регулярному расписанию. Такая стратегия в этом случае будет эффективной, поскольку информация в этих таблицах изменяется нечасто. Тем не менее, после существенных изменений в системе, например, создания баз данных, имен входа или изменения параметров конфигурации, следует выполнить внеплановое резервное копирование системных баз данных.

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

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

Восстанавливаем базу данных master

Создаем резервные копии баз данных master, msdb и model.

—База данных MASTER
BACKUP DATABASE MASTER
TO DISK = "T:\BACKUPS\master.bak"
WITH INIT
—База данных MSDB
BACKUP DATABASE MSDB
TO DISK = "T:\BACKUPS\msdb.bak"
WITH INIT
—База данных MODEL
BACKUP DATABASE MODEL
TO DISK = "T:\BACKUPS\model.bak"
WITH INIT

Закройте SQL Server Management Studio и все остальные программы, которые установили соединение с SQL Server.

В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager (Все программы, Microsoft SQL Server 2005, Средства настройки, Диспетчер конфигурации SQL Server).

В окне SQL Server Configuration Manager (Диспетчер конфигурации SQL Server) щелкните левой кнопкой мыши элемент SQL Server 2005 Services (Службы SQL Server 2005) в левой панели. При этом в правой панели отображаются все службы SQL Server. Остановите все службы, щелкнув правой кнопкой мыши на каждой из них и выбрав из контекстного меню команду Stop item (Остановить). Запомните, какие службы вы остановили; позже их надо будет запустить. После остановки всех служб окно программы будет выглядеть следующим образом:

Откройте командную строку; для этого в меню Start (Пуск) выберите команду Run (Выполнить), введите cmd и нажмите кнопку ОК.

Перейдите в папку binn в установочном каталоге SQL Server при помощи следующей команды: В этой команде указан путь, который используется программой установки SQL Server по умолчанию. Введите всю команду одной строкой (здесь перенос выполнен для того, чтобы команда уместилась в границы страницы), а затем нажмите клавишу Enter.

C:\Documents and Settings\Administrator>cd "\Program Files\ Microsoft SQL Server\MSSQL.1\MSSQL\binn"

Запустите SQL Server в однопользовательском администраторском режиме, указав в командной строке параметр -m, как показано в следующей команде. Введите эту команду и нажмите клавишу Enter.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr –m

Не закрывая это окно командной строки, запустите еще один экземпляр командной строки, повторив действия, описанные в пункте 5.

В этом новом окне командной строки введите sqlcmd -E, чтобы установить соединение с SQL Server.

Выполните восстановление базы данных master из командной строки sqlcmd при помощи следующей инструкции T-SQL. При вводе инструкции при переходе на новую строку нажимайте клавишу Enter. После того, как вы введете инструкцию GO и нажмете клавишу Enter, будет выполнена вся инструкция.

RESTORE DATABASE master
FROM DISK ='T:\BACKUPS\master.bak';
GO

Результат должен выглядеть следующим образом:

Перейдите в первое окно командной строки. SQL Server автоматически завершит работу. Теперь можно закрыть оба окна командной строки.

Переключитесь на SQL Server Configuration Manager (Диспетчер конфигурации SQL Server) и запустите только одну службу SQL Server -(MSSQLSERVER).

Восстанавливаем базы данных msdb и model

—База данных MSDB
RESTORE DATABASE MSDB
FROM DISK = "T:\BACKUPS\msdb.bak"
—База данных MODEL
RESTORE DATABASE MODEL
FROM DISK = "T:\BACKUPS\model.bak"
WITH REPLACE

Как составить расписание резервного копирования при помощи мастера планов обслуживания

До сих пор мы выполняли резервное копирование через Query Window (Окно запроса) в SQL Server Management Studio. Безусловно, в производственных системах необходимо создать расписание автоматического резервного копирования без участия пользователя. Для этого можно использовать любую программу автоматического обслуживания. В SQL Server есть встроенный планировщик, представляющий собой компонент службы SQL Server Agent (агент SQL Server). С помощью заданий SQL Server Agent (агента SQL Server) можно настроить пакеты T-SQL, которые будут выполняться по заданному расписанию. Этот компонент предоставляет функции, необходимые для автоматизации стратегии восстановления. Рассмотрим еще один способ составления расписания резервного копирования при помощи мастера. Мастер SQL Maintenance Plan Wizard (мастер планов обслуживания SQL) - это инструмент, которые помогает выполнять стандартные задачи вроде резервного копирования, дефрагментации индексов и проверки целостности базы данных. Мастер планов обслуживания зависит от службы SQL Server Agent (агент SQL Server) ввиду того, что его задания запускаются в нужный момент времени именно этой службой. Следовательно, служба SQL Server Agent (агент SQL Server) должна выполняться непрерывно. Службу SQL Agent Service (агент SQL Server) можно настроить через интерфейс SQL Server Configuration Management (Диспетчер конфигурации SQL Server). Настройте эту службу на автоматический запуск и запускайте при необходимости при помощи следующих действий.

Настраиваем автоматический запуск службы агент SQL Server

Используем мастер планов обслуживания

Планы обслуживания базы данных можно изменять через интерфейс SQL Server Management Studio. Для этого просто перейдите в Object Explorer (Обозревателе объектов) к папке Maintenance Plan (Планы обслуживания), которая вложена в папку Management (Управление), и щелкните правой кнопкой мыши на плане, который нужно изменить. Из этого меню можно открыть также журнал.

Заключение

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

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

Краткий справочник по 4-5 лекциям

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;

SELECT DATABASEPROPERTYEX 
("AdventureWorks",'Recovery')

USE msdb
GO
SELECT backup_start_date, type,
physical_device_name, backup_set_id
FROM backupset bs
inner join backupmediafamily bm
ON bs.media_set_id = bm.media_set_id
WHERE database_name ='AdventureWorks'
ORDER BY backup_start_date desc

SELECT filegroup_name,logical_name,
physical_name
FROM msdb..backupfile
WHERE backup_set_id = <backup id>

RESTORE HEADERONLY 
  FROM DISK= "T:\BACKUPS\ADVFULL.BAK" 
RESTORE FILELISTONLY 
  FROM DISK= "T:\BACKUPS\ADVFULL.BAK"