В данном материале мы рассмотрим возможность перемещения файлов базы данных Microsoft SQL Server на новый жесткий диск с помощью инструкции ALTER DATABASE…MODIFY FILE.

Метод, который мы будем рассматривать в данной статье, подразумевает перемещение файлов базы данных в пределах одного экземпляра SQL Server, другими словами одного сервера, т.е. мы не будем перемещать БД на другой сервер, используя например, инструкции по отсоединению и присоединению баз данных.

Необходимость перемещения файлов базы данных на новый диск в пределах одного экземпляра SQL Server может возникнуть, например, тогда, когда у Вас закончилось место на диске (при этом сжатие базы данных Вам не помогает или Вы не хотите его делать) или Вы, просто захотели переместить файлы БД на более быстрый жесткий диск, который Вы уже подключили.

В данных случаях самым простым и рекомендуемым способом является использование инструкции по изменению свойств базы данных ALTER DATABASE…MODIFY FILE, при этом, как Вы понимаете, переустанавливать Microsoft SQL Server не нужно.

Перемещение базы данных MS SQL Server на новый жесткий диск

Для того чтобы переместить файлы базы данных необходимо выполнить несколько простых шагов.

Примечание! В качестве SQL сервера у меня выступает Microsoft SQL Server 2016 Express. Для примера я буду переносить файлы базы данных TestBase на новый локальный диск D.

Шаг 1

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

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

  
  USE master;
  GO
  SELECT db_name(dbid) AS [DatabaseName],
             loginame AS [UserName]
  FROM sys.sysprocesses
  WHERE db_name(dbid) = N'TestBase'

Где, TestBase – это название базы данных (как и во всех представленных ниже SQL запросах).

Если запрос не вернул данных, значит можно приступать к переносу файлов БД, если же, кто-то в ней работает, то необходимо попросить пользователя выйти или в случае необходимости закрыть все процессы самому (например, с помощью инструкции kill или монитора активности SSMS).

Шаг 2

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

  
  USE master;
  GO
  SELECT Name, 
             Physical_name AS CurrentLocation, 
             State_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(N'TestBase')

Где, Name – это и есть логическое имя файла, оно нам потребуется для переноса файла в новое место.

Шаг 3

Далее мы переводим базу данных в режим «Вне сети». Это делается следующим образом:

  
  ALTER DATABASE TestBase SET OFFLINE;

Шаг 4

После вышеперечисленных действий осуществляем физическое перемещение файлов БД в новое место (в моем случае я перемещаю файлы в каталог D:\DataBase\).

Шаг 5

Затем изменяем свойства базы данных, а именно путь к файлам БД. Инструкцию ALTER DATABASE необходимо выполнить для каждого файла, который Вы переместили.

  
  ALTER DATABASE TestBase 
      MODIFY FILE ( NAME = TestBase, 
                    FILENAME = 'D:\DataBase\TestBase.mdf');
  GO
  ALTER DATABASE TestBase
      MODIFY FILE ( NAME = TestBase_log, 
                    FILENAME = 'D:\DataBase\TestBase_log.ldf');
  GO

Где, параметр NAME и есть логическое имя файла БД, а FILENAME — это новое расположение файла.

Шаг 6

Переводим базу данных обратно в нормальный режим.

  
  ALTER DATABASE TestBase SET ONLINE;

Шаг 7

Проверяем новое расположение файлов, для этого еще раз выполним запрос, который мы использовали на шаге 2.

  
  SELECT Name, 
             Physical_name AS CurrentLocation, 
             State_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(N'TestBase')

Как видим, у нас отобразилось новое физическое расположение файлов БД, т.е. мы успешно переместили файлы базы данных на новый диск (рекомендую также проверить работу самой БД).

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

На этом у меня все, надеюсь, материал был Вам полезен, пока!