Как перенести базу sql на другой диск
Перейти к содержимому

Как перенести базу sql на другой диск

  • автор:

Перемещение системных баз данных

В этой статье описывается перемещение системных баз данных в SQL Server. Эта операция может пригодиться в следующих ситуациях:

  • Восстановление после сбоя. Например, база данных находится в подозрительном режиме, или ее работа была прекращена из-за сбоя оборудования;
  • Плановое перемещение.
  • Перемещение для запланированного обслуживания дисков.

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

Для выполнения процедур, описанных в этой статье, необходимо логическое имя файлов базы данных. Это имя можно получить из столбца name представления каталога sys.master_files .

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

Перемещение системных баз данных

Чтобы переместить данные системной базы данных или файл журнала в рамках запланированного перемещения (операции запланированного обслуживания), следуйте следующим указаниям: К ним относятся системные базы данных model , msdb и tempdb .

Эта процедура применима ко всем системным базам данных, кроме master и Resource . Шаги по перемещению базы данных master см. далее в этой статье. Базу данных Resource нельзя переместить.

  1. Запишите существующее расположение файлов базы данных, которые нужно переместить, проверив представление каталога sys.master_files.
  2. Убедитесь, что учетная запись службы ядра СУБД SQL Server имеет полные разрешения на новое расположение файлов. Дополнительные сведения см. в статье Настройка учетных записей службы Windows и разрешений. Если учетная запись службы ядра СУБД не может управлять файлами в новом расположении, экземпляр SQL Server не запустится.
  3. Для перемещения каждого файла базы данных выполните следующую инструкцию.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ); 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N''); 

Дальнейшие действия после перемещения системной базы данных msdb

Если база данных msdb перемещена, а Database Mail настроена, выполните следующие дополнительные действия.

    Убедитесь, что для базы данных включен msdb компонент Service Broker, выполнив следующий запрос.

SELECT is_broker_enabled FROM sys.databases WHERE name = N'msdb'; 

Если компонент Service Broker не включен msdb , его необходимо повторно включить, чтобы компонент Database Mail функционировал. Дополнительные сведения см. в статье об ALTER DATABASE . SET ENABLE_BROKER.

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; 

Процедура восстановления после сбоя

Если нужно перенести файл из-за сбоя оборудования, необходимо выполнить приведенные ниже действия для его перемещения на новое место. Эта процедура применима ко всем системным базам данных, кроме master и Resource . В следующих примерах используется командная строка Windows и служебная программа sqlcmd.

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

  1. Убедитесь, что учетная запись службы ядра СУБД SQL Server имеет полные разрешения на новое расположение файлов. Дополнительные сведения см. в статье Настройка учетных записей службы Windows и разрешений. Если учетная запись службы ядра СУБД не может управлять файлами в новом расположении, экземпляр SQL Server не запустится.
  2. Остановите экземпляр SQL Server, если он запущен.
  3. Запустите экземпляр SQL Server в режиме восстановления только для главного сервера, введя одну из следующих команд в командной строке. При использовании параметра запуска 3608 SQL Server прекращается автоматический запуск и восстановление любой базы данных, кроме master . Дополнительные сведения см. в разделах Параметры запуска и TF3608. В задаваемых для них параметрах учитывается регистр символов. Команды завершаются ошибкой, если параметры заданы не так, как показано. В случае с экземпляром по умолчанию (MSSQLSERVER) запустите следующую команду:
NET START MSSQLSERVER /f /T3608 

В случае с именованным экземпляром запустите следующую команду:

NET START MSSQL$instancename /f /T3608 
sqlcmd 

Чтобы подключиться к именованному экземпляру на локальном сервере с помощью встроенной проверки подлинности Active Directory выполните следующее:

sqlcmd -S localhost\instancename 

Дополнительные сведения о синтаксисе sqlcmd см. в разделе о служебной программе sqlcmd. Для перемещения каждого файла используйте команды sqlcmd или SQL Server Management Studio для выполнения следующей инструкции. Дополнительные сведения об использовании программы sqlcmd см. в статье Использование программы sqlcmd. После открытия сеанса sqlcmd выполните следующую инструкцию по одному разу для перемещения каждого файла:

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' ) GO 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N''); 

Перемещение базы данных master

Чтобы переместить базу данных master , выполните следующие действия.

  1. Убедитесь, что учетная запись службы ядра СУБД SQL Server имеет полные разрешения на новое расположение файлов. Дополнительные сведения см. в статье Настройка учетных записей службы Windows и разрешений. Если учетная запись службы ядра СУБД не может управлять файлами в новом расположении, экземпляр SQL Server не запустится.
  2. Через меню Пуск найдите и запустите диспетчер конфигурации SQL Server. Дополнительные сведения об ожидаемом расположении см. в статье о диспетчере конфигурации SQL Server.
  3. На узле служб SQL Server щелкните правой кнопкой мыши экземпляр SQL Server (например, SQL Server (MSSQLSERVER)) и выберите «Свойства«.
  4. В диалоговом окне Свойства SQL Server (instance_name) выберите вкладку Параметры запуска.
  5. В поле Существующие параметры выберите параметр -d . В поле «Указание параметра запуска» измените параметр на новый путь к файлу master данных. Для сохранения изменений выберите Обновить.
  6. В поле Существующие параметры выберите параметр -l . В поле «Указание параметра запуска» измените параметр на новый путь к файлу master журнала. Для сохранения изменений выберите Обновить. Значение параметра для файла данных должно соответствовать параметру -d , а значение для файла журнала — параметру -l . В следующем примере показаны значения параметров для местоположения файла данных master по умолчанию. -dC:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\DATA\master.mdf -lC:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\DATA\mastlog.ldf Если файл данных базы данных master планируется переместить в E:\SQLData , значения параметров необходимо изменить следующим образом: -dE:\SQLData\master.mdf -lE:\SQLData\mastlog.ldf
  7. Нажмите ОК, чтобы окончательно сохранить изменения и закрыть диалоговое окно Свойства SQL Server (имя_экземпляра).
  8. Остановите экземпляр SQL Server, щелкнув правой кнопкой мыши имя экземпляра и выбрав «Остановить«.
  9. master.mdf Скопируйте файлы в mastlog.ldf новое расположение.
  10. Перезапустите экземпляр SQL Server.
  11. Проверьте правильность изменений файла для базы данных master , выполнив следующий запрос.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID('master'); 

Перемещение базы данных ресурсов

Расположение базы данных Resource — \:\Program Files\Microsoft SQL Server\MSSQL\.\\MSSQL\Binn\\ . Базу данных нельзя переместить.

Дальнейшие действия. После перемещения всех системных баз данных

Если все системные базы данных перемещаются на новый диск или том либо на другой сервер с другой буквой диска, выполните следующие обновления.

  • Измените путь к журналу агента SQL Server. Если этого не сделать, то агент SQL Server не сможет запуститься.
  • Измените расположение по умолчанию для базы данных. Создание новой базы данных может завершиться ошибкой, если буква диска и путь, указанные в качестве места расположения по умолчанию, не существуют.
Измените путь к журналу агента SQL Server.

Если все системные базы данных перемещены в новый том или перенесены на другой сервер с другой буквой диска, а путь к файлу SQLAGENT.OUT журнала ошибок агента SQL больше не существует, сделайте следующее обновление.

  1. В SQL Server Management Studio в обозревателе объектов разверните Агент SQL Server.
  2. Щелкните правой кнопкой мыши Журналы ошибок и выберите Настроить.
  3. В диалоговом окне Настройка журналов ошибок агента SQL Server задайте новое расположение для файла SQLAGENT.OUT. По умолчанию он расположен в папке C:\Program Files\Microsoft SQL Server\MSSQL\.\MSSQL\Log\\ .
Измените расположение по умолчанию для базы данных
  1. Из SQL Server Management Studio в обозревателе объектов подключитесь к требуемому экземпляру SQL Server. Щелкните экземпляр правой кнопкой мыши и выберите пункт Свойства.
  2. В диалоговом окне Свойства сервера выберите пункт Настройки базы данных.
  3. На панели Места хранения, используемые базой данных по умолчаниюможно просмотреть текущие места хранения, используемые по умолчанию для новых файлов данных и файлов журнала.
  4. Остановите и запустите службу SQL Server, чтобы завершить изменение.

Примеры

О. Перемещение базы данных tempdb

В следующем примере показано перемещение файлов базы данных tempdb и журнала на новое место в рамках запланированного перемещения.

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

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

    Определение логических имен файлов базы данных tempdb и их текущего местоположения на диске.

SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO 
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf'); GO 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); 

См. также

  • База данных Resource
  • База данных tempdb
  • База данных master
  • База данных msdb
  • База данных model

Далее

  • Перемещение пользовательских баз данных
  • Перемещение файлов базы данных
  • Запуск, остановка, приостановка, возобновление и перезапуск компонента Database Engine, агента SQL и службы браузера SQL Server
  • ALTER DATABASE (Transact-SQL)
  • Перестроение системных баз данных

Перемещение пользовательских баз данных

В SQL Server можно переместить файлы данных, журналов и полнотекстового каталога пользовательской базы данных в новое расположение, указав новое расположение файла в предложении FILENAME инструкции ALTER DATABASE . Этот метод применяется к перемещению файлов базы данных в одном экземпляре SQL Server. Чтобы переместить базу данных в другой экземпляр SQL Server или на другой сервер, используйте операции резервного копирования и восстановления или отсоединения и присоединения.

В этой статье рассматривается перемещение файлов пользовательской базы данных. Сведения о перемещении файлов системной базы данных см. в разделе Перемещение системных баз данных.

Рекомендации

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

Некоторые функции ядра СУБД SQL Server изменяют способ хранения сведений в файлах базы данных. Эти функции ограничены определенными выпусками SQL Server. База данных, содержащая эти функции, не может быть перемещена в выпуск SQL Server, который не поддерживает их. Используйте динамическое административное представление sys.dm_db_persisted_sku_features для просмотра всех функций текущей базы данных, зависящих от выпуска.

Для выполнения процедур, описанных в этой статье, необходимо логическое имя файлов базы данных. Это имя можно получить из столбца name представления каталога sys.master_files .

Начиная с SQL Server 2008 R2 (10.50.x), полнотекстовые каталоги интегрируются в базу данных, а не хранятся в файловой системе. Полнотекстовые каталоги теперь перемещаются автоматически при перемещении базы данных.

Убедитесь, что у учетной записи Служб баз данных SQL Server есть разрешения для нового расположения файлов в файловой системе. Дополнительные сведения см. в статье Настройка разрешений файловой системы для доступа к компоненту ядра СУБД.

Процедура запланированного перемещения

Для запланированного перемещения файлов журнала или данных выполните следующие действия.

    Для каждого перемещаемого файла выполните следующую инструкцию.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ); 
ALTER DATABASE database_name SET OFFLINE; 

Для выполнения этого действия требуется эксклюзивный доступ к базе данных. Если открыто другое соединение к базе данных, инструкция ALTER DATABASE будет заблокирована до тех пор, пока не будут закрыты все соединения. Чтобы переопределить это поведение, используйте предложение WITH . Например, чтобы автоматически выполнить откат и разорвать все остальные соединения с базой данных, выполните инструкцию:

ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE database_name SET ONLINE; 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N''); 

Перемещение для запланированного обслуживания дисков

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

    Для каждого перемещаемого файла выполните следующую инструкцию.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ); 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N''); 

Процедура восстановления после сбоя

Если файл необходимо переместить в новое место из-за аппаратного сбоя, выполните следующие действия.

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

  1. Остановите экземпляр SQL Server, если он запущен.
  2. Запустите экземпляр SQL Server в режиме восстановления только для главного сервера, введя одну из следующих команд в командной строке.
  3. В случае с экземпляром по умолчанию (MSSQLSERVER) выполните следующую команду.
NET START MSSQLSERVER /f /T3608 
NET START MSSQL$instancename /f /T3608 
ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' ); 
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N''); 

Примеры

В следующем примере файл журнала базы данных AdventureWorks2022 переносится в новое место во время запланированного перемещения.

USE master; GO -- Return the logical file name. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG'; GO ALTER DATABASE AdventureWorks2022 SET OFFLINE; GO -- Physically move the file to a new location. -- In the following statement, modify the path specified in FILENAME to -- the new location of the file on your server. ALTER DATABASE AdventureWorks2022 MODIFY FILE ( NAME = AdventureWorks2022_Log, FILENAME = 'C:\NewLoc\AdventureWorks2022_Log.ldf'); GO ALTER DATABASE AdventureWorks2022 SET ONLINE; GO --Verify the new location. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG'; 

См. также

  • ALTER DATABASE (Transact-SQL)
  • CREATE DATABASE (SQL Server Transact-SQL)
  • Отсоединение базы данных и подключение (SQL Server)
  • Перемещение системных баз данных
  • Перемещение файлов базы данных
  • BACKUP (Transact-SQL)
  • RESTORE (Transact-SQL)
  • Запуск, остановка, приостановка, возобновление и перезапуск компонента Database Engine, агента SQL и службы браузера SQL Server

Вики IT-KB

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

Рассмотрим пример перемещения файлов пользовательской базы данных SQL Server в новое месторасположение. В рассматриваемом примере все файлы одной отдельно взятой БД с именем EffectOffice будут перенесены с одного логического диска на другой (с диска T:\ на диск U:\ ).

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

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

USE master; SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID('EffectOffice');

Выполним запрос на закрытие всех соединений к БД и перевод БД в одно-пользовательский режим:

ALTER DATABASE [EffectOffice] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Переведём базу данных в Offline-режим:

ALTER DATABASE [EffectOffice] SET OFFLINE;

Выполним копирование файлов БД в новое место-расположение с помощью утилиты командной строки robocopy, которая позволит нам сохранить все разрешения на каталоги и файлы на уровне NTFS.

В нашем примере файлы БД копируются из каталога T:\DBCL02-EffectOffice в каталог U:\DBCL02-EffectOffice . Каталог назначения при этом будет создан в процессе копирования и на него будут скопированы все атрибуты исходного каталога.

ROBOCOPY "T:\DBCL02-EffectOffice" "U:\DBCL02-EffectOffice" ^ /E /B /COPYALL /DCOPY:DAT /V /R:2 /W:10 ^ /UNILOG+:U:\Robocopy.log /BYTES /TEE /NP /UNICODE

Выполним замену путей к файлам на уровне SQL Server запросом вида (отдельный запрос по каждому файлу):

ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_dat', Filename = 'U:\DBCL02-EffectOffice\EffectOffice.mdf' ); ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_log', Filename = 'U:\DBCL02-EffectOffice\EffectOffice_log.LDF' ); ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Version_dat', Filename = 'U:\DBCL02-EffectOffice\EffectOffice_Version_dat.ndf' ); ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Message_dat', Filename = 'U:\DBCL02-EffectOffice\EffectOffice_Message_dat.ndf' ); ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Blob_dat', Filename = 'U:\DBCL02-EffectOffice\EffectOffice_Blob_dat.ndf' );

Переведём базу данных в Online-режим и обратно включим многопользовательский режим работы с БД

ALTER DATABASE [EffectOffice] SET ONLINE ALTER DATABASE [EffectOffice] SET MULTI_USER

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

После успешного запуска БД и проверок, можем удалить файлы с их исходного местоположения ( T:\DBCL02-EffectOffice )

Дополнительные источники информации:

Проверено на следующих конфигурациях:

Версия SQL Server
Microsoft SQL Server 2016 Standard Edition SP2 CU14 (13.0.5830.85)

Автор первичной редакции:
Алексей Максимов
Время публикации: 24.09.2020 09:15

Обсуждение

microsoft-sql-server/t-sql-script-samples/how-to-move-sql-server-database-files-to-another-directory-or-to-a-different-drive.txt · Последнее изменение: 24.09.2020 09:15 — Алексей Максимов

Перенести базу данных MS SQL на другой диск.

Сервер 1С создал новый массив на SSD и хочу перенести на него часть баз 1С в MSSQL
Вопрос как Microsoft SQL Server поменять место хранения базы данных ?

Administrator
Сообщений: 734 Баллов: 8901 Рейтинг: 0 Регистрация: 09.02.2011
17.03.2016 18:10:01

РЕШЕНИЕ:
1. Далее Вам необходимо зайти в SQL Managment Studio.
2. Сделайте бэкап базы, которую хотите перенести на другой диск (на всякий случай).
3. Нажмите правой кнопкой по нужной вам базе пункт Tasks -> Detach. В открывшемся окне нажмите ок
4. Зайдите в ту папку, там где физически лежит Ваша база данных (например у Вас C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data) и скопируйте в новую папку или диск два файла, они должны называться как Ваша база данных (расширение файлов MDF и LDF).
5. В SQL Managment Studio, в дереве, станьте на элемент «Databases» и нажмите правую кнопку, затем Attach.
6. В появившемся окне нажмите Add (добавить) и выберите файл из той папки, куда вы скопировали базу и нажмите ок.

ФОТО ТОГО КАК ЭТО ОПИСАНО ВЫШЕ:



При ошибке:
The file «*****.mdf» is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Решение:
The solution to this is marking the mdf and ldf file as uncompressed. Follow the following steps,
1. Right-click the .mdf file and select the properties menu.
2. From the Properties dialog box select the Advanced button
4. From the Advanced Dialog un-tick the «Compress Contents To Save Disk Space check box»

Do the same thing for the .ldf file

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *