Восстанавливаем базу данных SQL Server из режима “suspect”
В случае выхода базы данных из строя, может быть повреждена важная информация, потеря которой обернется катастрофическими последствиями как для пользователя, так и для бизнеса.
Автор: Waqas, журналист в сфере информационной безопасности
В случае выхода базы данных из строя может быть потеряна важная информация. Последствия потери данных могут быть катастрофическими как для пользователя, так и для бизнеса. Если крупные организации понесут огромные убытки, малые предприятия могут поплатиться своим существованием.
По словам разработчиков, ошибка присутствует в каждой программе. Даже самое лучшее программное обеспечение может иногда давать сбой.
Иногда работа и жизнь людей зависят от функциональности программного обеспечения. Корректная работа ПО влияет на финансовое благополучие или здоровье людей. Поэтому особенно важно, чтобы при сбоях программного обеспечения, имелась возможность быстро его вернуть в нормальное рабочее состояние.
Программы работают с базами данных. В случае выхода базы данных из строя, может быть повреждена важная информация, потеря которой обернется катастрофическими последствиями как для пользователя, так и для бизнеса. Большинство баз данных работают на сервере Microsoft SQL. В случае проблем с сервером для восстановления базы потребуется много времени и сил.
Существует несколько способов восстановить базу данных после инцидента. Во-первых, следует разобраться с таблицей подозрительных(suspect) страниц. Информация в таблице подозрительных страниц доступна любому пользователю, имеющему доступ к базе данных MSDB. Обновлять базу также может любой пользователь, имеющий разрешение на обновление. Владельцы базы, исправив роль базы данных в MSDB, или сисадмин, исправив роль сервера, могут вставлять, обновлять и удалять записи.
Способы восстановления базы данных из подозрительного режима:
Сброс статуса базы данных + DBCC CHECKDB
Используйте программное обеспечение Recovery Toolbox for SQL Server
Таблица подозрительных страниц содержит информацию о потенциально поврежденных страницах и используется при принятии решения о восстановлении страниц. Подозрительная страница из таблицы находится в базе данных MSDB.
Страница считается «подозрительной», если при попытке ее чтения ядром СУБД SQL Server обнаруживается одна из следующих ошибок.
Ошибка 823: возникает во время проверки циклической контрольной суммы (CRC), запущенной операционной системой, например, ошибка диска (возникает при некоторых аппаратных ошибках)
Ошибка 824: например, разрыв страницы (или любая другая логическая ошибка)
Идентификатор каждой «подозрительной» страницы записывается в таблицу подозрительных страниц. В данную таблицу компонент Database Engine записывает все подозрительные страницы, с которыми сталкивается во время обработки, в частности:
Когда при обработке запроса необходимо прочитать страницу.
При выполнении DBCC CHECKDB.
Во время операции резервного копирования.
Во время операции восстановления, исправления DBCC или удаления базы данных таблица подозрительных страниц также обновляется по мере необходимости.
Ниже приведены несколько способов восстановления базы данных, если она перешла в режим “suspected”.
Во время своей работы я однажды столкнулся с ситуацией, когда рабочая база данных в конце дня перешла в режим “suspected”. Причем в последний раз база была заархивирована много часов назад. К сожалению, вернуться в штатный режим не получилось. DBCC checkdb также отказался запускаться.
Я очень расстроился, пока не нашел решение. Рассмотрим первый способ восстановления базы данных.
Сначала необходимо перевести базу данных в АВАРИЙНЫЙ режим, выполнив следующие действия:
- EXEC sp_resetstatus ‘yourDBname’;
- ALTER DATABASE yourDBname SET EMERGENCY
Затем требуется протестировать базу данных:
- DBCC checkdb (‘yourDBname’)
- ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
- ALTER DATABASE yourDBname SET MULTI_USER
Если не получилось восстановить базу первым способом
У вас имеется поврежденная база данных сервера (MS SQL 2005) и она неисправна. Такую базу невозможно восстановить путем тестирования-исправления (возникает ошибка контрольной суммы). В таком случае база данных не выгружается в файл – выдается та же ошибка. Вы попробовали несколько раз, и это не помогло. Попробуйте восстановить базу данных, протестировав сам SQL.
Команды для тестирования SQL приведены ниже:
- DBCC CHECKDB (‘database’, REPAIR_FAST)
- DBCC CHECKDB (‘database’, REPAIR_REBUILD)
Если обе команды не работают, можно использовать третью. Рекомендуем использовать данную команду только в крайнем случае в связи с опасностью возможной потери данных.
DBCC CHECKDB (‘database’, REPAIR_ALLOW_DATA_LOSS)
Если команда не выполняется из-за не однопользовательского режима, используйте команду:
Alter database db-name set SINGLE_USER
! Перед тестированием обязательно сделайте бэкап!
Используйте программу Recovery Toolbox for SQL Server — важный инструмент в работе любого системного администратора. Программа позволяет работать с файлами MS SQL Server любой версии.
Программа позволяет комплексно восстанавливать файлы базы данных. Особенности программы Recovery Toolbox for SQL Server приведены ниже:
1. Данные из нечитаемых баз данных можно восстановить в приостановленном состоянии (suspended state);
2. Программа работает со всеми версиями Microsoft SQL Server;
3. Программа позволяет восстановить самое важное и ценное в базе данных;
4. В базе данных несколько элементов — тоже не проблема;
5. Восстановливает таблицы при работе с MDF файлами;
6. SQL MDF Recovery экспортирует данные непосредственно в Microsoft SQL Server;
7. Информация сохраняется в том числе в виде скриптов;
8. Извлеченные данные напрямую экспортируются в новую базу данных;
9. Программа работает с любой версией Windows;
10. Мультиязычный интерфейс;
11. Возможен просмотр данных перед восстановлением;
Сбой базы данных после сбоя сервера является самым страшным сном любого сисадмина. Как в такой ситуации восстановить поврежденную базу?
Для восстановления данных после сбоя обычно используется резервная копия. Однако, если по какой-то причине копия не была сделана, попробуйте использовать Recovery Toolbox for SQL Server. Скорее всего, вам удастся восстановить рабочее состояние базы данных.
Для этого необходимо выполнить следующие действия:
1. Установите Recovery Toolbox for SQL Server на свой компьютер. Нет необходимости использовать полную версию, достаточно демонстрационной версии;
2. Выберите файл;
3. После выполнения действий начнется анализ базы данных;
4. Изучите список всех восстановленных таблиц;
5. Просматривайте данные в таблицах;
6. Изучайте восстановленные объекты;
7. Настройте параметры сохранения;
8. Выберите необходимые данные;
9. Сохраните их (для этого потребуется полная версия)
Восстановление базы данных
Как видим, для быстрого исправления MDF файла потребовалось нажать всего несколько кнопок. Все восстановленные данные копируются в новую базу данных или в виде скриптов на диск. Таким образом, программа никак не влияет на поврежденные файлы.
Как это работает?
1. Выбираем поврежденную базу данных.
2. Смотрим, какие данные можно восстановить.
3. Определяемся с вариантом экспорта.
4. Выбираем данные для восстановления.
5. Просматриваем отчет после сохранения.
Программа условно-бесплатная, стоит 99 долларов. Скачать программу можно здесь.
Источник
Восстановление отдельных страниц в базе данных
Предисловие
Статья Gail Shaw «Help, my database is corrupt. Now what?», перевод которой я запостил на прошлой неделе, вызвала, вроде бы, определенный интерес, но она, увы, не содержала «практики». Да, там написано как можно спасти данные, но нет никаких примеров.
Изначально я хотел сделать еще один перевод все того же автора, но, подумав, решил написать пост «от себя», как бы «по мотивам». Причины, побудившие меня поступить так, я опишу в конце поста, в примечаниях.
Восстановление баз данных в SQL Server
Как уже было сказано в предыдущей статье, в том случае, если повреждены страницы кластерного индекса или кучи, то данные, содержащиеся на этих страницах, потеряны и единственным вариантом для их восстановления является непосредственно восстановление базы данных.
SQL Server предоставляет множество возможностей для восстановления баз данных. Во-первых, это восстановление базы данных целиком — оно может занимать довольно много времени (зависит от размера БД и скорости жестких дисков). Во-вторых, восстановление отдельных файловых групп, либо файлов, если ваша БД состоит из нескольких файловых групп (или, соответствено, файлов). В этом случае, есть возможность восстановления только поврежденных частей БД, не затрагивая остальных. Эти два вида восстановления БД используются довольно часто и затрагиваться в дальнейшем не будут.
В-третьих, в SQL Server 2005 появилась возможность восстановления отдельных страниц БД — в этом случае из бэкапа будут восстановлены только указанные страницы. Такое восстановление будет особенно актуально, если DBCC CHECKDB найдет несколько поврежденных страниц в какой-нибудь огромной таблице, «лежащей» в здоровенном файле. За счет того, что восстанавливаться будет не весь файл, и даже не вся таблица, а только несколько страниц — время простоя может быть значительно сокращено.
Требования и ограничения
Модель восстановления и доступность резервных копий журнала транзакций
Самое главное, что нужно помнить — для восстановления отдельных страниц, база данных должна использовать полную (full) модель восстановления, либо модель восстановления с неполным протоколированием (bulk-logged). Если ваши базы находятся в простой (simple) модели восстановления — дальше вы можете уже и не читать.
Второе требование — ваши полные бэкапы и бэкапы журнала транзакций должны образовывать неразрывную цепочку журналов (log chain). Если вы никогда не выполняете команду BACKUP LOG WITH TRUNCATE_ONLY (NO_LOG) и не переключаетесь в простую модель восстановления для того, чтобы уменьшить журнал транзакций, и у вас есть ВСЕ резервные копии журнала транзакций с момента последней полной резервной копии не содержащей поврежденных страниц (включая эту самую полную резервную копию) — за цепочку журналов можно не волноваться.
В модели восстановления с неполным протоколированием, теоретически, восстановление отдельных страниц должно работать нормально в том случае, если соблюдаются условия описанные выше, и восстанавливаемые страницы не изменялись операциями, выполняемыми с минимальным протоколированием.
Редакции SQL Server
Восстановление страниц возможно в любой редакции SQL Server, но для редакций Enterprise Edition и Developer Edition возможно восстановление поврежденных страниц on-line, т.е. к базе данных, во время восстановления, можно обращаться (и более того, обращаться можно даже к той таблице, к которой относятся восстанавливаемые в данный момент страницы, если сами эти страницы не будут «затрагиваться» — в противном случае, запрос завершится ошибкой). Для редакций «ниже» Enterprise Edition, восстановление страниц проходит в режиме off-line и база данных, на время восстановления, становится недоступной.
Тип поврежденной страницы
В том случае если повреждены страницы индекса, либо данных, их восстановление возможно в режиме online в редакции Enterprise Edition.
Страницы, приндалежащие критически важным системным таблицам могут быть восстановлены, но база данных, при восстановлении, будет недоступна в любой редакции SQL Server.
«Карты размещения» не могут быть восстановлены «отдельно». Если повреждены GAM, SGAM, PFS, ML, DIFF-страницы, необходимо восстанавливать базу данных целиком. Единственным исключением являются IAM-страницы. Хотя они и относятся к «картам размещения», но они описывают только одну таблицу, а не всю базу данных, и их восстановление возможно.
Загрузочная страница базы данных (9-я страница в 1-м файле БД) и страница заголовка файла (0-я страница в каждом файле) не могут быть восстановлены «отдельно», при их повреждении придется восстанавливать БД целиком.
Собственно, восстановление
Теперь, наконец, переходим от теории к практике.
В первую очередь, для тренировки, нужна испорченная база данных.
Портим БД
Для экспериментов я буду использовать базу данных AdventureWorks, которая поставляется вместе с SQL Server. Если вы не устанавливали ее, при желании, можно скачать здесь. Перевожу ее в модель восстановления full:
убеждаюсь, что ошибок в ней еще нет:
и создаю полный бэкап:
В этой базе данных я создаю таблицу crash.
Поле типа varchar мы и будем портить, для того, чтобы проверить что произойдет, если вдруг SQL Server обнаружит в нем не те данные, которые он сам туда записал.
Прежде чем что-то испортить, надо это чем-то заполнить. Я забиваю в созданную таблицу левые данные.
Теперь делаю резервную копию журнала транзакций:
Теперь немного изменим данные:
Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR’ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько ‘z’ на произвольные символы:
Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «suspect» не упадет.
Ищем ошибки
Итак, испорченная БД успешно вернулась в строй. Снова запустим проверку целостности:
В результате то, чего мы ждали (обязательно запоминайте номера поврежденных страниц!):
Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘crash’ (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘AdventureWorks’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).
В данном случае повреждены сами данные, находящиеся в куче (index поэтому SQL Server эти данные восстановить не сможет.
Сейчас у нас есть три варианта:
- Смириться с потерей данных и выполнить DBCC CHECKDB(‘AdventureWorks’, REPAIR_ALLOW_DATA_LOSS)
- Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
- Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу
Со вторым вариантом все должно быть понятно, а вот что произойдет если запустить DBCC CHECKDB или как восстанавливаются отдельные страницы — я покажу дальше.
Восстанавливаем поврежденную страницу
В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (tail backup). При этом, если у вас Enterprise Edition, вы можете не добавлять параметр NORECOVERY, который переведет БД в состояние «restoring», поскольку эта редакция поддерживает on-line восстановление страниц. Более того, если у вас резервные копии журнала транзакций выполняются на регулярной основе, чтобы не нарушать цепочку журналов, в редакции Enterprise Edition, вы можете сделать COPY_ONLY бэкап.
Я же иду по пути off-line восстановления и выполняю:
Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):
В итоге, имеем:
Обратите внимание на то, что необходимо использовать опцию NORECOVERY, поскольку нам предстоит еще накатывать на нее бэкапы журнала транзакций.
и
Вроде бы все прошло успешно, запускаем DBCC CHECKDB и…
Восстановление прошло успешно.
Обратите внимание, что время простоя сокращается за счет того, что из полного бэкапа мы восстанавливаем не всю БД, а только поврежденные страницы (если бы я восстанавливал бэкап целиком — бэкап восстановился бы за 8,5 секунд, но, чем больше размер БД — тем больше будет разница во времени). Счастливчики с SQL Server Enterprise Edition, использующие on-line восстановление, так же сэкономят время на восстановлении из бэкапов лога, а при off-line восстановлении, увы, журналы будут обрабатываться целиком.
Стоит так же добавить, что в SQL Server 2005, 2008, 2008 R2 восстановление отдельной страницы возможно только с помощью T-SQL, в Denali появилась возможность делать это через GUI.
А если все-таки DBCC CHECKDB?
На всякий случай я решил проверить что сделает SQL Server, если я запущу DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS. Все та же ошибка:
Сначала переводим БД в режим SINGLE_USER:
А затем, запускаем восстановление:
В итоге:
Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).
Ага, SQL Server удалил «испорченную» страницу. Переводим БД в режим MULTI_USER, чтобы она стала доступной для всех и проверяем что пропало:
Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.
Источник
