Как восстановить ldf файл sql

Обновлено: 06.07.2024

Иногда приходится столкнуться с ситуацией, когда плоды многолетней работы, находящиеся в SQL-базе данных оказываются под угрозой. Эта статья о том, как не допустить потерю данных, а в случае, если это всё-таки произошло, как восстановить данные из того, что осталось от некогда нормальной базы.

Итак, приступим. Ситуация следующая: имеется сервер с запущенной на нем 1С+SQL. Во время работы SQL базы рубанули питание. Результат плачевный: база находится в состоянии suspect, и когда 1с пытается к ней зацепиться, выдается ошибка, что мол соединиться невозможно т.к. база помечена suspect for recovery. Этот режим в принципе означает, что MSSQL Server попытается восстановить базу своими средствами. Я не стал ни чего трогать и оставил все на ночь, в надежде, что к утру база восстановится, но и утром было то же самое, и к базе, стало быть, ни как не подобраться. Backup по закону подлости имеется, но он трехдневной давности, плюс имеется куча документов которые проводятся лишь по базе, а по бумажным документам их нет, т.е. возможности вручную восстановить документы нет. Потратив кучу сил, и нервов, (которые, как известно не восстанавливаются :)), я пришел к следующей последовательности действий, необходимых для восстановления базы.

1) Основной принцип поначалу – не навреди. Глушим SQL server и копируем *.mdf и *.ldf файлы от базы в сторону.
2) В принципе, бывает, что состояние suspect возникает из-за того, что поменялись пути к файлам с базой (например, добавился новый диск в системе, который потом убрали, переименовали папку с базой и т.д.). Затем, конечно же, пути восстановили, но база все равно остается помеченной как suspect. Вот что мы делаем:
3) Запускаем SQL Server.
4) Пробуем подключить базу через Enterprise Manager:
Правой кнопкой по Databases, в появившемся меню выбираем All tasks->Attach database, затем в появившемся диалогов окне выбираем файл с базой (*.mdf) и устанавливаем необходимые параметры.
5) или через Query Analyser примерно такой командой:
a. sp_attach_db @dbname = 'DemoXMB',
b. @filename1 = 'E:\Data\DemoXMB_Data.MDF',
c. @filename2 = 'E:\Data\DemoXMB_Log.LDF'
6) Пути к базе, естественно нужно заменить на свои. Если база подключилась, то, можно сказать, отделались легким испугом, если же нет, то продолжим.
7) Если log-файл не поврежден (*.ldf), а поврежден *.mdf (например, при подключении базы sql ругается на ошибки в mdf-файле), и режим сохранения backup'а стоит full, то восстанавливаем базу без восстановления лога транзакций, почти 100%, что все мучения на этом могут закончиться.
8) Если же наоборот, поврежден ldf-файл, но остался *.mdf файл, при подключении база ругается на отсутствие/повреждение лога транзакций. В этом случае можно воспользоваться ХП "sp_attach_single_file_db"

Например:
use master
EXEC sp_attach_single_file_db @dbname = 'DemoXMB',
@physname = 'c:\mssql7\data\DemoXMB_Dat.mdf'

При выполнении этих команд, создастся файл DemoXMB_Log.ldf в том же каталоге где и база, размером 1MB и авторасширением.
Если есть *.MDF и *.LDF-файлы, или данные хранятся более чем в одном физическом файле (общее количество подключаемых физических файлов не должно превышать 16-ти), то следует использовать ХП "sp_attach_db"

Например:
use master
EXEC sp_attach_db @dbname = 'DemoXMB',
@filename1 = 'c:\mssql7\data\DemoXMB_Dat.mdf',
@filename1 = 'c:\mssql7\data\DemoXMB_Log.ldf'

Для подключения более 16-ти физических файлов к БД следует использовать команду:
CREATE DATABASE FOR ATTACH

Однако если ничего не помогло, оказались поврежденными оба файла и база всё еще в состоянии suspect, то можно попробовать сбросить состояние базы следующей последовательностью: (перед использованием этой ХП необходимо разрешить прямое изменение системных таблиц)
use master
go
Разрешаем прямое изменение системных таблиц:
sp_configure 'allow updates',1
go
reconfigure with override
go
Для сброса признака suspect выполняем в БД master ХП sp_resetstatus:
sp_resetstatus 'DataBaseName'
go
А теперь запретим прямое изменение системных таблиц:
sp_configure 'allow updates',0
go
reconfigure with override
go

В принципе, когда я выполнил все эти шаги, статус suspect сбросился, НО! при попытке выполнить какие-либо действия SQL начинала ругаться, что база все еще в состоянии suspect. И тогда я сделал так:

Из QA выполняем скрипт:
Use master
go
sp_configure 'allow updates', 1
reconfigure with override
go

Там же выполняем :
update sysdatabases set status= 32768 where name = '<db_name>'

Перезапускаем SQL Server. В принципе база должна быть видна (в emergency mode).

Из QA выполняем:
USE '<db_name>'
GO
sp_dboption '<db_name>', 'single_user', 'true'
go
DBCC CHECKDB('<db_name>', REPAIR_ALLOW_DATA_LOSS)
go

Если все в порядке, то:
sp_dboption '<db_name>', 'single_user', 'false'
go
Use master
go
sp_configure 'allow updates', 0
go

После этого стало возможно просмотреть таблицы базы из SQL, а вот работать с ней было невозможно. Теперь необходимо при помощи Data Transformation Services экспортировать данные в новую базу. После этого проводим восстановление/тестирование базы средствами 1С. Внимание! Многие не обращают внимания на этот очень важный пункт. В результате, вы можете в один прекрасный момент обнаружить, что база восстановлена, мягко говоря, не совсем корректно. Т.е. в документе вместо номенклатуры будет стоять нечто вроде 10122/<Объект не найден>, это та проблема, которая возникла у меня, вариантов же может быть уйма. Поэтому лучше потерять время, но проверить базу средствами 1С.

Но! Здесь следует сделать паузу. Статья не была бы полной, если бы я не описал методы для предотвращения подобных проблем. Итак, самый простой и надежный способ: архивация, архивация и еще раз архивация. В Enterprise Manager заходим в меню Tools->Wizards->Management->Backup Wizard и настраиваем все необходимые параметры. В результате, у меня полный сброс SQL базы на диск происходит ночью, а затем каждые 15 минут backup изменений, внесенных в базу. В принципе, если бы у меня был такой backup, я бы за пару минут сделал откат назад, и продолжил бы попивать Coca-Cola :).

Теоретически.

Можно попробовать просто приатачить mdf + ldf файл. Если база подцепится (я не знаю, что может помещать), весь непримененный (тот, что после последнего checkpoint-а) лог должен накатиться на базу, а с учетом того, что checkpoint LSN храниться в самой базе, то проблем возникнуть не должно.

либо я вас не понимаю, либо вы меня.

предположим у нас есть база на момент времени t1, mdf и ldf "согласованны", "грязных" страниц нет, checkpoint lsn = последнему lsn в логе.

мы производим некое количество изменений, коммитем их которые, они попадают в лог, но из кеша на диск в mdf не сбрасываются, и того у нас получается mdf на момент t1 и ldf на момент времени t2.

в этот момент мы "жестко" ребутаем сервис, sql server по логу повторно накатит все изменения в mdf и принудительно сделает checkpoint

mdf файл да, старый, но бакап то можно сделать только сейчас, вот и восстановить можно будет на момент "сейчас".

То есть у сиквела маловато возможностей, не предусмотрены операции, которые теоретически можно сделать и даже совсем несложно технически.

Разве что попробовать присоединить файл с включённым аттрибутом read-only, может сиквел не поменяет ему всякие LSN-ы, и сделать бакап.

-----------------------------------
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'db8'. CREATE DATABASE is aborted.
Msg 5125, Level 24, State 1, Line 1
File 'Z:\Anna\_backups\hp\db8\db8.mdf' appears to have been truncated by the operating system. Expected size is 3328 KB but actual size is 2304 KB.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'db8', an error occurred at log record ID (30:122:54). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

не могу сейчас объяснить, на какой момент он требует .mdf,
но файл не обрезан.
опыт был проведен 2 раза, файл копируется нормально.
для проверки в третий раз просто пробую сразу же приаттачить этот .mdf без .ldf,
и все с ним ok.

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'db8'. CREATE DATABASE is aborted.
Msg 5125, Level 24, State 1, Line 1
File 'Z:\Anna\_backups\hp\db8\db8.mdf' appears to have been truncated by the operating system. Expected size is 6144 KB but actual size is 5120 KB.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'db8', an error occurred at log record ID (30:123:186). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

ну не знаю я, почему он на мегабайт больше ждет, нету идей, завтра еще подумаю,
а может кто-то знает и поделится соображениями.

1. Создал базу с полной моделью восстановления.
2. Сделал полный бекап (до этого база по факту в simple )
3. Сделал detach.
4. Скопировал mdf
5. Сделал attach
6. Внес изменения в данные.
7. Сделал checkpoint
8. Сделал detach.
9. Подменил mdf на скопированную в 4-том пункте
10. Сделал attach

база подцепилась, но изменений пункта 6 нет, в checkpoint lsn в странице 1:9 храниться lsn завершения последнего checkpoint-а из лога.

здравствуйте. у меня проблема аналогичная что и у топикстартера. а именно шифровальщик покушал MDF а последний актуальный бекап 3 недельной давности. но есть нетронутый шифровальщиком LDF

сервер мускула не поднимается, вероятно пожрано что то из системного
не могли бы вы подробнее описать вами вышеуказанный порядок действий на новом экземпляре сервера?

В процессе работы Microsoft SQL Server 2005/2008/2012/2014/2017 из ресурсов в основном полагается на память и жёсткий диск. При внештатной ситуации со службой сервера, данные в памяти могут быть потеряны. То же самое касается и самих баз - при наличии проблемы чтении-записи на диск, база может быть отмечена как подозрительная (suspected). Чаще всего слетает индекс (минимальный урон) или одна последняя транзакция, которая не записана на диск,- делается roll forward при возможности или roll back при невозможности зафиксировать(применить) транзакцию (средний урон, в зависимости от объёма транзакции). В случае с потерянными данными в индексе, есть возможность их восстановить методом реиндексации из информации соответствующей таблицы, индекс которой нарушен.

Содержание

Причины

Причина нарушения целостности данных базы (обычно ошибка сопровождается аббревиатурой CRC) является отказ в системе чтения-записи жёсткого диска (I/O).
К примеру:

Битые сектора жёсткого диска, с которых нельзя считать данные;
Потеря питания жёсткого диска - скачёк напряжения или, наоборот, полная его потеря;
Вмешательство внешних факторов - вирус, торрент-клиент, который занимает 100% ресурсов диска;
Другие ресурсоёмкие задачи, которые мешают свободному функционированию важной службы Microsoft SQL Server: виртуальные машины, рендеринг/конвертирование видео, запись потока с видеокамер.

Системные базы

Возвращение пользовательской базы к нормальному состоянию описано в соответствующей статье Исправление базы данных.
А что делать, если база системная? Системными базами являются базы, которые создаются с установкой Microsoft SQL Server и содержат служебные данные: master, model, msdb, tempdb и, при наличии репликации,- distribution. Выход из строя одной из системных баз влечёт за собой отказ в полноценной работе службы или её запуске.
База master не переводится в режим SINGLE_USER/MULTI_USER, что автоматически не позволит её восстановить при сбое.
База tempdb пересоздаётся при каждой перезагрузке службы.
База model может быть восстановлена только запросами из командной строки OSQL/SQLCMD.
Базы msdb и distribution могут быть восстановлены как и пользовательские.

Восстановление базы master

При установке Microsoft SQL Server в папке C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\Binn\Templates создаются файловые шаблоны баз. Данные шаблоны являются минимально настроенными и позволяют частично или полностью восстановить работоспособность сервера в полевых условиях без необходимости долгого удаления и установки сервера в целом. Здесь и далее [instance] = наименование установленной инстанции в корневой папке Microsoft SQL Server, например MSSQL11.MSSQLSERVER.

Процедура восстановления базы master:

Заменить базу master, заменив файлы master.mdf и mastlog.ldf из ранее указанной папки Templates.
Запустить командную строку cmd с правами администратора (далее Командная строка №1).
Перейти в "C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\Binn\".
Запустить sqlservr.exe с командным параметром -m. Данный параметр заставит сервер запуститься в монопольном режиме.
Поcле запуска будут выполняться скрипты обновления базы и настройки сервера.
При появлении следующей записи необходимо приступать к корректировке реальных путей к файлам

Для корректировки сервер должен быть запущен с параметрами -m -t3608. Параметр запрещает автоматический запуск SQL Server и восстановление любых баз данных, кроме базы данных master. Если инициируются действия, для которых требуется tempdb, то база model восстанавливается, и создается tempdb. Другие базы данных будут запущены и восстановлены при открытии. Параметр может быть использован и для перемещения системных баз данных. Внимание: Данный параметр должен быть использован только при условии выполнения всех предыдущих пунктов!
После старта в режиме "изоляции", производится корректировка путей и создание пользователя. Для этого необходимо запустить новый экземпляр командной строки с правами администратора (далее Командная строка №2), подключиться к серверу с помощью команды SQLCMD -E и выполнить следующие скрипты одновременно или по очереди, предварительно подставив свои значения:

