Выполнения операции резервного копирования БД используя T-SQL

Резервное копирование базы данных является одной из наиболее важных задач администратора баз данных (DBA). Имея файлы резервной копии и тщательно планируя восстановление после аварии, DBA может восстанавливать систему в случае отказа. DBA несет ответственность за поддержку системы в работоспособном состоянии, насколько это возможно, и за максимально быстрое восстановление ее работы в случае отказа системы. Простой системы может доставлять неудобства и приносить большие убытки. Поэтому важно как можно быстрее восстановить базу данных и вернуть ее к работе. Здесь могут помочь такие технологии, как кластеризация и отказоустойчивые дисковые подсистемы, но они не могут служить заменой добротного планирования и надежного метода резервного копирования.

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

При восстановлении данных из резервной копии они копируются назад в базу данных. Не путайте восстановление (restore) с воспроизведением (регенерацией) (recovery): это две различные операции.

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

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

Воспроизведение

Воспроизведение (регенерация) (recovery) – это способность системы управления реляционной базой данных (СУРБД – RDBMS) уцелеть после аварии системы и воспроизвести выполненные транзакции. SQL Server не выполняет запись на диск после каждого изменения, вносимого в базу данных. Если бы это было так, то большая система (например, банковская) работала бы намного медленнее, поскольку в каждой транзакции приходилось бы ждать, пока не закончится очередная запись, создающая задержку в системе.

Именно задержки при записи изменений на диск приводят к тому, что база данных после отказа системы может остаться в запорченном состоянии из-за того, что некоторые изменения, внесенные в базу данных, могли быть не записаны на диск, а изменения, записанные на диск, могли быть не зафиксированы. Для поддержки целостности базы данных SQL Server протоколирует все изменения в журнале транзакций. (Журнал транзакций подробно описывается в разделе "Журнал транзакций" ниже в этой лекции.) При запуске SQL Server после отказа системы журнал транзакций используется для повторного исполнения (воспроизведения) транзакций, которые были фиксированы, но не записаны на диск, и отката (отмены результатов) транзакций, которые не были фиксированы на момент аварии системы. Такой подход гарантирует точность данных.

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

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

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

