SQL Server

Восстановление MS SQL базы данных из состояния SUSPECT

Posted


Рассмотрим несколько вариантов восстановления БД, в зависимости от того, насколько повреждены файлы БД зависит успешность того или иного метода. Все описанные способы были лично мной проверены на практике и все случаи восстановления, за исключением одного, были успешны. Используйте данное руководство на свой страх и риск, за совершенные вами действия ответственность несете, вы сами.
Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.
Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.
Для всех версий SQL Server подойдет следующий вариант: делаем Detach database(отсоединить базу данных), удаляем журнал транзакций(файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

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

Присоединить один mdf файл без журнала транзакций, если у вас SQL 2005 и выше, можно командами

или

Если не получилось, то создаем новую базу данных с таким же именем, останавливаем сервер. Подменяем файл mdf файлом от нашей базы, стартуем службу SQL Server и открываем Query analyzer(SQL 2000) или Management studio(SQL 2005/2008) в зависимости от нашей версии сервера.
Пишем следующее:

Если у вас SQL 2000, то далее пишем:

Если SQL 2005 или 2008, то пишем:

где вместо db_name пишем имя своей БД
Жмем F5. После этого наша БД должна быть видна в статусе EMERGENCY. Отлично, приступаем к восстановлению.
Все что написали стираем, чтобы не смущало, и пишем.

Для SQL 2000:

Жмем F5, если все нормально, сервер скажет: Warning: The log for database ‘db_name’ has been rebuilt.

Стираем и пишем:

если DBCC не хочет выполняться, то вместо REPAIR_REBUILD нужно подставить REPAIR_ALLOW_DATA_LOSS
Жмем F5, ждем некоторое время. Сервер вернет кучу сообщений. Если там будут содержаться ошибки, то лучше еще раз выполнить DBCC CHECKDB с параметром REPAIR_REBUILD, пока все ошибки не будут устранены.

Upd: как подсказал Anim в комментах ниже, если и в этом случае запрос не хочет выполняться, БД не хочет переходить в Single User Mode, то можно попробовать следующее:

Для SQL 2005/2008 действия несколько иные:

Тут без вариантов. В SQL 2005 и выше нет инструкции REBUILD_LOG, вместо этого выполняется CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS.

После того как сервер закончит выполнять запрос и вернет результат, меняем REPAIR_ALLOW_DATA_LOSS на REPAIR_REBUILD и выполняем запрос еще раз, это должно убрать оставшиеся ошибки в бд.
После всего этого наша база становится в нормальное состояние и уже доступна для работы с ней, но только в однопользовательском режиме, поэтому завершаем наш процесс возвращением бд в многопользовательский режим.
Пишем:
Для SQL 2000:

Для SQL 2005/2008:

Все. База онлайн и готова к работе. Радуемся и не забываем делать бэкапы.
Большинство информации в свое время было найдено на форуме sql.ru

Восстановление MS SQL базы данных из состояния SUSPECT: 25 комментариев

  1. Спасибо.
    Хорошая статья, мне помогла восстановить БД.
    Спасибо что отдельно расписал для SQL2000.

  2. Из пятёрки найденных статей эта наиболее свежая и рабочая.
    Хотя, если позанудствовать, можно сказать, что в конце статьи автор забыл упомянуть возвращение параметров в исходные значения:
    sp_configure ‘allow_updates’, 0
    и
    reconfigure with override

  3. На SQL2000 SP4 возникала ошибка при попытке перевода моей базы TEST_DB в single-режим:

    use master
    go
    sp_dboption ‘DB_TEST’, ‘single user’, ‘true’
    go
    use «DB_TEST»
    go
    dbcc checkdb(‘DB_TEST’, repair_rebuild)
    go

    на строку (3) ругалось —
    Database options single user and dbo use only cannot be set at the same time.
    и соответственно на строке (7) вылазило —
    Repair statement not processed. Database needs to be in single user mode.

    Победилось заменой первых двух строк на эти три:
    use «TEST_DB»
    alter database «TEST_DB» set SINGLE_USER with rollback immediate
    go
    …и далее по тексту (начиная с команды sp_dboption ‘DB_TEST’ … )

    Также в FAQ на sql.ru написано, что проблему можно решить так:
    Если Вам не удалось перевести базу в single user mode, то для проверки целостности данных можно попробовать dbo only mode:
    sp_dboption », ‘dbo use only’, ‘true’

      1. Спасибо, добавил в статью Ваше примечание. Надеюсь, кому-нибудь сохранит время и нервы

  4. Спасибо огромное за пост, уже не знал что делать с базой MS SQL 2008. Нужно было восстановить в кратчайшие сроки. Так подробно и хорошо все написано.

  5. Всем Спасибо!
    У меня MsSQL2000 SP3 не получалось перевести в SINGLE_USER (ругался на синтаксис) помогло только:

    ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    Может кому пригодиться…

  6. Так же для того же MsSQL2000 SP3 полноценное восстановление в многопользовательский режим только через:

    ALTER DATABASE db_name SET MULTI_USER;

  7. Спасибо огромное, а то мои медленно поседевшие волосы уже начинали выпадать. Отличная статья! SQL 2000.

  8. Понятно написано, спасибо. Но у меня почему БД не становится в статус Emergency, пишет что скрипт прошел, в логе пишет что для БД установлен этот статус, но ничего не происходит.

  9. Спас от инфаркта 5 бухгалтеров. SQL 2000 + 1C. Данные на 30 тысяч жителей по водопользованию. Спасибо огромное!!!

  10. У меня база тоже не переводилась в однопользовательский режим. сделал так: ALTER DATABASE db_name SET MULTI_USER;
    а потом так:
    Use master
    go
    sp_dboption ‘db_name’, ‘single user’, ‘true’
    go
    USE db_name
    GO
    DBCC CHECKDB(‘db_name’, REPAIR_REBUILD)
    go
    перевелось, база пошла проверятся и исправлятся

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *