Как отключить базу MS SQL: OFFLINE, detach и остановка досту

Отключить базу MS SQL можно двумя основными способами: временно перевести ее в состояние OFFLINE или отсоединить от экземпляра SQL Server через detach. Если база должна просто перестать принимать подключения на время обслуживания, чаще подходит OFFLINE. Если нужно снять базу с сервера и потом подключить файлы MDF/LDF в другом месте, используют detach

Главная ошибка в этом запросе — путать отключение базы с удалением. OFFLINE и detach не удаляют файлы базы, но могут прервать работу приложений. Поэтому перед любым действием стоит сделать резервную копию, предупредить пользователей и понять, кто сейчас подключен к базе

Содержание

Когда выбирать OFFLINE
Когда выбирать detach
Как отключить базу через SSMS
Как посмотреть активные подключения
Мини-практика
Частые ошибки
Что почитать дальше по SQL

Когда выбирать OFFLINE

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

Пример команды:

ALTER DATABASE ShopDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;

ROLLBACK IMMEDIATE принудительно завершает активные транзакции и отключает пользователей. Это удобно, когда база «не отпускается», но опасно для рабочей системы без подготовки. Если в базе идут важные операции, лучше сначала остановить приложение и дождаться завершения запросов

Вернуть базу обратно можно так:

ALTER DATABASE ShopDemo SET ONLINE;

После возврата проверьте состояние:

SELECT name, state_desc
FROM sys.databases
WHERE name = 'ShopDemo';

Ожидаемый результат:

Когда выбирать detach

detach отсоединяет базу от экземпляра SQL Server. Файлы базы остаются на диске, но сервер больше не показывает ее в списке баз. Такой вариант используют для переноса файлов, архивирования или ручной переустановки окружения

Перед detach полезно перевести базу в однопользовательский режим:

ALTER DATABASE ShopDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'ShopDemo';

Подключить базу обратно можно через SSMS: Databases -> Attach, выбрать MDF-файл и проверить путь к LDF. Через SQL это выглядит так:

CREATE DATABASE ShopDemo
ON (FILENAME = 'D:\MSSQL\Data\ShopDemo.mdf'),
   (FILENAME = 'D:\MSSQL\Data\ShopDemo_log.ldf')
FOR ATTACH;

Если LDF-файл потерян, не нужно первым делом искать команду «создать лог заново». Сначала проверьте бэкап и состояние MDF, потому что восстановление без журнала может закончиться поврежденной базой

Как отключить базу через SSMS

В SSMS откройте Object Explorer, найдите базу, нажмите правой кнопкой и выберите Tasks. Для временного отключения удобнее использовать New Query и команду ALTER DATABASE, потому что там явно видно, что вы делаете. Для отсоединения используйте Tasks -> Detach, но внимательно смотрите флажок Drop Connections

Если база занята, SSMS может показать ошибку, что база используется другими процессами. Это не значит, что команда неправильная. Это значит, что к базе подключены пользователи, сервис, 1С, сайт или фоновая задача

Как посмотреть активные подключения

Перед отключением можно посмотреть сессии:

SELECT
    DB_NAME(database_id) AS database_name,
    session_id,
    login_name,
    host_name,
    program_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('ShopDemo');

Если видите подключение от приложения, лучше сначала остановить само приложение. Принудительное отключение через ROLLBACK IMMEDIATE оставьте для случаев, когда вы понимаете последствия

Мини-практика

Создайте тестовую базу, переведите ее в OFFLINE, верните в ONLINE и проверьте state_desc в sys.databases. На учебной базе это безопасно и хорошо показывает механику: база не удаляется, но становится недоступной для запросов

CREATE DATABASE DemoOffline;
GO

ALTER DATABASE DemoOffline SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

SELECT name, state_desc
FROM sys.databases
WHERE name = 'DemoOffline';
GO

ALTER DATABASE DemoOffline SET ONLINE;
GO

Частые ошибки

Отключают рабочую базу без бэкапа и без окна обслуживания
Используют detach там, где достаточно OFFLINE
Не проверяют активные подключения и потом удивляются ошибке «database is in use»
Путают отключение базы с остановкой всего SQL Server
Не записывают путь к MDF/LDF перед detach