Поскольку журнал транзакций является определяющим компонентом для воспроизведения транзакций в случае аварии, он должен всегда располагаться на томе RAID 1 (зеркальном томе). (О RAID [дисковые матрицы]

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

Транзакция, откат которой выполняет SQL Server, идентична транзакции, которая заканчивается командой ROLLBACK. Эта транзакция аннулируется, и все соответствующие данные восстанавливаются к их исходному состоянию.

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

Существуют различные методы резервного копирования базы данных: полное и разностное резервное копирование, резервное копирование журнала транзакций, группы файлов и файла данных. Каждый из них имеет свои режимы и возможности работы. Полное резервное копирование (full backup) предусматривает резервное копирование всех данных базы данных, группы файлов или файла данных. Разностное резервное копирование (differential backup) предусматривает резервное копирование только тех данных, которые изменились с момента последнего резервного копирования. Резервное копирование журнала транзакций используется для резервного копирования и усечения журнала транзакций. (Как уже говорилось, резервное копирование журнала транзакций является определяющей задачей для DBA, поскольку данные журнала транзакций используются в сочетании с резервными копиями базы данных.) Резервное копирование групп файлов и файла данных используется для создания резервной копии определенной группы файлов или файла данных в базе данных.

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

Полное резервное копирование

Как уже говорилось, полное резервное копирование подразумевает резервное копирование всей базы данных. Выполняется резервное копирование всех групп файлов и файлов данных, которые являются частью этой базы данных. Если у вас несколько баз данных, то вам следует создать для всех них резервные копии. Полное резервное копирование является, видимо, наиболее распространенным методом резервного копирования баз данных небольшого и среднего размера. В зависимости от размера баз данных этот процесс может занимать очень много времени, и если для вас важен вопрос времени, то вы можете предусмотреть разностное резервное копирование или резервное копирование групп файлов, как это описано ниже. После запуска резервного копирования вы не можете приостановить его – оно продолжает выполняться, пока не будет получена резервная копия всей базы данных. (О выполнении полного резервного копирования базы данных см. раздел "Выполнение резервного копирования" далее.)

Разностное резервное копирование

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

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

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

Резервное копирование группы файлов

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

Резервное копирование файла данных

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

Приступаем от теории к практики

Использование операторов T-SQL для резервного копирования базы данных может оказаться поначалу чуть сложнее, чем использование Enterprise Manager. Но если вы относитесь к тем администраторам, которые предпочитают автоматизировать операции с помощью сценариев, этот метод будет для вас удобнее. Кроме того, оператор T-SQL BACKUP дает несколько больше возможностей, чем программа резервного копирования в Enterprise Manager. В этом разделе мы рассмотрим синтаксис и параметры оператора BACKUP. На самом деле существуют два оператора резервного копирования; выбор используемого оператора зависит от типа резервного копирования, которое вам нужно выполнить. 

• BACKUP DATABASE. Используется для резервного копирования всей базы данных либо файла или группы файлов.
• BACKUP LOG. Используется для резервного копирования журнала транзакций.

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

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

Оператор BACKUP для полного резервного копирования базы данных имеет следующий синтаксис:

BACKUP DATABASE имя_базы_данных
TO устройство_резервного_копирования
[ WITH необязательные параметры ]

Для этого оператора обязательными параметрами являются только имя базы данных и имя устройства резервного копирования. (Примеры операторов BACKUP можно найти во врезке "Использование оператора BACKUP" далее.)

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

BACKUP DATABASE имя_базы_данных
имя_файла или имя_группы_файлов [,...n]
TO устройство_резервного_копирования
[ WITH необязательные параметры ]

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

Оператор для резервного копирования журнала транзакций имеет следующий синтаксис:

BACKUP LOG имя_базы_данных
{
[ WITH \ NO_LOG | TRUNCATE_ONLY )]
}
|
{
TO устройство_резервного_копирования
}
[ WITH необязательные параметры ]

Для этого оператора обязательными параметрами являются только имя базы данных и параметр WITH NO_LOG или WITH TRUNCATE_ONLY либо имя устройства резервного копирования. Вы можете затем добавлять любые нужные вам параметры. Параметры NO_LOG и TRUNCATE ONLY является синонимами; оба указывают усечение журнала без создания его резервной копии.

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

Во всех трех указанных командах резервного копирования имя_базы_данных представляет базу данных, для которой будет создана резервная копия. Устройство_ резервного_копирования – это имя логического устройства резервного копирования или имя физического устройства. Если указано физическое устройство, то имени устройства должен предшествовать текст DISK =, TAPE = или PIPE = (в зависимости от типа устройства). Вы можете задать одно устройство или набор разделенных запятыми устройств, как это показано в следующих двух примерах:

Backup_dev_1, Backup_dev_2, Backup_dev_3

TAPE = '\\.\Tape0', TAPE = '\\.\Tape1', TAPE = '\\.\Tape2'

Необязательные параметры

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

