Пол Рэндал (paul@SQLskills.com) – работал в команде Microsoft SQL Server как разработчик и менеджер, написал много команд DBCC, отвечал за ядро движка хранения в SQL Server 2008

Методы планирования операций резервного копирования и восстановления известны многим, то же касается и применения команд BACKUP и RESTORE. Большинству администраторов баз данных знакомы часто используемые параметры команд, но лишь немногие знают о пользе менее известных параметров. О применении таких параметров и пойдет речь в данной статье.

Я полагаю, что читатели знакомы с такими понятиями, как резервное копирование, восстановление, файлы данных и журналов, транзакции. Начинающим администраторам рекомендуется посмотреть учебные видео о механизмах резервного копирования (http://technet.microsoft.com/en-us/sqlserver/gg429796.aspx) и восстановления (http://technet.microsoft.com/en-us/sqlserver/gg508895.aspx), подготовленные мною в 2010 г. для компании Microsoft.

Проверка целостности резервной копии

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

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

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

Для проверки контрольных сумм страниц необходимо использовать команду BACKUP с параметром CHECKSUM. Это очень полезный параметр, с его помощью можно выявить испорченные резервные копии. Кроме того, если обнаружена испорченная страница, резервное копирование завершится неудачей (также по умолчанию) с сообщением, аналогичным показанному на экране 1. При использовании этого параметра в процессе резервного копирования также вычисляется контрольная сумма всей резервной копии, сохраняемая в ее заголовке.

 

Сообщение об ошибке резервной копии из-за порчи страницы
Экран 1. Сообщение об ошибке резервной копии из-за порчи страницы

Некоторые администраторы баз данных проверяют целостность резервной копии другим способом: с помощью команды RESTORE с параметром VERIFYONLY. К сожалению, при этом проверяется только заголовок, но не данные резервной копии. Необходимо использовать команду RESTORE с двумя параметрами: CHECKSUM и VERIFYONLY. Только в этом случае процесс восстановления повторно проверяет все контрольные суммы страниц и всей резервной копии. Обратите внимание, что нельзя проверить контрольные суммы страниц резервной копии, созданных без параметра CHECKSUM.

Обязательно используйте параметр CHECKSUM при создании и проверке резервных копий, если только вы не намерены по-настоящему восстанавливать резервные копии, проверяя их целостность. Дополнительная нагрузка на процессор при вычислении контрольных сумм страниц ничтожна, а время операции резервного копирования не увеличивается. Можно быстро выяснить, используются ли контрольные суммы, заглянув в столбец has_backup_checksums в таблице backupset в msdb или в поле HasBackupChecksums в выводе команды RESTORE HEADERONLY, примененной к единственной резервной копии. Более подробные сведения и примеры приведены в моем блоге (http://www.sqlskills.com/BLOGS/PAUL/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx).

Снижение потерь данных в случае аварии

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

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

Если попытаться выполнить резервное копирование заключительного фрагмента журнала, а файлы данных отсутствуют или повреждены, будет получено сообщение об ошибке, аналогичное показанному на экране 2. В этом случае можно использовать параметр NO_TRUNCATE с инструкцией BACKUP, что позволит выполнить резервное копирование журнала, так как метаданные базы данных, отслеживающие резервные копии, зеркалируются на странице заголовка файла журнала. Даже если весь экземпляр SQL Server недоступен, можно перенести уцелевший файл журнала на другой экземпляр SQL Server, присоединить файл журнала к нему, а затем выполнить резервное копирование заключительного фрагмента журнала. Способы резервного копирования заключительного фрагмента журнала в обычных условиях и при отсутствующем экземпляре описаны в заметке «Disaster 101: Backing up the Tail of the Log» в моем блоге (http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx).

 

Сообщение об ошибке резервной копии из-за отсутствующих или поврежденных файлов данных
Экран 2. Сообщение об ошибке резервной копии из-за отсутствующих или поврежденных файлов данных

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

Повышение скорости резервного копирования

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

* сокращается время присутствия в системе дополнительной рабочей нагрузки на систему ввода-вывода (и потенциально — нагрузки на процессор, связанной со сжатием резервных копий);

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

Самый простой способ повысить скорость резервного копирования — обеспечить параллельность операции. Этот метод известен как чередование (backup striping). По умолчанию существует единственный поток чтения данных для каждого логического диска или точки подключения, из которой выполняется чтение, и единственный поток записи данных для каждого устройства резервного копирования, в которое выполняется запись. В целом увеличение числа потоков чтения и записи должно привести к повышению производительности вплоть до того момента, когда подсистема ввода-вывода становится узким местом для операций чтения или записи. В каждой системе существует своя точка насыщения. В статье «Tuning the Performance of Backup Compression in SQL Server 2008» (http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx), подготовленной консультативной группой SQLCAT, рассматривается чередование резервных копий. Там также приведены многочисленные тестовые данные и диаграммы, показывающие возможный выигрыш.

Другой способ увеличить скорость резервного копирования — вручную указать количество буферов ввода-вывода, используемых при резервном копировании (с параметром BUFFERCOUNT) и размер каждого буфера (с параметром MAXTRANSFERSIZE). И вновь, оптимальная комбинация этих параметров у каждой системы своя, но в итоге можно получить значительный выигрыш. Следует проявлять осторожность, изменяя эти значения, так как размер необходимого виртуального адресного пространства (в дополнение к используемому буферному пулу) равняется BUFFERCOUNT x MAXTRANSFERSIZE, что может привести к ошибкам нехватки памяти в 32-разрядных системах.

В данном случае неизбежен компромисс, подробно описанный Джонатаном Кехайасом в его блоге в статьях «An XEvent a Day (17 of 31)» (http://sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-%2817-of-31%29-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-%28Part-1%29.aspx) и «An XEvent a Day (18 of 31)» (http://sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-%2818-of-31%29-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-%28Part-2%29.aspx). В этих заметках содержатся ссылки на дополнительные материалы службы поддержки клиентов Microsoft (CSS).

Сокращение времени простоя после аварии

Одна из обычных целей операции восстановления — восстановить как можно меньше данных, чтобы сократить время простоя. С появлением метода поэтапного восстановления эта задача стала гораздо проще. Дополнительное преимущество приносит поэтапное восстановление в оперативном режиме. В ходе поэтапной операции восстанавливается только часть базы данных, например одна страница (с использованием параметра PAGE) или один файл (с параметром FILE). Поэтапное восстановление в оперативном режиме реанимирует часть базы данных, а остальная часть базы данных остается доступной. Это возможно благодаря частичной доступности базы данных.

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

Если в результате проверки целостности выясняется, что существует единственная испорченная страница или небольшое число испорченных страниц, гораздо быстрее восстановить постранично, чем восстанавливать файл или группу файлов, частью которых они являются. Более подробно о восстановлении страниц рассказано в тематическом разделе электронной документации по SQL Server «Performing Page Restores» (http://msdn.microsoft.com/en-us/library/ms175168.aspx) и в моем блоге MSDN «Fixing damaged pages using page restore or manual inserts» (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx).

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

Частичная доступность базы данных появилась в версии SQL Server 2005, а поэтапное восстановление в оперативном режиме возможно только в Enterprise Edition. Благодаря частичной доступности базы данных некоторые ее части могут быть автономными, а с остальными пользователь может работать в оперативном режиме. Поэтапное восстановление в оперативном режиме позволяет вернуть к жизни автономные части, сохраняя базу данных в оперативном режиме.

Например, предположим, что испорчена база данных VLDB, в которой содержатся данные о текущих и прошлых продажах. Если в VLDB существует единственный файл данных, то восстановление базы данных до состояния, пригодного для работы торгового приложения, требует обработки всей VLDB целиком. Но если в архитектуре VLDB заложены возможности хранения различных фрагментов данных в нескольких группах файлов (например, текущих продаж в одной группе файлов, продаж 2010 года — в другой, 2009 года — в третьей и т.д.), то базу данных можно восстановить поэтапно и вернуть в оперативный режим, не восстанавливая все данные. При этом восстанавливается группа файлов PRIMARY с использованием параметра PARTIAL, затем восстанавливается группа файлов текущих продаж, после чего базу данных можно перевести в оперативный режим. Затем можно поэтапно восстановить другие группы файлов, содержащие исторические данные. Такой подход значительно сокращает время простоя приложения.

Более подробно о частичной доступности базы данных рассказано в тематическом разделе электронной документации по SQL Server «Performing Piecemeal Restores» (http://msdn.microsoft.com/en-us/library/ms177425.aspx). Пример сценария такого восстановления опубликован в блоге SQL Server Questions Answered (Using Partial Database Availability for Targeted Restores) по адресу http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/entryid/12771/using-partial-database-availability-for-targeted-restores.

Восстановление для точки во времени

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

Если время удаления неизвестно, можно провести операцию, которую я называю «перебором журнала». В такой операции восстановления используются параметры STOPAT и STANDBY для прохода по журналу с малым временным шагом, чтобы не пропустить нужный момент. Данный сценарий (наряду с некоторыми другими) проиллюстрирован в веб-трансляции «Part 10: Recovering from Human Error (Level 200)» по адресу http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/entryid/12771/using-partial-database-availability-for-targeted-restores.

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

BEGIN TRANSACTION PaulsTran WITH MARK;

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

Для применения любого из этих параметров необходимо знать имя метки журнала. Если они не были записаны вручную, их можно найти в таблице logmarkhistory в msdb (еще одна причина для резервного копирования базы данных msdb). Если информация в таблице logmarkhistory утеряна, найти имена меток журнала трудно. Для этого нужно использовать сторонний инструмент или команды undocumented log и log backup.

Если одно имя метки журнала применяется неоднократно, необходимо указать время останова с помощью параметра AFTER <время>. В противном случае остановка произойдет у первой метки, совпадающей с именем, указанным в инструкции RESTORE.

Обычно помеченные транзакции используются, только если задействована одна база данных. Однако это единственный надежный способ восстановить несколько баз данных к одной транзакционно соответствующей точке, на одном или нескольких экземплярах. Если все базы данных на одном экземпляре, метка журнала автоматически вставляется в журнал транзакций каждой базы данных. Для баз данных на удаленных экземплярах необходимо использовать распределенную транзакцию, чтобы вставить метку журнала в каждый журнал транзакций. Подробнее эта процедура описана в тематическом разделе электронной документации по SQL Server «Using Marked Transactions (Full Recovery Model)» (http://msdn.microsoft.com/en-us/library/ms187014.aspx).

Все необходимые базы данных можно восстановить до одной точки, указав одно и то же имя метки журнала (и AFTER <время>, при необходимости) для каждой последовательности восстановления баз данных. Подробнее об использовании помеченных транзакций в ходе восстановления рассказано в тематическом разделе электронной документации по SQL Server «Recovering to a Marked Transaction» (http://msdn.microsoft.com/en-us/library/ms188623.aspx).

Перезапуск прерванной операции восстановления

WITH RESTART — один из малоизвестных параметров команды RESTORE. С его помощью можно перезапустить прерванную операцию восстановления. Операция восстановления периодически записывает файл контрольных точек, в котором указывается, до какого места прошло восстановление. Файлы контрольных точек (не имеющих никакого отношения к обычным контрольным точкам базы данных) сохраняются в папке \InstanceName\MSSQL\Backup.

Файл контрольных точек обновляется при следующих условиях:

* завершено создание и заполнение нулями файла базы данных;

* обработаны все резервные наборы данных;

* завершена стадия повтора при восстановлении.

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

 

Сообщение об ошибке: не обнаружена контрольная точка
Экран 3. Сообщение об ошибке: не обнаружена контрольная точка

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

Самый полезный параметр

Меня часто спрашивают, какой из малоизвестных параметров резервного копирования и восстановления наиболее полезен. Огромный выигрыш в быстродействии можно получить, настраивая буферы ввода-вывода с помощью параметров BUFFERCOUNT и MAXTRANSFERSIZE. Благодаря одностраничному и поэтапному восстановлению с использованием параметров PAGE и PARTIAL существенно сокращается время простоя после аварии. Но если бы мне пришлось указать единственный параметр, я бы выбрал NO_TRUNCATE для резервного копирования заключительного фрагмента журнала. Это единственный способ выполнить восстановление точно до момента аварии, если файлы данных недоступны.