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

В процессе работы Microsoft SQL Server 2005/2008/2012/2014/2017/2019 из ресурсов в основном полагается на память и жёсткий диск. При внештатной ситуации со службой сервера, данные в памяти могут быть потеряны.

То же самое касается и самих баз - при наличии проблемы чтении-записи на диск, база может быть отмечена как подозрительная (suspected). Чаще всего слетает индекс (минимальный урон) или одна последняя транзакция, которая не записана на диск,- делается roll forward при возможности или roll back при невозможности зафиксировать(применить) транзакцию (средний урон, в зависимости от объёма транзакции).

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

Содержание

1 Причины
2 Системные базы
3 Восстановление базы master
- 3.1 Процедура восстановления базы master:
4 Восстановление базы msdb
5 Восстановление базы model
6 Предотвращение проблем
7 Выводы
8 Некоторые полезные ресурсы

Причины

Причина нарушения целостности данных базы (обычно ошибка сопровождается аббревиатурой 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 = 'Сложный_Пароль';

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

-- Создаем учетную запись агента на сервере

-- Добавляем пользователя 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.

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 для всех баз, которые были на сервере ранее.

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

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

Выполнять плановую поддержку 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