Восстановление SQL server после сбоя

В процессе работы 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