Параметр	Описание
BLOCKSIZE	Этот параметр указывает размер физического блока в байтах
DESCRIPTION	Этот параметр указывает текстовое описание набора резервного копирования. Его полезно использовать для поиска нужной резервной копии, с которой будет выполняться восстановление
DIFFERENTIAL	Этот параметр указывает разностное резервное копирование. Его можно использовать только при наличии полной резервной копии базы данных
EXPIREDATE = дата RETAINDAYS = дни	Параметр EXPIREDATE указывает дату, когда истекает срок действия данного набора резервного копирования (и когда его можно перезаписывать).
RETAINDAYS	указывает количество дней, соответствующих сроку действия данного набора резервного копирования
PASSWORD = пароль	Параметр PASSWORD позволяет вам задавать пароль для резервной копии, что повышает безопасность самой резервной копии
FORMAT | NOFORMAT	Параметр FORMAT указывает, что заголовок носителя должен быть перезаписан, делая тем самым недействительными первоначальные данные на этом носителе. Параметр NOFORMAT указывает, что заголовок носителя не должен перезаписываться
INIT | NOINIT	Параметр INIT указывает, что набор резервной копии должен находиться в первом файле на данном носителе, причем заголовок носителя остается без изменений, но все данные на этом носителе перезаписываются; иными словами, INIT указывает перезапись всего, чт.е. на ленте. Параметр NOINIT указывает, что данный набор резервной копии добавляется к содержимому носителя. Если вы повторно используете ленты, то вам нужно использовать этот параметр
MEDIADESCRIPTION = текст	Это текстовое поле задает описание набора носителей
MEDIANAME= имя_носителя	Указывает имя носителя
MEDIAPASSWORD = пароль	С помощью этого параметра вы можете указывать пароль для набора носителей
NAME= имя_набора_резервной_копии	Этот параметр позволяет вам задавать имя набора резервной копии
NOSKIP | SKIP	Параметр NOSKIP указывает, что прежде чем перезаписывать наборы резервных копий на данном носителе, будут проверяться даты истечения срока действия соответствующих наборов резервных копий. Параметр SKIP отключает проверку этой даты
NO_TRUNCATE	Этот параметр запрещает усечение журнала транзакций после создания резервной копии. Используется только для резервного копирования журнала транзакций
NOUNLOAD | UNLOAD	Параметр NOUNLOAD указывает, что после завершения резервного копирования носитель не будет выгружаться из устройства (например, не будет извлекаться лента). Параметр UNLOAD указывает, что по окончании резервного копирования носитель будет выгружен
RESTART	Этот параметр указывает SQL Server необходимость перезапуска резервного копирования, которое было прервано
STATS [ = процент ]	Этот параметр указывает вывод сообщения после выполнения определенного процента резервного копирования. Его полезно использовать, если вы любите следить за ходом выполнения операций

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

Примеры

В этом разделе мы рассмотрим пару примеров использования оператора T-SQL BACKUP. Следующий оператор используется для резервного копирования файлов данных базы данных Example:

BACKUP DATABASE Example 
TO Backup_Dev_1, Backup_Dev_2 
WITH 
DESCRIPTION = "DB backup of example", 
STATS = 5 
GO

Здесь устройства резервного копирования – это Backup_Dev_1 и Backup_Dev_2, а сообщение о состоянии будет выводиться после выполнения очередных 5 процентов резервного копирования. Отметим, что в этом примере задано описание резервной копии.

Если вы будете проверять этот пример на небольшой базе данных, такой как Northwind, то вы не увидите сообщений с приращением по 5 процентов. Это будут приращения, например, в 7 процентов, 16 процентов и т.д. Дело в том, что программа резервного копирования читает и записывает за один раз более 5 процентов от объема всех данных такой базы данных. Для наборов данных большего размера за один раз будет записываться меньше 5 процентов данных и поэтому сообщения будут появляться ожидаемым образом.

Следующий оператор используется для резервного копирования журнала транзакций базы данных Example:

BACKUP LOG Example 
TO Backup_Dev_3, Backup_Dev_4 
WITH 
DESCRIPTION = "DB backup of example", 
STATS = 25 
GO

Здесь устройства резервного копирования – это Backup_Dev_3 и Backup_Dev_4, а сообщения о состоянии будут выводиться с интервалом в 25 процентов. В результирующем наборе будет представлен процент выполненных операций, а также результаты резервного копирования. Будет указано количество скопированных страниц, сколько времени длится резервное копирование и какова скорость (Мб/с).

Управление резервным копированием

Поскольку оператор T-SQL BACKUP не выполняется под управлением Enterprise Manager и, тем самым, не выполняется под управлением SQL Server Agent, вы не можете задать расписание этого задания в операторе BACKUP . Но вы можете задать расписание для оператора T-SQL BACKUP с помощью средств планирования заданий в SQL Server. После того как составлено расписание для задания, им можно управлять точно так же, как и заданиями резервного копирования в Enterprise Manager.