Переключиться в Командную строку №1 и опять запустить сервер sqlservr.exe. Будет выполнен набор служебных скриптов, которые настроят базу master.
После запуска сервера можно выполнять attach пользовательской базы Microinvest, вне зависимости от того была база в репликации или нет.
При необходимости можно установить имя сервера запросом в Management Studio, после чего выполнить перезагрузку службы

Восстановление базы msdb

Заменить базу msdb, заменив файлы MSDBData.mdf и MSDBLog.ldf из ранее указанной папки Templates.
Запустить командную строку cmd с правами администратора.
Перейти в "C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\Binn\".
Запустить sqlservr.exe с командным параметром -m. Данный параметр заставит сервер запуститься в монопольном режиме.
Поcле запуска будут выполняться скрипты обновления базы и настройки сервера.
После этого понадобится сделать Attach для всех баз, которые были на сервере ранее

Восстановление базы model

Заменить базу model, заменив файлы model.mdf и modellog.ldf из ранее указанной папки Templates.
Запустить командную строку cmd с правами администратора.
Перейти в "C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\Binn\".
Запустить sqlservr.exe с командным параметром -m. Данный параметр заставит сервер запуститься в монопольном режиме.
Поcле запуска будут выполняться скрипты обновления базы и настройки сервера.
После этого понадобится сделать Attach для всех баз, которые были на сервере ранее

Предотвращение проблем

В предотвращении потери времени на восстановление работы остановившегося сервера рекомендуется заранее предпринять следующие действия:

Выполнять плановую поддержку SQL серверов
Установить источник бесперебойного питания и/или стабилизатор напряжения.
Создать планы обслуживания баз: Server - Management - Maintenance plans (при наличии SQL версии Standard и выше).
Выполнять периодически резервные копии системных баз как bak или файловая копия.

Выводы

Данная процедура восстановления базы master занимает на разных серверах от 3 до 5 минут. Удаление и установка нового сервера займёт многократно большее времени. При нежелании или отсутствии времени возиться с восстановлением сервера вы можете оплатить удаленное подключение наших специалистов Microinvest, которые всё сделают за вас.
Восстановление базы не потребуется, если у вас есть все факторы, чтобы этого избежать, однако если всё же пришлось восстановить и запустить сервер по вышеописанной технологии, примите меры на будущее. Также важно знать, что кодовая страница (collation) стандартной базы master устанавливает для целого сервера SQL_Latin1_General_CP1_CI_AI, что отличается от рекомендуемой Microinvest Cyrillic_General_CI_AS

В этом разделе описывается восстановление файлов и файловых групп в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.

В этом разделе

Перед началом работы

Восстановление файлов и файловых групп с помощью следующих средств

Перед началом

Ограничения

Системный администратор, восстанавливающий файлы и файловые группы, должен быть единственным лицом, использующим восстанавливаемую базу данных в данный момент.

Инструкция RESTORE недопустима в явной или неявной транзакции.

При использовании простой модели восстановления файл должен принадлежать к файловой группе, находящейся в режиме только для чтения.

Перед началом восстановления файлов по модели полного восстановления или модели восстановления с неполным протоколированием необходимо выполнить резервное копирование активного журнала транзакций (который называется заключительным фрагментом журнала). Дополнительные сведения см. в статье Создание резервной копии журнала транзакций (SQL Server)).

Чтобы восстановить зашифрованную базу данных, необходимо иметь доступ к сертификату или асимметричному ключу, который использовался для шифрования базы данных. Без сертификата или асимметричного ключа восстановить базу данных нельзя. Поэтому сертификат, используемый для шифрования ключа шифрования базы данных, должен храниться в течение всего времени, пока есть необходимость в резервной копии. Дополнительные сведения см. в статье SQL Server Certificates and Asymmetric Keys.

безопасность

Permissions

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

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

Использование среды SQL Server Management Studio

Восстановление файлов и файловых групп

После подключения к соответствующему экземпляру компонента Компонент SQL Server Database Engineв обозревателе объектов разверните дерево сервера, щелкнув имя сервера.

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

Щелкните правой кнопкой мыши базу данных, укажите на пункт Задачи и щелкните Восстановить.

Щелкните Файлы и файловые группы, после чего откроется диалоговое окно Восстановление файлов и файловых групп .

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

Чтобы указать источник и расположение восстанавливаемых резервных наборов данных, выберите один из следующих вариантов.

Из базы данных

Введите имя базы данных в списке. Данный список содержит только базы данных, резервное копирование которых было выполнено в соответствии с журналом резервного копирования msdb .

С устройства

После добавления нужных устройств в списке Носитель резервной копии нажмите кнопку ОК для возвращения на страницу Общие .

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

Для просмотра или выбора дополнительных параметров нажмите кнопку Параметры на панели Выбор страницы.

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

Восстановление файловой группы
Указывает, что восстанавливается вся файловая группа.

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

Выбор этой функции равнозначен использованию параметра REPLACE в инструкции RESTORE языка Transact-SQL .

Выдавать приглашение перед восстановлением каждой резервной копии
Запрашивает подтверждение перед восстановлением из копии каждого резервного набора данных.

Этот параметр особенно полезен, когда для использования различных наборов носителей необходимо менять ленты (например, если на сервере существует одно ленточное устройство).

Ограничить доступ к восстановленной базе данных
Доступ к восстановленной базе данных будет только у пользователей db_owner, dbcreator или sysadmin.

Выбор этой функции равнозначен использованию параметра RESTRICTED_USER в инструкции RESTORE языка Transact-SQL .

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

В качестве значения параметра Состояние восстановления укажите состояние базы данных после операции восстановления.

Оставить базу данных готовой к использованию, выполнив откат незафиксированных транзакций. Невозможно восстановить дополнительные журналы транзакций. (RESTORE WITH RECOVERY)
Восстанавливает базу данных. Это поведение по умолчанию. Выберите этот параметр для восстановления всех необходимых резервных копий. Этот параметр равнозначен указанию предложения WITH RECOVERY в инструкции RESTORE языка Transact-SQL .

Оставить базу данных в нерабочем состоянии и не выполнять откат незавершенных транзакций. Можно восстановить дополнительные журналы транзакций. (RESTORE WITH NORECOVERY)
Оставляет базу данных в состоянии восстановления. Чтобы восстановить базу данных, необходимо выполнить еще одно восстановление с использованием предыдущего параметра RESTORE WITH RECOVERY (см. выше). Этот параметр равнозначен указанию предложения WITH NORECOVERY в инструкции RESTORE языка Transact-SQL .

Если выбран этот параметр, то параметр Сохранить настройки репликации недоступен.

Оставить базу данных в режиме «только для чтения». Выполнить откат незавершенных транзакций с сохранением операции отката в файле, чтобы можно было отменить операцию восстановления. (RESTORE WITH STANDBY)
Оставляет базу данных в резервном состоянии. Этот параметр равнозначен указанию предложения WITH STANDBY в инструкции RESTORE языка Transact-SQL .

При выборе этого параметра необходимо указать резервный файл.

Файл отмены отката
Укажите имя резервного файла в текстовом поле Файл отмены отката . Этот параметр необходим, если нужно оставить базу данных в режиме «только для чтения» (RESTORE WITH STANDBY).

Использование Transact-SQL

Восстановление файлов и файловых групп

Выполните инструкцию RESTORE DATABASE для восстановления резервной копии файлов и файловых групп, указав следующее:

Имя базы данных для восстановления.

Устройство резервного копирования, откуда будет восстановлена полная резервная копия.

предложение FILE для каждого восстанавливаемого файла;

предложение FILEGROUP для каждой восстанавливаемой файловой группы;

Предложение NORECOVERY. (если файлы не изменялись со времени создания резервной копии, укажите предложение RECOVERY).

Если файлы были изменены после создания резервной копии, выполните инструкцию RESTORE LOG для применения резервной копии журнала транзакций, указав следующее:

Имя базы данных, к которой будет применен журнал транзакций.

Устройство резервного копирования, с которого будет восстановлена резервная копия журнала транзакций.

Предложение NORECOVERY, если существует другая резервная копия журналов транзакций для применения после текущего; в противном случае укажите предложение RECOVERY.

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

Примеры (Transact-SQL)

В этом примере восстанавливаются файлы и файловые группы базы данных MyDatabase . При восстановлении базы данных до текущего момента будут применены два журнала транзакций.

Читайте также: