null

Перенос файлов баз данных MS SQL

18 September 2020 0 Maxim Zhukov

Введение в проблему

Порой возникает необходимость разнести или перенести файлы баз данных MS SQL Server, например, активные файлы и файловые группы на более быстрые дисковые подсистемы.

Проблемы в этом процессе нет, если понимать, что и как делать - об этом пойдёт речь

Я не изобретаю велосипед: в статье у microsoft https://docs.microsoft.com/ru-ru/sql/relational-databases/databases/move-database-files?view=sql-server-ver15 Move Database Files есть описания процесса, НО статье есть ошибка последовательности - после offline базы данных (пункт 1) проделать что-либо с такой бд не представляется возможным, в частности не переместить файл (остальные пункты)

При попытке действий ошибка будет сформулирована справедливо

Msg 942, Level 14, State 4, Line 4
Database 'MyUserDataBase' cannot be opened because it is offline.

Последовательность действий

Prerequirements

Без простоя базы данных и СУБД провести данную процедуру невозможно.

Время простоя будет складываться из

  1. Перевода базы данных в offline - до одной минуты.
  2. Копирование/перенос файлов в необходимую локацию средствами файловой системы - зависит от быстродействия дисков и объемов данных
  3. Перевода базы данных в online - до одной минуты.
  4. Возможный перезапуск СУБД  - до одной минуты.

Корректная последовательность действий по переносу файлов базы данных MS SQL

Перенос будет показан Transact-SQL на примере базы данных 

MyUserDataBase

Файла с

  • Logical Name "MyUserDataBase.Tenant#00069@01"
  • имеющий путь 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyUserDataBase.Tenant#00069@01.ndf'
  • и переносимого в 'H:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyUserDataBase.Tenant#00069@01.ndf'

В своём случае Вы можете заменить данные значения на необходимые Вам.

  1. Переписываем локацию файла 
    alter database [MyUserDataBase] MODIFY FILE ( NAME = "MyUserDataBase.Tenant#00069@01", FILENAME = 'H:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyUserDataBase.Tenant#00069@01.ndf' );

    В результате должны увидеть сообщение

    The file "MyUserDataBase.Tenant#00069@01" has been modified in the system catalog. The new path will be used the next time the database is started.

  2. Переводим базу данных в offline - начинается простой, см. Prerequirements, 

    alter database [MyUserDataBase] set OFFLINE;
  3. В случае трудностей на предыдущем пункте останавливаем СУБД.
  4. Вручную перемещаем файл средствами системных утилит, програм, средств файловой системы в новое расположение С СОХРАНЕНИЕМ ПРАВ ДОСТУПА НА ФАЙЛ 
    xcopy
'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyUserDataBase.Tenant#00069@01.ndf''
'H:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyUserDataBase.Tenant#00069@01.ndf'
 /O /X /E /H /K

  5. Переводим базу данных в  ONLINE, или запускаем СУБД

    alter database [MyUserDataBase] set ONLINE;
  6. Проверяем локацию перенесенного файла средствами Transact-SQL 
    SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'MyUserDataBase');

     

Известные ошибки и проблемы

  1. Unable to open the physical file "H:\MSSQL14.MSSQLSERVER\MSSQL\DATA\*юndf". Operating system error 5: "5(Access is denied.)".
    См. пункт 4, права на файл у MSSQLSERVER. Выполните повторное копирование
  2. При переводе в Offline 
    ALTER DATABASE statement failed.

    Проверяем пункт 4. Корректность изменненых файлов и путей к ним (существуют ли). В случае проблем останавливаем СУБД.