Как восстановить базу sql

Восстановление служебных баз SQL сервера

В процессе работы Microsoft SQL Server 2005/2008/2012/2014/2017/2019 из ресурсов в основном полагается на память и жёсткий диск. При внештатной ситуации со службой сервера, данные в памяти могут быть потеряны. То же самое касается и самих баз — при наличии проблемы чтении-записи на диск, база может быть отмечена как подозрительная (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ле запуска будут выполняться скрипты обновления базы и настройки сервера.
• При появлении следующей записи необходимо приступать к корректировке реальных путей к файлам

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file. Diagnose and correct the operating system error, and retry the operation.

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

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\tempdb.mdf') ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\templog.ldf') ALTER DATABASE model MODIFY FILE (name = modeldev, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\model.mdf') ALTER DATABASE model MODIFY FILE (name = modellog, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\modellog.ldf') ALTER DATABASE msdb MODIFY FILE (name = MSDBData, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\MSDBData.mdf') ALTER DATABASE msdb MODIFY FILE (name = MSDBLog, filename = 'C:\Program Files\Microsoft SQL Server\[instance]\MSSQL\DATA\MSDBLog.ldf') GO ALTER LOGIN sa WITH PASSWORD = 'Сложный_Пароль'; GO ALTER LOGIN sa WITH DEFAULT_LANGUAGE=us_english; GO -- Активируем Mixed Mode для соединения с сервером. -- По умолчанию значение только Windows Authentication Mode, что не позволит использовать сервер по сети EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 GO -- Указываем имя ПК и пользователя для добавления пользователя как администратора сервера CREATE LOGIN [PCNAME\USERNAME] FROM Windows; GO -- Добавляем пользователя в группу администраторы. EXEC master..sp_addsrvrolemember @loginame = N'PCNAME\USERNAME', @rolename = N'sysadmin' GO -- Создаем учетную запись агента на сервере CREATE LOGIN [NT Service\SQLSERVERAGENT] FROM Windows; GO -- Добавляем пользователя NT Service\SQLSERVERAGENT в группу администраторы для того, чтобы агент мог подключиться к серверу EXEC master..sp_addsrvrolemember @loginame = N'NT Service\SQLSERVERAGENT', @rolename = N'sysadmin' GO

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

sp_addserver 'SERVERNAME', local -- Где SERVERNAME равен имени ПК

Если в процессе сервер ругается сообщением Msg 18461, Level 14, State 1, Server PCNAME, Line 1. Login failed for user ‘PCNAME\USERNAME’. Reason: Server is in single user mode. Only one administrator can connect at this time. , то добавьте параметр -t902 к старту сервера. Этот параметр предотвращает запуск скрипта обновления базы.

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

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

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

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

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

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

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

Выводы

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

Некоторые полезные ресурсы

• Документация по всем продуктам;
• Форум технической поддержки Microinvest, где Вы сможете быстро получить ответ на интересующий вопрос;
• Примеры автоматизации ресторанов и торговли на базе Microinvest в России и странах СНГ;
• График предстоящих обучений в России и странах СНГ;
• Программы для автоматизации ресторанов и кафе бесплатные полнофункциональные демо-версии продуктов Microinvest для ресторанного бизнеса;
• Программы для автоматизации торговли и склада бесплатные полнофункциональные демо-версии продуктов Microinvest для магазинов;
• Автоматизация торговли под Linux;
• www.microinvest.tv Видеоуроки, обучения;
• Корпоративный блог — интересные события в мире Microinvest;
• Все статьи Базы Знаний;
• Скачать офф-лайн версию Базы Знаний на свой компьютер.

SQL-Ex blog

Восстановление на момент времени в SQL Server

Добавил Sergey Moiseenko on Суббота, 13 мая. 2023

В какой-то момент вредоносная команда может быть выполнена на одной из ваших баз данных, и вам потребуется восстановить потерянные данные. Имеется несколько вариантов действий, которые могут восстановить потерянные данные, но какой из них сделает это лучшим образом? Одним из вариантов, которые предлагает SQL Server, является возможность восстановление ваших данных на заданный момент времени, для того чтобы восстановить вашу базу к состоянию, непосредственно предшествовавшему выполнению вредоносной команды. Итак, какие шаги следует предпринять для восстановления на момент времени?

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

Модель восстановления

Чтобы иметь возможность восстановления на момент времени, ваша база данных должна находиться в режиме модели полного восстановления (Full) или в режиме c неполным протоколированием (Bulk-Logged). Если ваша база использует простую модель восстановления (Simple), данная функциональность работать не будет. Чтобы установить вашу базу в режим восстановления Full или Bulk-Logged, можно воспользоваться GUI или окном запросов:

Команды T-SQL

• ALTER DATABASE имя_базы_данных SET RECOVERY BULK_LOGGED
• ALTER DATABASE имя_базы_данных SET RECOVERY FULL

SQL Server Management Studio

Чтобы установить модель восстановления для базы данных с помощью GUI, щелкните правой кнопкой на имени базы данных и выберите Properties (свойства).

Если база данных установлена в режим полного восстановления, у вас есть возможность восстановления на момент времени для всех резервных копий журнала транзакций. Для модели восстановления с неполным протоколированием, если у вас имеются какие-либо операции с неполным протоколированием в конкретном журнале транзакций, который вы пытаетесь использовать для восстановления на момент времени, процесс восстановления не удастся, а вы получите одну из приведенных ниже ошибок. Это связано с тем, что транзакции с неполным протоколированием таких операций, как create index и bulk insert, могут минимально журнализироваться и, поскольку не все данные этих операций присутствуют в журнале транзакций, восстановление на определенный момент времени невозможно.

Если попытаться использовать восстановление на момент времени и существуют операции с неполным протоколированием, вы получите такие сообщения об ошибках:

Msg 4341, Level 16, State 1, Line 4
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
(Эта резервная копия журнала содержит изменения массовой загрузки. Ее нельзя использовать для остановки в произвольный момент времени.)
Msg 4338, Level 16, State 1, Line 4
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
(Предложение STOPAT указывает слишком раннюю точку для восстановления этого набора резервных копий. Выберите другую точку остановки или используйте RESTORE DATABASE WITH RECOVERY для восстановления на текущую точку.)
Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
(RESTORE LOG завершается аварийно.)

Процесс восстановления

Для восстановления базы данных вы можете использовать окно запросов или административные инструменты SQL Server.

Вот команды для восстановления базы данных и одного журнала транзакций на Dec 19, 2022 11:26:05 AM. Первая полная резервная копия восстанавливается при использовании опции NORECOVERY, поэтому база данных остается в состоянии загрузки, и могут быть восстановлены дополнительные резервные копии. Вторая команда восстанавливает журнал транзакций на момент времени = ‘Dec 19, 2022 11:26:05 AM’. Кроме того, мы используем опцию RECOVERY, чтобы перевести базу данных обратно в рабочее состояние после восстановления журнала транзакций.

RESTORE DATABASE DBUtil FROM DISK = ‘C:\Backup\dbutil.bak’ WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = ‘C:\Backup\dbutil_log.trn’ WITH RECOVERY, STOPAT = ‘Dec 19, 2022 11:26:05 AM’

Если нам нужно восстановить дополнительные журналы транзакций, это можно сделать с использованием опции NORECOVERY для каждого из последующих журналов транзакций, а параметр должен использоваться только для последнего восстанавливаемого файла.

RESTORE DATABASE DBUtil FROM DISK = ‘C:\Backup\dbutil.bak’ WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = ‘C:\Backup\dbutil_log_1.trn’ WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = ‘C:\Backup\dbutil_log_2.trn’ WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = ‘C:\Backup\dbutil_log_3.trn’ WITH RECOVERY, STOPAT = ‘Dec 19, 2022 11:26:05 AM’

При использовании SQL Server Management Studio GUI выполните нижеприведенные действия. Сначала необходимо выбрать все файлы резервных копий, которые вы хотите восстановить, а затем использовать опцию «Timeline» для восстановления к конкретному моменту времени.

На этом экране вы можете выбрать момент времен для восстановления.

Это почти все, что нужно сделать для восстановления на заданный момент времени. Осталось только выяснить к какому моменту времен восстанавливать журнал транзакций. В некоторых обстоятельствах вы можете знать точное время, когда что-то пошло не так, но в других случаях это может быть вам неизвестно. Тут могут помочь инструменты чтения журналов транзакций. Эти инструменты позволят вам увидеть все транзакции и время, когда они произошли, так что вы сможете определить точное время для восстановления вашей базы данных.

1. Примеры резервного копирования базы данных SQL Server
2. Основы журнала транзакций в SQL Server
3. 5 типов резервных копий в SQL Server
4. Архитектура журнала транзакций SQL Server

Как восстановить базу sql

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

Примечание Имена баз данных должны точно совпадать с именами баз данных из исходного Microsoft SQL Server 2008 R2 Express Edition, WhatsUp, NetFlow, и NFArchive.

1. На целевом SQL Server запустите SQL Server Management Studio и выполните вход с помощью либо Windows Authentication, если у пользователя Windows есть права sysadmin, либо учетной записи SQL с правами sysadmin. Убедитесь, что в поле Имя сервера выбран подходящий экземпляр SQL Server.
2. Щелкните правой кнопкой мыши пункт Базы данных в дереве Проводника объектов слева, затем нажмите Восстановить базу данных. Открывается диалоговое окно Восстановить базу данных.
3. Выберите Из устройства, затем нажмите кнопку обзора (. ). Откроется диалоговое окно Укажите резервную копию.
4. Нажмите кнопку Добавить. Откроется диалоговое окно Выберите файл резервной копии.
5. Перейдите в расположение файла резервной копии базы данных ( .bak ), затем нажмите OK. Откроется диалоговое окно Укажите резервную копию с указанием пути файла .bak .
6. Нажмите кнопку ОК. Открывается диалоговое окно Восстановить базу данных.
7. В поле Целевая база данных введите WhatsUp .
8. В разделе Выберите резервные копии для восстановления диалогового окна нажмите, чтобы установить флажок Восстановить.
9. Нажмите кнопку ОК. Процесс восстановления завершится.
10. Повторите шаги 2 — 8 для резервных копий баз данных Netflow и NFArchive при наличии лицензии на подключаемый модуль Flow Monitor.

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

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

Для того чтобы разработать эффективный план резервного копирования в Microsoft SQL Server2000, Вам необходимо научиться восстанавливать данные из резервной копии. Восстановление может понадобиться в случае сбоя в работе оборудования или при необходимости создания на другом сервере абсолютно идентичной копии существующей базы данных.

Запомните, если Вы восстанавливаете базу, используя Simple Recovery Model, Вам нужно будет восстановить только последнюю полную копию. Если же Вы используете Full или Bulk Recovery Model , Вы должны восстановить полную копию, затем последнюю дифференциальную копию и все копии журналов транзакций. Изучим подробнее процессы восстановления.

Восстановление базы данных из полной копии.

Независимо от модели восстановления, первым шагом всегда является восстановление последней полной резервной копии. Для восстановления БД в Enterprise Manager, следует выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, после этого откроется диалоговое окно, показанное на Рисунке A.

Диалоговое окно Restore Database позволяет просматривать все последние резервные копии в хронологическом порядке. Там же Вы можете выбрать базу данных, которую нужно восстановить. На вкладке Options показанной на Рисунке B, Вы можете выбрать сделующие опции:

• Eject tapes after restoring each backup (выгружать ленту после каждого восстановления)
• Prompt befor restoring each backup (выдавать дополнительное предупреждение перед началом восстановления каждой копии)
• Force restore over existing database (осуществлять восстановление поверх существующей базы данных), эта опция эквивалентна Move в T-SQL.

В нижней части окна находятся три переключателя, которые позволяют определить состояние базы после восстановления копии:

• Leave Database Operational. No Additional Transaction Logs Can Be Restored.
  • Если Вы выбрали это значение, то после загрузки резервной копии будет инициирован процесс восстановления, что приведет к откату всех незавершенных транзакций. Станет невозможной загрузка дополнительных копий журнала транзакций. Пользователи получат возможность нормально работать с базой данных.
  • По окончании загрузки копии база данных будет оставаться временно недоступной. Будет необходимо загрузить дополнительные копии, после чего инициировать процесс восстановления.
  • База данных становится доступной только для чтения. Вы можете загрузить дополнительные резервные копии журнала транзакций. Эта опция используется для создания резервного сервера (Standby Server)

  Для восстановления базы данных и журналов транзакций осталось просто нажать кнопку OK.

  Восстановление базы данных с помощью T-SQL.

  Восстановление базы данных можно выполнить и с помощью T-SQL, который предлагает больше опций чем Enterprise Manager. Синтакс использования T-SQL команды следующий:

  RESTORE DATABASE < database_name | @database_name_var >
  [ FROM < backup_device >[ ,. n ] ]
  [ WITH
  [ RESTRICTED_USER ]
  [ [ , ] FILE = < file_number | @file_number > ]
  [ [ , ] PASSWORD= < password | @password_variable > ]
  [ [ , ] MEDIANAME= < media_name | @media_name_variable > ]
  [ [ , ] MEDIAPASSWORD= < mediapassword | @mediapassword_variable > ]
  [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
  [ ,. n ]
  [ [ , ] KEEP_REPLICATION ]
  [ [ , ] < NORECOVERY | RECOVERY | STANDBY =undo_file_name > ]
  [ [ , ] < NOREWIND | REWIND >]
  [ [ , ] < NOUNLOAD | UNLOAD >]
  [ [ , ] REPLACE ]
  [ [ , ] RESTART ]
  [ [ , ] STATS [ =percentage ] ]

  Для детального изучения каждой опции следует прочитать описание в SQL Server 2000 Books Online.

  На Рисунке C показано восстановление базы данных Pubs из полной копии с устройства резервного копирования.

  Восстановление базы данных из дифференциальной копии.

  Если Вы используете Full или Bulk Recovery Model, Вы должны выполнить сначала восстановление полной резервной копии, затем последней дифференциальной копии и всех журналов транзакций. Для выполнения восстановления базы данных, используя дифференциальную копию, в Enterprise Manager необходимо выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, выбрать восстановления полной и дифференциальной копии базы данных, а затем нажать OK. (исунок D)

  Синтаксис команды Restore для выполнения восстановления с использованием дифференциальных копий, показан на Рисунке Е.

  Восстановление журнала транзакций.

  Перед началом восстановления журнала транзакций, Вы должны восстановить полную и последнюю дифференциальную копию базы. Затем Вы можете восстанавливать журналы транзакций в соответсвующем порядке. Если Вы используете Enterprise Manager, нужно выделить базу данных, дважды щелкнуть по ней правой кнопкой мыши и выбрать в контекстном меню “All Tasks > Restore Database”, выбрать все нужные копии и, если есть необходимость, опцию Point in Time Restore (восстановление на определенный момент времени) (Рисунок F).

  Синтаксис команды Restore для восстановления журнала транзакций, показан на РисункеG.

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