Резервное копирование и восстановление баз данных
При возникновении проблем с физическими устройствами, возникновении сбоев в программных средствах, при непреднамеренном уничтожении данных из базы данных придется производить резервное копирование, с последующим восстановлением. Существует два основных способа резервного копирования:
1. Полное архивирование (или дамп базы данных, содержит все используемые страницы базы данных, full backup) подразумевает создание копий всех страниц с данными в БД, включая все системные таблицы. Так как среди системных таблиц есть специальная таблица Syslogs , в которую записывается журнал транзакций, при выполнении полного архивирования создается копия и журнала транзакций.
2. Пошаговое архивирование(incremental backup) предусматривает архивирование только журнала транзакций. Другими словами, создается копия только измененных данных.
3. Архивирование таблицы(table backup)-позволяет создать архивную копию отдельной таблицы базы данных. Восстановить отдельную таблицу можно и из полного архива БД. Архивирование таблиц следует сочетать с регулярным полномасштабным архивированием данных.
1. Прежде всего, при резервном копировании (при архивировании) нужно определить место копии данных или иначе устройство для резервного копирования данных.
2. После того как вы решили какое устройство вы будете использовать для резервного копирования, необходимо указать это устройство SQL Server, используя хранимую процедуру sp_addumpdevice:
sp_addumpdevice{‘тип устройства’,’логическое имя’,’физическое имя’}
[, { тип контроллера|’состояние устройства’}]
тип устройства – определяет тип устройства резервного копирования. Тип данных, используемый для этого аргумента, - varchar(10), значение по умолчанию не используется, и должно быть выбрано одно из следующих значений:
- Disk – жесткий диск
- Pipe – именованный канал
- tape – устройство для записи на магнитную ленту
’логическое имя’ – указывает имя устройства резервного копирования, которое вы затем сможете использовать в командах BUCKUP и RESTORE. Тип данных для этого аргумента – sysname, он не может принимать значение NULL и не имеет значения по умолчанию. Этот аргумент показывает, каким образом вы будете идентифицировать дамп-устройство непосредственно во время резервного копирования
’физическое имя’ – указывает на физическое местонахождение устройства резервного копирования. Физическое имя должно включать полный путь. Тип данных этого аргумента nvarchar(26), он не может принимать значения NULL и не имеет значения по умолчанию.
Устройство резервного копирования на магнитной ленте должно быть подсоединено к компьютеру, на котором находится сервер, не может быть использовано удаленное устройство. Если имя устройства содержит специальные символы, оно должно быть заключено в кавычки.
тип контроллера – может быть опущен. Тип данных для этого параметра smallint, по умолчанию NULL, и этот параметр может принимать следующие значения: 2 – используется с disk,
5- используется с типом устройства tape , 6 - используется с устройством pipe.
’состояние устройства’ – определяет, будут ли ярлыки магнитных лент читаться (NOSKIP) или игнорироваться (SKIP). Следует указывать либо параметр ‘тип контроллера’ либо ‘состояние устройства’. Тип данных для состояния устройства varchar(40), значение по умолчанию NOSKIP.
Для устройства резервного копирования разрешения должны быть установлены правильно, в противном случае определить и использовать устройства резервного копирования будет невозможно. Также для того, чтобы определить устройство резервного копирования, вы должны быть в базе данных master, так как устройство резервного копирования – это ресурс, определяемый для всего сервера, и записи о нем должны храниться в таблице sysdevices баы данных master.
Например:
Use master
Exec sp_addumpdevice ‘disk’,’newdumpdev’,’c:\dump\newdump.dat’
После того как определено устройство резервного копирования, можно создавать резервную копию базы данных. Для этого используется команда BACKUP. Команда создания резервной копии базы данных:
BACKUP DATABASE {база данных|@переменная для базы данных’} to устройство резервного копирования [,…]
[With blocsize – {размер блока|@переменная для размера блока}]
[,description={текст|@переменная для текста}]
[,differential]
[,expiredate={дата|@переменная для даты}|retaindays={количество дней |@переменная для количества дней}]
[,format|noformat]
[,unit|nounit]
[,mediadescription={текст|@переменная для текста}]
[,medianame={имя носителя|@переменная для имени носителя}]
[,name={имя набора резервных копий|@переменная для имени резервных копий}]
[,noskip|skip]
[,{nounload|unload}]
[, restart]
[,stats=проценты]
Команда создания резервной копии журнала транзакций:
BACKUP LOG {база данных|@переменная для базы данных’}
[with no_log|trancate_only}]
to устройство резервного копирования [,…]
[With blocsize – {размер блока|@переменная для размера блока}]
[,description={текст|@переменная для текста}]
[,differential]
[,expiredate={дата|@переменная для даты}|retaindays={количество дней |@переменная для количества дней}]
[,format|noformat]
[,unit|nounit]
[,mediadescription={текст|@переменная для текста}]
[,medianame={имя носителя|@переменная для имени носителя}]
[,name={имя набора резервных копий|@переменная для имени резервных копий}]
[,noskip|skip]
[,{nounload|unload}]
[, restart]
[,stats=проценты], где
<устройство резервного копирования> - имя устройства резервного копирования |disk|tape|pipe
blocsize – размер физического блока в байтах
description –используется для добавления описания к набору резервных файлов
differential –определяет, что будет создана резервная копия только изменений, внесенных в базу данных с момента последнего полного резервного копирования
expiredate – определяет дату, после которой резервная копия считается устаревшей и может быть перезаписана
retaindays – определяет сколько дней может пройти до момента, когда резервная копия на устройстве может быть перезаписана
format|noformat – указывает, что может быть создана новая информация о разметке для всех томов, используемых при копировании
unit –указывает на то, что резервная копия будет первым файлом на диске или магнитной ленте, но сохраняет существующую информацию о разметке. При этом вся информация на устройстве резервного копирования будет перезаписана
nounit – параметр по умолчанию, указывает на то, что новая резервная копия будет добавлена к уже существующей информации на носителях устройств резервного копирования
mediadescription – добавляет описание носителя в текстовом формате
medianame – указывает имя носителя (до 128 символов) для всего набора носителей, используемого для резервного копирования
name – определяет имя резервной копии
noskip – даты устаревания и имена резервных копий должны быть проверены, прежде чем старые будут перезаписаны |skip – отключается проверка дат устаревания и имен
nounload магнитная лента по умолчанию будет выгружена | unload – будет автоматически перемотана и выгружена.
Restart – проверяет, что если операция резервного копирования будет прервана, ее можно будет перезапустить
Stats – выводит сообщение после того как выполнен процент от всего задания (если процент опущен, то через 10 процентов от общего задания выводится сообщение о резервном копировании)
No_log – удаляет неактивную часть журнала транзакций и обрезает файл журнала транзакций. Этот параметр освобождает дисковое пространство. При этом необходимости указывать устройство резервного копирования нет, так как резервная копия журнала транзакций создана не будет. После использования этого параметра записи из журнала транзакций не могут быть восстановлены. Поэтому в целях обеспечения возможности восстановления вы должны немедленно выполнить команду BACKAP DATABASE
No_Truncate – этот параметр создает резервную копию журнала транзакций без его усечения. Этот параметр используется, если база данных повреждена или помечена как подозрительная (suspect)/
При выборе стратегии резервного копирования учитывается размер базы данных, то насколько часто обновляется в ней информация и бюджет, которым вы располагаете. Обычно для баз данных небольшого размера производится периодическое полное резервное копирование. Для баз данных большого размера полное резервное копирование производится реже, а вместо него выполняются операции по резервному копированию журнала транзакций (который содержит изменения, внесенные с момента последнего резервного копирования). Конкретные параметры зависят от того, сколько времени занимает создание резервной копии базы данных и насколько часто в базу данных заносятся изменения. Резервное копирование журнала транзакций производят, когда необходимо освободить занимаемое им дисковое пространство. Информация о резервном копировании хранится в базах данных master и msdb.
Выбор устройства резервного копирования зависит от нескольких факторов: где вы собираетесь хранить резервные копии, насколько быстро в случае необходимости вам надо будет восстановить данные из резервной копии и будете ли вы куда-либо передавать резервные копии.
Для создания резервной копии базы данных нет необходимости прекращать работу с ней. Кроме того, резервные копии баз данных могут добавляться к существующим наборам резервных копий, так что резервные копии баз данных и журналов транзакций могут находиться на одном физическом устройстве.
Во время выполнения резервного копирования базы данных нельзя выполнять команды ALTER DATABASE с ключами ADD FILE, REMOVE FILE, CREATE INDEX, bcp, BULK INSERT, SELECT INTO. Во время резервного копирования можно делать все транзакции, включая операции без записи в журнал транзакций.
Способ восстановления базы данных зависит от способа создания резервной копии. Если была создана резервная копия только базы данных, то восстанавливаться будет база данных. Если – полная резервная копия, то восстанавливается база данных вместе с журналом транзакции. Для восстановления баз данных и журналов транзакций используется команда RESTORE DATABASE [опции], RESTORE LOG [опции]
Восстановление данных подразумевает загрузку последней архивной копии данных и всех архивных копий журнала транзакций, выполненных с момента архивирования данных. Т.о. данные восстанавливаются по состоянию на момент создания последней архивной копии журнала транзакций.
При восстановлении данных и журнала транзакций необходимо учесть следующее:
1)Когда БД восстанавливается на основе архивной копии, она должна быть закрыта, т.к. данные в этой БД переписываются на загружаемые данные.
2)Если данные восстанавливаются из-за повреждения физического устройства, в первую очередь необходимо стереть поврежденную БД. Это можно выполнить с помощью команды Drop Database или системной процедуры sp_dbremove. Только после этого можно пересоздать БД и восстановить данные из архивной копии.
3)Загрузка архивной копии журнала транзакций выполняется путем повторного выполнения записанных в нем изменений и отмены транзакций, которые не были завершены на момент создания архивной копии.
4)Архивные копии журнала транзакций должны загружаться в последовательности, в которой были созданы.