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

Материал из Info

Перейти к: навигация, поиск

В процессе работы 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ле запуска будут выполняться скрипты обновления базы и настройки сервера.
  • При появлении следующей записи необходимо приступать к корректировке реальных путей к файлам
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.
  • Переключиться в Командную строку №1 и опять запустить сервер 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ле запуска будут выполняться скрипты обновления базы и настройки сервера.
  • После этого понадобится сделать Attach для всех баз, которые были на сервере ранее

Восстановление базы 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

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


Рассмотрите другие статьи в категории SQL