Резервное копирование и восстановление MS SQL 2012

Доброго времени суток, коллеги.

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

Статья будет состоять из следующих пунктов:

1. Резервное копирование MS SQL 2012

1.1.Полное резервное копирование базы данных (Full Backup).

1.2. Разностное резервное копирование (Differential backup)

1.3. Резервное копирование журнала транзакций (Transaction Log Backup)

1.4. Резервное копирование файлов и файловых групп (Backup Filegroups, Backup Files)

1.5. Резервное копирование системных баз данных.

2. Восстановление MS SQL 2012

2.1. Восстановление базы из полной резервной копии (Restore Full Database)

2.2. Восстановление базы данных из разностной резервной копии (Restore Differential Backup)

2.3. Восстановление журнала транзакций (Restore Log Transaction)

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

2.5. Восстановление базы данных до определённого момента

2.6. Восстановление файлов и файловых групп (Restore Files, Restore Filegroups)

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

2.7.1. Восстановление системных баз, в случае доступности службы SQL.

2.7.2. Восстановление системных баз, в случае недоступности службы SQL .

1. Резервное копирование баз данных MS SQL 2012.

Договоримся, что все прочие параметры (например, compression, reliability и т.п.) будем использовать по умолчанию. Также договоримся, что будем использовать полную модель восстановления (Recovery model – Full). Резервные копии будут храниться на сетевой папке «\\vniz-tst-bkp01.test.local\Backup_SQL\»

База будет состоять из двух файловых групп «Secondary и Primary» по два файла в каждой: Test_Recovery1 и Test_Recovery2, Test_Recovery_s1 и Test_Recovery_s2 соответственно.

Создадим две таблицы «Table_primary» и «Table_secondary» для каждой файловой группы и заполним их произвольными данными:

USE Test_Recovery

 CREATE TABLE Table_primary (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

on 'PRIMARY'

INSERT INTO Table_primary (number,test,nickname,name,ter)

VALUES

  (1000,'Test1', 'Munserat', 'Alex',42),

  (1001,'Test2','Yulkau', 'Yulkau',40),

  (1002,'Test3','Djecka','Eugene',13),

  (1003,'Test4','Fritzfak','Eugene',45),

  (1004,'Test5','Monax','Egor',11);

--COMMIT;

 USE Test_Recovery

 CREATE TABLE Table_secondary (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

on 'SECONDARY'

INSERT INTO Table_secondary (number,test,nickname,name,ter)

VALUES

  (1100, 'SEC1', 'Moscow', 'Table',14),

  (1101, 'SEC2', 'Saransk', 'Only',88),

  (1102, 'SEC3', 'Torbeevo', 'You',20),

  (1103, 'SEC4', 'Atyashevo', 'Dear',04),

  (1104, 'SEC5', 'Chamzinka', 'Colleagues',45);

--COMMIT;

1.1. Полное резервное копирование базы данных (Full Backup).

Полная резервная копия содержит файлы данных и часть журнала транзакций и представляет собой базу данных на момент создания резервной копии и служит основным источником данных при восстановлении. В момент полного резервного копирования сервером MS SQL выполняются следующие действия:

• Резервное копирование всех данных в базе.
• Резервное копирование всех изменений, который возникают во время выполнения резервного копирования
• Резервное копирование всех транзакций, не зафиксированных в журнале транзакций.

Приступим к полному резервному копированию используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место «\\vniz-tst-bkp01.test.local\Backup_SQL\Full_Test_Recovery.bak» кнопкой «Add»

В пункте «Backup type» выбираем «Full». Нажимаем «Ок».

Резервное копирование успешно выполнится.

Рассмотрим полное резервное копирование используя Transact-SQL (T-SQL)

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full_Test_Recovery.bak'

GO

Резервное копирование успешно выполнится. 

1.2. Разностное резервное копирование (Differential backup)

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

Данную копию невозможно восстановить на базу без ее полной резервной копии. С каждым запуском разностного резервного копирования, размер резервной копии будет расти, ибо количество транзакций с момента полного резервного копирования будет увеличиваться во времени.

В момент полного резервного копирования сервером MS SQL выполняются следующие действия:

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

Создадим таблицу «Table_Diff»:

USE Test_Recovery

 CREATE TABLE Table_Diff (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

INSERT INTO Table_Diff (number,test,nickname,name,ter)

VALUES

  (2100, 'Diff1', 'Lok', 'Table',47),

  (2101, 'Diff2', 'Tok', 'Only',78),

  (2102, 'Diff3', 'Kot', 'You',24),

  (2103, 'Diff4', 'Kol', 'Dear',22),

  (2104, 'Diff5', 'Don', 'Colleagues',46);

--COMMIT;

Приступим к разностному резервному копированию используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место «\\vniz-tst-bkp01.test.local\Backup_SQL\Diff1_Test_Recovery.bak» кнопкой «Add»

В пункте «Backup type» выбираем «Differential». Нажимаем «Ок».

Резервное копирование успешно выполнится.

Рассмотрим разностное резервное копирование используя Transact-SQL (T-SQL).

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Diff1_Test_Recovery.bak'

WITH DIFFERENTIAL;

GO

Резервное копирование успешно выполнится.

Проверим утверждение о росте разностной резервной копии при увеличении транзакций без использования полного резервного копирования.

Создадим еще одну таблицу:

CREATE TABLE Table_Table_Diff2 (

    Table_Table_Diff2ID INTEGER NOT NULL IDENTITY(1, 1),

    Test VARCHAR(MAX) NULL,

    name VARCHAR(255) NULL,

    email VARCHAR(255) NULL,

    name2 VARCHAR(255) NULL,

    name3 VARCHAR(255) NULL,

    name4 VARCHAR(255) NULL,

    name5 VARCHAR(255) NULL,

    name6 VARCHAR(255) NULL,

    name7 VARCHAR(255) NULL,

    name8 VARCHAR(255) NULL,

    name9 VARCHAR(255) NULL,

    name10 VARCHAR(255) NULL,

    PRIMARY KEY (Table_Table_Diff2ID)

);

GO

Заполним ее случайными данными, я воспользовался генератором случайных данных для таблиц MS SQL тут.

Делаем разностное резервное копирование:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Diff2_Test_Recovery.bak'

WITH DIFFERENTIAL;

GO

Видим, что файл резервной копии увеличился.

Теперь делаем полное резервное копирование, после чего - разностное:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak'

GO

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Diff3_Test_Recovery.bak'

WITH DIFFERENTIAL;

GO

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

Из этого примера видно, что не следует злоупотреблять разностным резервным копированием, без полного резервного копирования. Ибо в какой-то момент времени разностная резервная копия станет больше полной резервной копии базы и все преимущество разностного резервного копирования, такие как быстрое восстановление и меньшее место на диске, пропадут.

1.3. Резервное копирование журнала транзакций (Transaction Log Backup)

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

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

В момент полного резервного копирования сервером MS SQL выполняются следующие действия:

• Создается резервная копия журнала транзакций от прошедшего последнего успешно выполненного резервного копирования лога до конца текущего.
• Очищаются части журнала транзакций до начала активной части и отбрасываются сведения в неактивной части.

Приступим к резервному копированию лога транзакций используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место «\\vniz-tst-bkp01.test.local\Backup_SQL\Log_Test_Recovery.bak» кнопкой «Add»

В пункте «Backup type» выбираем «Transaction Log». Нажимаем «Ок».

Резервное копирование успешно выполнится.

Рассмотрим резервное копирование лога транзакций используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

USE Test_Recovery

GO

BACKUP LOG Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log_Test_Recovery.bak'

GO

Создадим таблицу и сделаем еще одну копию журнала транзакций:

CREATE TABLE Table_Log2 (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

INSERT INTO Table_Log2 (number,test,nickname,name,ter)

VALUES

  (1488, 'Log1', 'Lok2', 'Table',55),

  (1489, 'Log2', 'Tok2', 'Only',56),

  (1490, 'Log3', 'Kot2', 'You',57),

  (1491, 'Log4', 'Kol2', 'Dear',58),

  (1492, 'Log5', 'Don2', 'Colleagues',59);

--COMMIT;

USE Test_Recovery

GO

BACKUP LOG Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log2_Test_Recovery.bak'

GO

Создадим еще одну таблицу и повторно сделаем резервное копирование журнала транзакций:

CREATE TABLE Table_Log3 (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

INSERT INTO Table_Log3 (number,test,nickname,name,ter)

VALUES

  (1388, 'Log10', 'Lok20', 'Table',355),

  (1389, 'Log20', 'Tok20', 'Only',356),

  (1390, 'Log30', 'Kot20', 'You',357),

  (1391, 'Log40', 'Kol20', 'Dear',358),

  (1392, 'Log50', 'Don20', 'Colleagues',359);

--COMMIT;

USE Test_Recovery

GO

BACKUP LOG Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log3_Test_Recovery.bak'

GO

В итоге получаем три файла резервной копии журнала транзакции: Log_Test_Recovery.bak, Log2_Test_Recovery.bak, Log3_Test_Recovery.bak. Они нам пригодятся в будущем.

Описывая резервное копирование лога транкзаций нельзя не упомянуть о резервных копиях заключительного фрагмента журнала (Tail-Log Backups)

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

Более подробнее описано тут. 

1.4. Резервное копирование файлов и файловых групп (Backup Filegroups, Backup Files)

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

Т.к. Файлы журналов не могут входить в состав файловых групп. Управление пространством журнала отделено от управления пространством данных, то возможно лишь полное и разностное резервное копирование файлов и файловых групп. Здесь будет рассмотрено полное резервное копирование файловых групп и файлов в файловой группе. Разностное резервное копирование файлов и файловых групп аналогично разностному резервному копированию базы данных.

Приступим к резервному копированию файловой группы «PRIMARY» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Backup component» выбираем «Files and Filegroup». В появившемся окне выбираем
файловую группу «PRIMARY»

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место «\\vniz-tst-bkp01.test.local\Backup_SQL\Prim_Test_Recovery.bak» кнопкой «Add». Нажимаем «Ок»

Резервное копирование успешно выполнится.

Приступим к резервному копированию файла «Test_Recovery_s2» в файловой группе «SECONDARY» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Backup component» выбираем «Files and Filegroups». В появившемся окне выбираем нужный нам файл «Test_Recovery_s2»

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место «\\vniz-tst-bkp01.test.local\Backup_SQL\Sec_s2_Test_Recovery.bak» кнопкой «Add». Нажимаем «Ок»

Приступим к резервному копированию файла и файловой группы, используя T-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

   FILEGROUP = 'PRIMARY'

   TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Prim_Test_Recovery.bak'

GO

Для файла:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

FILE = 'Test_Recovery_s2'

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Sec_s2_Test_Recovery.bak';

GO

1.5. Резервное копирование системных баз данных. (Backup System Databases)

Выполнять резервное копирование системных баз (master, msdb, model) MS SQL следует постоянно, особенно перед и после каждого их изменения.

Резервное копирование базы master. В этой системной базе содержатся все сведения обо всех базах данных в MS SQL. Следует проводить процедуру резервного копирования данной базы всякий раз, когда создаются, удаляются и изменяются пользовательские базы данных.

Резервное копирование базы msdb. В этой базе содержатся сведения о заданиях и операторах, которые использует агент сервера MS SQL. Следует проводить процедуру резервного копирования при добавлении задания агенту сервера MS SQL.

Резервное копирование базы model. Данная база используется в качестве шаблона для всех баз данных, созданных для экземпляра SQL Server. Следует проводить процедуру резервного копирования, при изменении настройки самой базы model.

Резервное копирование базы Resource. Resource - доступная только для чтения база данных, которая содержит все системные объекты, включенные в SQL Server. База данных Resource находится в файле mssqlsystemresource.mdf, в котором содержится только код. Поэтому SQL Server не может создать резервную копию базы данных Resource. Исходя из того, что файл mssqlsystemresource.mdf является простым двоичным файлом (EXE), а не файлом базы данных, для создания его резервной копии можно выполнить простое резервное копирование файла или диска.

Резервное копирование базы Tempdb производить не следует, ибо она уничтожается и создается при каждом рестарте экземпляра MS SQL.

Для данных баз данных рекомендуется полное резервное копирование, методы описаны выше.

BACKUP DATABASE master

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Master_1.bak'

GO

BACKUP DATABASE model

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Model_1.bak'

GO

BACKUP DATABASE msdb

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Msdb_1.bak'

GO

Сделаем еще один раз полное резервное копирование базы данных:

USE Test_Recovery

GO

BACKUP DATABASE Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full3_Test_Recovery.bak'

GO

USE Test_Recovery

GO

На рисунке ниже изображена схема резервного копирования на моменты времени t0-t13.

Таблица1. Соответствие таблиц базы данных на определённый момент времени:

Временной   интервал	Имя резервной копии	Таблицы в базе данных
t0	-	-
t1	Full_Test_Recovery	Table_primary, Table_secondary
t2	Diff_Test_Recovery	Table_primary, Table_secondary Table_Diff
t3	Diff2_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2
t4	Full2_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2
t5	Diff3_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2
t6	Log_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2
t7	Log2_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2
t8	Log3_Test_Recovery	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3
t9	Prim_Test_Recovery.bak	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3
t10	Sec_s2_Test_Recovery.bak	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3
t11	Full3_Test_Recovery.bak	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3
t12	-	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3 Table_Tail1
t13	TailLog1_Test_Recovery.bak	Table_primary, Table_secondary Table_Diff Table_Diff2 Table_Table_Diff2 Table_Log2 Table_Log3

2. Восстановление баз данных MS SQL 2012.

Восстановление базы из резервной копии — это процесс копирования данных из резервной копии и применения журналов транзакций для наката до целевой точки восстановления.

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

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

Более подробнее тут.

Также следует упомянуть параметры RECOVERY и NORECOVERY, которые будут использоваться ниже:

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

• Режим WITH RECOVERY включает и стадию повтора, и стадию отката и восстанавливает базу данных. Более поздние резервные копии восстановить невозможно. Это значение по умолчанию.Если набор данных наката не был восстановлен в достаточной степени, чтобы обеспечить согласованность с базой данных, стадия отката выполнена быть не может. Компонент Database Engine выдает ошибку и прекращает восстановление.Если весь набор данных наката согласован с базой данных, то выполняется восстановление, после чего базу данных можно перевести в режим в сети.
• Предложение WITH NORECOVERY позволяет пропустить стадию отката, чтобы сохранить незафиксированные транзакции. Пропуск стадии отката позволяет восстановить другие резервные копии, чтобы выполнить накат базы данных на более поздний момент времени. Иногда инструкция RESTORE WITH NORECOVERY выполняет накат данных до момента, пока они не будут согласованы с базой данных. В таких случаях компонент Database Engine выдает информационное сообщение, указывающее, что набор данных наката теперь можно восстановить при помощи параметра RECOVERY.Другими словами, параметр NORECOVERY нужно использовать, когда для восстановления базы используются несколько восстанавливаемых резервных копий, за исключением последней восстанавливаемой резервной копии. После применения параметра NORECOVERY, база данных переходит в состояние восстановления.

Более подробнее тут.

2.1.  Восстановление базы из полной резервной копии (Restore Full Database).

Восстановим базу «Test_Recovery» в состояние «t4».

Приступим к восстановление базы данных из полной резервной копии «Full2_Test_Recovery.bak» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Database».

В появившемся окне «Restore Database» в разделе «Sourse» выбираем «Device». Далее «Add», прописываем путь «\\vniz-tst-bkp01.test.local\Backup_SQL\ Full2_Test_Recovery.bak», нажимаем «Ok». В разделе «Destination» выбираем Database «Test Recovery»

Нажимаем «Ok»

База успешно восстановится.

Рассмотрим восстановление базы данных используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE master

GO

RESTORE DATABASE Test_Recovery

FROM DISK='\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak'

WITH REPLACE

База успешно восстановится.

В этом примере мы использовали параметр «REPLACE»:

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

Параметр REPLACE отменяет несколько важных проверок, обычно выполняемых операцией восстановления. Отменяются следующие проверки.

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

Подробнее тут.

Проверим таблицы в базе данных на момент времени «t4»

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Видим, что база данных содержит таблицы «Table_primary, Table_secondary, Table_Diff, Table_Table_Diff2», что соответствует Таблице1.

2.2. Восстановление базы данных из разностной резервной копии (Restore Differential Backup)

Восстановим базу в состояние «t3». Для этого сначала следует восстановить базу данных из полной резервной копии, а потом накатить на базу разностную резервную копию.

Приступим к восстановление базы данных используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Database».

В появившемся окне «Restore Database» в разделе «Sourse» выбираем «Device». Далее «Add», прописываем путь «\\vniz-tst-bkp01.test.local\Backup_SQL\». Выбираем два файла Full_Test_Recovery.bak» и «Diff2_Test_Recovery.bak», нажимаем «Ok».

 В разделе «Destination» выбираем Database «Test Recovery». Нажимаем «Ok»

База успешно восстановится.

Рассмотрим восстановление базы данных используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE master

RESTORE DATABASE Test_Recovery

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full_Test_Recovery.bak'

 WITH  FILE = 1,  NORECOVERY, REPLACE

RESTORE DATABASE Test_Recovery

 FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Diff2_Test_Recovery.bak'

 WITH  FILE = 4, RECOVERY

 GO

База успешно восстановится.

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

Узнать величину FILE можно следующим оператором HEADERONLY:

RESTORE HEADERONLY

FROM DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Diff2_Test_Recovery.bak'

WITH NOUNLOAD;

GO

RESTORE HEADERONLY

FROM DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full_Test_Recovery.bak'

WITH NOUNLOAD;

GO

Проверим таблицы в базе данных на момент времени «t3»

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Видим, что база данных содержит таблицы «Table_primary, Table_secondary, Table_Diff, Table_Table_Diff2», что соответствует Таблице1.

2.3. Восстановление журнала транзакций (Restore Log Lransaction)

Восстановим базу в состояние «t8». Для этого сначала следует восстановить базу данных из полной резервной копии Full2_Test_Recovery.bak, а потом накатить на базу последовательно все резервные копии журнала транзакций - Log_Test_Recovery, Log2_Test_Recovery.bak, Log3_Test_Recovery.bak.

Приступим к восстановление базы данных используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Database».

В появившемся окне «Restore Database» в разделе «Sourse» выбираем «Device». Далее «Add», прописываем путь «\\vniz-tst-bkp01.test.local\Backup_SQL\». Выбираем четыре файла Full_Test_Recovery.bak» и «Log_Test_Recovery, Log2_Test_Recovery, Log3_Test_Recovery», нажимаем «Ok».

В разделе «Destination» выбираем Database «Test Recovery». Нажимаем «Ok»

База успешно восстановится.

Рассмотрим восстановление базы данных используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

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

USE master

RESTORE DATABASE Test_Recovery

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak'

WITH  FILE = 3,  NORECOVERY, REPLACE

RESTORE LOG Test_Recovery

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log_Test_Recovery.bak'

WITH  FILE = 3,  NORECOVERY

RESTORE LOG Test_Recovery

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log2_Test_Recovery.bak'

WITH  FILE = 2,  NORECOVERY

RESTORE LOG Test_Recovery

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Log3_Test_Recovery.bak'

WITH  FILE = 3,  RECOVERY

GO

База успешно восстановится.

Проверим таблицы в базе на момент времени «t8»

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Видим, что база данных содержит таблицы «Table_primary, Table_secondary, Table_Diff, Table_Diff2, Table_Log2, Table_Log3», что соответствует Таблице1.

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

База данных находится в состоянии «t8». Сымитируем проблему, при которой вышел из строя дисковый массив с журналом транзакций.

Для этого остановим экземпляр SQL сервера. Щелкаем правой кнопкой по сервису SQL «vniz-tst-sql01» и нажимаем «Stop».

Далее, заходим в каталог с базой данных и удаляем файл «Test_Recovery_log». Включаем службу и видим, что база перешла в состояние «SUSPECT», подробнее про состояние баз данных можно прочитать тут:

SELECT DATABASEPROPERTYEX('Test_Recovery', 'Status');

Приступим к восстановлению базы в рабочее состояние.

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

Выполняем запрос:

USE master

GO

sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

 Переводим базу в однопользовательский режим и в состояние EMERGENCY:

ALTER DATABASE Test_Recovery SET EMERGENCY, SINGLE_USER

GO

SELECT DATABASEPROPERTYEX('Test_Recovery', 'Status');

Выполняем восстановление данных, подробнее тут:

DBCC CHECKDB('Test_Recovery', REPAIR_ALLOW_DATA_LOSS)

GO

После того как сервер закончит выполнять запрос и вернет результат, меняем REPAIR_ALLOW_DATA_LOSS на REPAIR_REBUILD и выполняем запрос еще раз, чтобы убрать оставшиеся ошибки в базе данных.

DBCC CHECKDB('Test_Recovery', REPAIR_REBUILD)

GO

База перешла в состояние «ONLINE» и уже доступна для работы, но только в однопользовательском режиме, возвращаем базу в многопользовательский режим:

ALTER DATABASE Test_Recovery SET ONLINE, MULTI_USER

GO

База готова к работе.

Возвращаем значения в исходное состояние:

USE master

GO

sp_configure 'allow updates', 0

GO

2.5. Восстановление базы данных до определённого момента

Договоримся, что база будет находиться в точке «t11».

Создадим таблицу «Table_Tail1» 25.12.2012 в 15:57:

CREATE TABLE Table_Tail1 (

  number INTEGER,

  test TEXT,

  nickname TEXT,

  name TEXT,

  ter INTEGER,

  )

INSERT INTO Table_Tail1 (number,test,nickname,name,ter)

VALUES

  (1358, 'Tail10', 'Lok20', 'Table',355),

  (1359, 'Tail20', 'Tok20', 'Only',356),

  (1350, 'Tail30', 'Kot20', 'You',357),

  (1351, 'Tail40', 'Kol20', 'Dear',358),

  (1352, 'Tail50', 'Don20', 'Colleagues',359);

--COMMIT;

База перешла в состояние точки «t12».

Удалим таблицу 25.12.2012 в 16:03:

Use Test_Recovery

GO

DROP TABLE Table_Tail1

Проверяем:

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Теперь база находится в точке «t13». Как видно из рисунка, в процессе создания и удаления таблицы мы ни разу не создавали резервную копию данных.

Допустим нам нужно вернуться в момент времени «t12» - момент создания базы данных «Table_Tail1».

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

Приступим к созданию резервной копии заключительного фрагмента журнала транзакций (Tail-Log Backups) используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Backup»

Откроется окно «Back Up Database – Test_Recovery».

В разделе «Destination» удаляем путь резервного копирования кнопкой «Remove» и добавляем новое место « \\vniz-tst-bkp01.test.local\Backup_SQL\TailLog1_Test_Recovery.bak» кнопкой «Add»

В пункте «Backup type» выбираем «Transaction Log». Нажимаем «Ок».

Резервное копирование успешно выполнится.

Рассмотрим резервное копирование лога транзакций используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»,

USE Test_Recovery

GO

BACKUP LOG Test_Recovery

TO DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\TailLog1_Test_Recovery.bak'

GO

Восстановим базу на момент «t12» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Database».

В появившемся окне «Restore Database» в разделе «Sourse» выбираем «Device». Далее «Add», прописываем путь «\\vniz-tst-bkp01.test.local\Backup_SQL\». Выбираем два файла Full3_Test_Recovery.bak» и «TailLog1_Test_Recovery.bak», нажимаем «Ok».

В разделе «Destination» выбираем Database «Test Recovery». Нажимаем на кнопку «Timeline»

В появившемся окне «Backup Timeline: Test_Recovery» выбираем «Specific date and time», в соответствующих полях вводим «25.12.2012 15:58:12», нажимаем «Ок».

Нажимаем «Ок»

База данных успешно восстановится.

Проверяем:

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Видим, что база «Table_Tail1» успешно восстановилась.

Восстановим базу на момент «t12» используя Transact-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE master

RESTORE DATABASE Test_Recovery FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Full3_Test_Recovery.bak'

WITH  FILE = 1,  NORECOVERY, REPLACE

RESTORE LOG Test_Recovery FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\TailLog1_Test_Recovery.bak'

WITH  FILE = 1,  STOPAT = '2013-12-25T15:58:12'

GO

База успешно восстановится

2.6. Восстановление файлов и файловых групп (Restore Files, Restore Filegroups)

На данный момент мы находимся в точке «t12».

Удалим таблицу «Test_Recovery», которая находится в PRIMARY файловой группе.

Use Test_Recovery

GO

DROP TABLE Table_primary

Проверяем:

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Восстановим файловую группу «PRIMARY» на момент «t9» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Files and FileGroups».

В появившемся окне «Restore Files and FileGroups» в разделе «Sourse for Restore» выбираем «From Device». Далее «Add», Выбираем «\\vniz-tst-bkp01.test.local\Backup_SQL\Prim_Test_Recovery.bak», нажимаем «Ok».

 База успешно восстановится.

Проверяем:

Use Test_Recovery

GO

SELECT * FROM sysobjects WHERE type = 'U'

Восстановим файловую группу «PRIMARY» на момент «t9», используя T-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

USE master;

GO

RESTORE DATABASE Test_Recovery FILEGROUP = 'PRIMARY'

FROM DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Prim_Test_Recovery.bak'

WITH PARTIAL, RECOVERY, REPLACE

База успешно восстановится. Так как мы восстанавливали только часть базы – файловую группу, то мы использовали параметр «PARTIAL».

Восстановление файла.

Сымитируем повреждение файла «Test_Recovery_s2.ndf». Для этого выключим сервис MS SQL и удалим файл Test_Recovery_s2.ndf. Включаем сервис MS SQL.

Проверяем состояние файла:

select name,state_desc from sys.master_files

 WHERE name = 'Test_Recovery_s2'

Восстановим файл «Test_Recovery_s2.ndf» на момент «t10» используя «SQL Server Management Studio». Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «Tasks», далее «Restore», потом «Files and FileGroups».

В появившемся окне «Restore Files and FileGroups» в разделе «Sourse for Restore» выбираем «From Device». Далее «Add», выбираем «\\vniz-tst-bkp01.test.local\Backup_SQL\Sec_s2_Test_Recovery.bak», нажимаем «Ok».

База успешно восстановится.

Проверяем:

select name,state_desc from sys.master_files

 WHERE name = 'Test_Recovery_s2'

Восстановим файл «Test_Recovery_s2.ndf» на момент «t10» используя используя T-SQL.

Щелкаем правой кнопкой мыши по базе «Test_Recovery», в появившемся меню выбираем «New Query»:

В появившемся окне вводим:

RESTORE DATABASE Test_Recovery

FILE = 'Test_Recovery_s2'

FROM  DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\Sec_s2_Test_Recovery.bak'

WITH NORECOVERY, REPLACE

GO

База успешно восстановится.

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

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

2.7.1. Восстановление системных баз из резервной копии, в случае доступности службы SQL

Если экземпляр SQL сервера доступен, то системные базы восстанавливаются согласно приведенной таблице:

Системная база данных	Способ восстановления
Master	Запускаем экземпляр сервера в однопользовательском режиме. Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных. После восстановления следует перезапустить экземпляр SQL сервера.
msdb	Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.
model	Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.
Resource	Восстановление базы осуществляется так же, как восстановление обычного файла, копированием и заменой.

Отдельно рассмотрим восстановление главной системной базы «master».

Запускаем экземпляр сервера в однопользовательском режиме: выключим и включим экземпляр сервера с параметром запуска /m, для этого вводим в «CMD»:

net stop MSSQLSERVER

net start MSSQLSERVER /m

Подключаемся к серверу и запустим процесс восстановления базы из резервной копии.

sqlcmd

RESTORE DATABASE master FROM DISK = '\\vniz-tst-bkp01.test.local\Backup_SQL\master_1.bak' WITH REPLACE;

GO

База успешно восстановится, а экземпляр SQL примет состояние «вне сети».

Стартуем сервер в многопользовательском режиме:

net start MSSQLSERVER

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

Если база данных «master» сильно повреждена и не удается запустить экземпляр SQL сервера, то следует создать новую версию базы данных «master», запустив программу установки SQL сервера, а потом восстановить прежнюю базу из резервной копии.

Сымитируем повреждение базы данных «master». Выключаем сервер SQL, удаляем базу данных «master».

Попытаемся включить экземпляр сервера SQL, по очевидным причинам экземпляр не запустится.

Вставляем установочный диск в CD-ROM и запускаем в командной строке установку SQL сервера со следующими параметрами, подробнее про параметры можно узнать тут.

D:\Setup.exe /ACTION=REBUILDDATABASE /QUIET /SAPWD=Pass#6Ord /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=test\larin

Системные базы данных успешно пересоздадутся. Экземпляр сервера SQL успешно запустится.

Далее можно восстановить системные базы данных из резервной копии.

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

На этом предлагаю закончить.

Вопросы, замечания и предложения пишите в комментариях или на почту.

Удачного тестирования!