Восстановление базы sql: Как исправить ошибки MS SQL и восстановить базу данных OTUS

Восстановление отдельных страниц в базе данных / Хабр

Предисловие

Статья 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'

В этой базе данных я создаю таблицу 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'

Теперь немного изменим данные:

Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR’ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько ‘z’ на произвольные символы:

Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «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 эти данные восстановить не сможет.

Сейчас у нас есть три варианта:

1. Смириться с потерей данных и выполнить DBCC CHECKDB(‘AdventureWorks’, REPAIR_ALLOW_DATA_LOSS)
2. Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
3. Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу

Со вторым вариантом все должно быть понятно, а вот что произойдет если запустить DBCC CHECKDB или как восстанавливаются отдельные страницы — я покажу дальше.

Восстанавливаем поврежденную страницу

В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (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

Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):

RESTORE DATABASE AdventureWorks
PAGE = '1:20455'
FROM DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak'
WITH NORECOVERY

В итоге, имеем:

Обратите внимание на то, что необходимо использовать опцию 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

Вроде бы все прошло успешно, запускаем 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:

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, чтобы она стала доступной для всех и проверяем что пропало:

Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.

Так почему, все-таки, не перевод?

Итак, почему это все-таки не перевод, а пост «по мотивам». Дело в том, что, в открытом доступе статьи «Page Restore» за авторством Gail Shaw нет. Есть такой раздел в книге SQL Server MVP Deep Dives vol.2, которая продается за довольно-таки ощутимые деньги (но, естественно, легко находится в интернетах) и я не уверен, что публиковать перевод — это эм… правильно что ли.

В общем, я прочитал статью, взял на заметку основные моменты, а потом уже сам писал текст и, попутно, проводил эксперимент по восстановлению. Надеюсь, кому-нибудь этот опыт был полезен.

И, господа, я искренне надеюсь, что если вы решите повторять этот эксперимент, то будете предельно осторожны (например, не будете эксперементировать с основной БД на production-сервере). Помните, что никакой ответственности за ваши действия я не несу.

Резервное копирование и восстановление базы данных SQL Server из командной строки

2023 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2023-06-30 10:45

Самая важная часть плана обслуживания SQL Server — это резервное копирование баз данных. Чтобы создать резервную копию базы данных, вы не можете просто скопировать соответствующие файлы MDF и LDF базы данных, поскольку SQL Server блокирует их. Вместо этого вам нужно создать настоящий файл резервной копии через SQL Server.

Хотя это можно сделать, разработав План обслуживания внутри SQL Management Studio, бесплатные версии Express SQL Server не предлагают этот интерфейс. Чтобы обойти это, вы можете легко создавать резервные копии своих баз данных, выполнив приведенную ниже команду во время входа в систему в качестве администратора Windows:

SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”

Приведем примеры ниже.

Экземпляр SQL Server по умолчанию:

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

Именованный экземпляр SQL Server:

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB. bak'”

Вышеописанная версия полностью восстанавливает резервную копию «MyDB» в файл «D: BackupsMyDB.bak», который можно использовать для аварийного восстановления. Конечно, вы можете изменить местоположение и файл резервной копии на все, что вам нужно, но убедитесь, что вы указали местоположение папки, которое существует на локальном компьютере. Затем этот файл резервной копии можно скопировать на ленточный накопитель или другое внешнее хранилище.

Общий вопрос: «Можно ли создать резервный файл для сопоставленного диска или UNC-адреса?», А быстрый ответ — нет. Причина в том, что служба Windows SQL Server работает как учетная запись пользователя, которая имеет доступ только к локальной машине. Вы можете изменить учетную запись, на которую работает служба, но это сильно обескураживает по соображениям безопасности.

Чтобы восстановить базу данных из файла резервной копии, просто используйте команду:

SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM DISK=’X:PathToBackupFile[File_Name]. bak'”

Например:

SqlCmd -E -S MyServer –Q “RESTORE DATABASE [MyDB] FROM DISK=’D:BackupsMyDB.bak'”

Вышеупомянутая команда восстановит резервную копию «MyDB» из данных, хранящихся в файле резервной копии «D: BackupsMyDB.bak». Любые изменения, внесенные в MyDB с момента создания файла резервной копии, будут потеряны.

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

Примечание: приведенные выше команды будут работать на SQL 2005 и выше (любое издание). Для SQL 2000 и ранее замените «SqlCmd» на «oSql».

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

Последнее изменение: 3 мая 2023 г.

1 Введение

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

Это практическое руководство научит вас делать следующее:

• Восстановление базы данных

Подробное описание этого действия см. в этом видео:

2 Предварительные требования

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

• Наличие достаточных прав на СУБД (подробности см. в разделе «Разрешения» ниже)
• Убедитесь, что выполняются следующие пункты:
  • База данных Mendix поддерживается с использованием планов обслуживания, как описано в Как настроить планы обслуживания Mendix SQL
  • Для базы данных Mendix существует допустимый файл резервной копии базы данных
  • Для базы данных Mendix существуют допустимые файлы журнала транзакций

3 Разрешения

Если восстанавливаемая база данных не существует, пользователь должен иметь разрешения CREATE DATABASE , чтобы иметь возможность выполнить RESTORE . Если база данных существует, разрешения RESTORE по умолчанию предоставляются членам фиксированных серверных ролей sysadmin и dbcreator и владельцу базы данных ( дбо ).

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

4 Восстановление базы данных

Чтобы восстановить базу данных, выполните следующие действия:

1. После подключения к соответствующему экземпляру ядра базы данных Microsoft SQL Server щелкните имя сервера, чтобы развернуть дерево серверов в обозревателе объектов .

2. Расширить Базы данных . В зависимости от базы данных выберите пользовательскую базу данных или разверните Системные базы данных и выберите системную базу данных.

3. Щелкните правой кнопкой мыши базу данных, выберите Задачи > Восстановить > Database , после чего откроется диалоговое окно Restore Database .

4. В разделе Источник на странице Общие параметры укажите источник и расположение наборов резервных копий для восстановления, выбрав Устройство > Добавить , а затем найдите файл резервной копии:

5. В разделе Destination страницы General Settings база данных 9Поле 0040 автоматически заполняется именем восстанавливаемой базы данных. Чтобы изменить имя базы данных, введите новое имя в это поле.

6. В разделе План восстановления на странице Общие настройки оставьте значение по умолчанию До последней резервной копии или щелкните Временная шкала , чтобы открыть диалоговое окно Временная шкала резервного копирования , в котором можно вручную выбрать момент времени чтобы остановить действие восстановления.

7. В наборах резервных копий для восстановления сетки выберите резервные копии для восстановления. В этой сетке отображаются резервные копии, доступные для указанного местоположения. По умолчанию предлагается план восстановления. Чтобы переопределить предложенный план восстановления, измените выбор в сетке. Резервные копии, которые зависят от восстановления более ранней резервной копии, автоматически отменяются при отмене выбора более ранней резервной копии.

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

8. Чтобы просмотреть или выбрать дополнительные параметры, на панели Параметры восстановления на странице Параметры можно выбрать любой из следующих параметров, если он подходит для вашей ситуации:

   • С опциями (не требуется):
     • Перезаписать существующую базу данных ( WITH REPLACE )
     • Сохранить настройки репликации ( WITH KEEP_REPLICATION )
     • Ограничить доступ к восстановленной базе данных ( С RESTRICTED_USER )
   • Выберите параметр для поля Состояние восстановления , которое определяет состояние базы данных после операции восстановления:
     • ВОССТАНОВЛЕНИЕ С ВОССТАНОВЛЕНИЕМ — это поведение по умолчанию, которое оставляет базу данных готовой к использованию путем отката незафиксированных транзакций.
       • Дополнительные журналы транзакций не могут быть восстановлены
       • Выберите этот вариант, если вы сейчас восстанавливаете все необходимые резервные копии
     • RESTORE WITH NORECOVERY оставляет базу данных неработоспособной и не выполняет откат незафиксированных транзакций.
       • Можно восстановить дополнительные журналы транзакций
       • Базу данных нельзя использовать, пока она не будет восстановлена ​​
     • RESTORE WITH STANDBY оставляет базу данных в режиме только для чтения
       • Отменяет незафиксированные транзакции, но сохраняет действия отмены в резервном файле, чтобы можно было отменить последствия восстановления
   • Сделайте резервную копию хвостового журнала перед выбором восстановления, если это необходимо для выбранного вами момента времени.
     • Вам не нужно изменять этот параметр, но вы можете сделать резервную копию конца журнала, даже если это не требуется
   • Операции восстановления могут завершиться ошибкой, если есть активные подключения к базе данных
     • Установите флажок Закрыть существующие соединения , чтобы убедиться, что все активные соединения между Management Studio и базой данных закрыты (этот флажок переводит базу данных в однопользовательский режим перед выполнением операций восстановления и переводит базу данных в многопользовательский режим по завершении )
   • Выберите Запрашивать перед восстановлением каждой резервной копии , если вы хотите получать запрос между каждой операцией восстановления
     • Обычно в этом нет необходимости, если только база данных не велика и вы не хотите отслеживать состояние операции восстановления

9. Нажмите OK .

5 Подробнее

• Устранение неполадок SQL Server
• Как настроить пользователя SQL Server
• Как настроить новую базу данных SQL Server
• Как настроить планы обслуживания Mendix SQL
• Как настроить контрольный список безопасности для локальной установки

Обратная связь

Была ли эта страница полезной?

Рад слышать это! Благодарю за ваш ответ.

Жаль это слышать. Пожалуйста, расскажите нам, как мы можем улучшить.

Восстановление резервных копий SQL

Восстановление резервных копий SQL

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

При восстановлении резервных копий SQL учитывайте следующее:

•

Unitrends не поддерживает выборочное восстановление записей базы данных Microsoft SQL Server.

•

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

•

При восстановлении в альтернативное расположение можно восстановить только полные резервные копии. Назад: Название программ пк: Как описать в резюме навыки работы с компьютером Вперед: Зависает компьютер после включения: Компьютер зависает при включении — через несколько минут, намертво, что делать