Предисловие
Статья 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:
ALTER DATABASE AdventureWorks SET RECOVERY FULL
убеждаюсь, что ошибок в ней еще нет:DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
и создаю полный бэкап:BACKUP DATABASE AdventureWorks
TO DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak'
![](https://habrastorage.org/storage2/1ac/3e2/52a/1ac3e252accf0fe9957ba7172ce40790.png)
В этой базе данных я создаю таблицу crash.
CREATE TABLE crash (txt varchar(1000))
Поле типа varchar мы и будем портить, для того, чтобы проверить что произойдет, если вдруг SQL Server обнаружит в нем не те данные, которые он сам туда записал. Прежде чем что-то испортить, надо это чем-то заполнить. Я забиваю в созданную таблицу левые данные.
SET NOCOUNT ON
DECLARE @i INT
SET @i = 1
WHILE @i<100000
BEGIN
INSERT INTO crash
SELECT REPLICATE('a', 1000)
SET @i = @i + 1
END
SET NOCOUNT OFF
Теперь делаю резервную копию журнала транзакций:BACKUP LOG AdventureWorks
TO DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn'
![](http://habrastorage.org/storage2/161/75b/af4/16175baf42d15ed01933abaffc57cd1f.png)
Теперь немного изменим данные:
![](http://habrastorage.org/storage2/3dd/50b/b98/3dd50bb9880830209ff826b91d298f09.png)
Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR'ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько 'z' на произвольные символы:
![](http://habrastorage.org/storage2/cbc/4b9/e47/cbc4b9e47c7ac76d05abcc3029746191.png)
Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «suspect» не упадет.
Ищем ошибки
Итак, испорченная БД успешно вернулась в строй. Снова запустим проверку целостности:
DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
В результате то, чего мы ждали (обязательно запоминайте номера поврежденных страниц!):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 id = 0), поэтому SQL Server эти данные восстановить не сможет.Сейчас у нас есть три варианта:
- Смириться с потерей данных и выполнить DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS)
- Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
- Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу
Восстанавливаем поврежденную страницу
В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (tail backup). При этом, если у вас Enterprise Edition, вы можете не добавлять параметр NORECOVERY, который переведет БД в состояние «restoring», поскольку эта редакция поддерживает on-line восстановление страниц. Более того, если у вас резервные копии журнала транзакций выполняются на регулярной основе, чтобы не нарушать цепочку журналов, в редакции Enterprise Edition, вы можете сделать COPY_ONLY бэкап.
Я же иду по пути off-line восстановления и выполняю:
BACKUP LOG AdventureWorks
TO DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn'
WITH NORECOVERY
![](http://habrastorage.org/storage2/f84/f17/daa/f84f17daa87d72eb12724635cb7c8f9d.png)
Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):
RESTORE DATABASE AdventureWorks
PAGE = '1:20455'
FROM DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak'
WITH NORECOVERY
В итоге, имеем:
![](http://habrastorage.org/storage2/937/ee0/b57/937ee0b576ecabee2eb882521f1816cb.png)
Обратите внимание на то, что необходимо использовать опцию NORECOVERY, поскольку нам предстоит еще накатывать на нее бэкапы журнала транзакций.
RESTORE LOG AdventureWorks
FROM DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn'
WITH NORECOVERY
иRESTORE LOG AdventureWorks
FROM DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn'
WITH RECOVERY
![](http://habrastorage.org/storage2/64a/af6/c31/64aaf6c3147838277a31be5fbc9e3a8f.png)
Вроде бы все прошло успешно, запускаем DBCC CHECKDB и…
![](http://habrastorage.org/storage2/ab6/b7b/2f8/ab6b7b2f8aa4b0ed2acdcbfa3402ce83.png)
Восстановление прошло успешно.
Обратите внимание, что время простоя сокращается за счет того, что из полного бэкапа мы восстанавливаем не всю БД, а только поврежденные страницы (если бы я восстанавливал бэкап целиком — бэкап восстановился бы за 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. Все та же ошибка:
![](http://habrastorage.org/storage2/e0e/dae/154/e0edae154df37a0677113b56a9a3615d.png)
Сначала переводим БД в режим SINGLE_USER:
ALTER DATABASE AdventureWorks SET SINGLE_USER
А затем, запускаем восстановление:DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
В итоге: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, чтобы она стала доступной для всех и проверяем что пропало:![](http://habrastorage.org/storage2/244/8b0/1ee/2448b01ee92bd0aa77d65fcf435a3e48.png)
Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.
Так почему, все-таки, не перевод?
Итак, почему это все-таки не перевод, а пост «по мотивам». Дело в том, что, в открытом доступе статьи «Page Restore» за авторством Gail Shaw нет. Есть такой раздел в книге SQL Server MVP Deep Dives vol.2, которая продается за довольно-таки ощутимые деньги (но, естественно, легко находится в интернетах) и я не уверен, что публиковать перевод — это эм… правильно что ли.
В общем, я прочитал статью, взял на заметку основные моменты, а потом уже сам писал текст и, попутно, проводил эксперимент по восстановлению. Надеюсь, кому-нибудь этот опыт был полезен.
И, господа, я искренне надеюсь, что если вы решите повторять этот эксперимент, то будете предельно осторожны (например, не будете эксперементировать с основной БД на production-сервере). Помните, что никакой ответственности за ваши действия я не несу.