Как перенести базу данных SQL Server на другой диск

Перенести базу данных SQL Server на другой диск можно несколькими способами: через резервную копию и восстановление, через detach и attach, либо через изменение путей файлов базы с остановкой доступа. Самый спокойный и понятный вариант для большинства случаев — сделать BACKUP, затем RESTORE с новыми путями файлов

Просто копировать файлы .mdf и .ldf, пока база работает, нельзя. SQL Server держит их открытыми, а данные в процессе работы меняются

Содержание

Способ через backup и restore
Способ через detach и attach
Как проверить текущие пути файлов базы
Что учесть перед переносом
Мини-практика
Частые ошибки
Что почитать дальше по SQL

Способ через backup и restore

Сначала создают резервную копию базы

BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backup\MyDatabase.bak'
WITH INIT;

Затем восстанавливают базу на новом месте. Перед восстановлением нужно узнать логические имена файлов

RESTORE FILELISTONLY
FROM DISK = 'D:\Backup\MyDatabase.bak';

После этого выполняют восстановление с указанием новых путей

RESTORE DATABASE MyDatabase_New
FROM DISK = 'D:\Backup\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'E:\SQLData\MyDatabase.mdf',
     MOVE 'MyDatabase_log' TO 'E:\SQLLogs\MyDatabase_log.ldf';

Логические имена MyDatabase и MyDatabase_log нужно брать из результата RESTORE FILELISTONLY, а не угадывать

Способ через detach и attach

При detach база отключается от SQL Server, после чего файлы можно перенести вручную и подключить снова. Этот способ проще выглядит, но требует остановки работы пользователей с базой

EXEC sp_detach_db 'MyDatabase';

После переноса файлов подключение выполняют так:

CREATE DATABASE MyDatabase
ON (FILENAME = 'E:\SQLData\MyDatabase.mdf'),
   (FILENAME = 'E:\SQLLogs\MyDatabase_log.ldf')
FOR ATTACH;

Перед detach обязательно сделайте резервную копию. Если что-то пойдет не так, backup будет последней нормальной точкой возврата

Как проверить текущие пути файлов базы

Перед переносом посмотрите, где сейчас лежат файлы

SELECT
    name,
    physical_name,
    type_desc
FROM sys.master_files
WHERE database_id = DB_ID('MyDatabase');

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

Что учесть перед переносом

Проверьте свободное место на новом диске, права учетной записи службы SQL Server на папки, расписание резервного копирования, задания SQL Agent и приложения, которые подключаются к базе. Сам перенос файлов не должен менять имя базы, но любые ошибки в доступе к файлам могут остановить запуск базы

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

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

На тестовой базе выполните проверку файлов, сделайте backup, восстановите базу с новым именем на другой диск или в другую папку и сравните количество таблиц

SELECT COUNT(*) AS tables_count
FROM MyDatabase_New.sys.tables;

Если база восстановилась и таблицы на месте, проверьте несколько ключевых таблиц через SELECT COUNT(*)

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

Первая ошибка — копировать .mdf и .ldf при работающей базе. Так можно получить поврежденную копию

Вторая ошибка — не проверить логические имена файлов перед RESTORE WITH MOVE. Физические имена файлов и логические имена в backup — разные вещи

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

Что почитать дальше по SQL

Если нужен общий маршрут по теме, откройте рубрику SQL. Для соседних задач пригодятся эти разборы: