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

Область применения: SQL Server

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

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

• Плановое перемещение.

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

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

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

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

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

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');
   

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

4. Остановите экземпляр SQL Server для выполнения обслуживания. Дополнительные сведения см. в разделе "Пуск", "Остановка", "Приостановка", "Возобновление" и перезапуск служб SQL Server.

5. Скопируйте файл (файлы) базы данных в новое расположение. Этот шаг не нужен для системной tempdb базы данных. Эти файлы создаются в новом расположении автоматически.

6. Перезапустите экземпляр SQL Server или сервера. Дополнительные сведения см. в разделе "Пуск", "Остановка", "Приостановка", "Возобновление" и перезапуск служб SQL Server.

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

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

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

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

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

1. Убедитесь, что для базы данных включен 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;
   

   Убедитесь, что значение is_broker_enabled теперь равно 1.

2. Отправкой тестового сообщения проверьте работоспособность компонента Database Mail.

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

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

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

2. Остановите экземпляр SQL Server, если он запущен.

3. Запустите экземпляр SQL Server в masterрежиме восстановления только с помощью одной из следующих команд в командной строке. При использовании параметра запуска 3608 SQL Server прекращается автоматический запуск и восстановление любой базы данных, кроме master. Дополнительные сведения см. в разделах Параметры запуска и TF3608.

   В задаваемых для них параметрах учитывается регистр символов. Команды завершаются ошибкой, если параметры не указаны, как показано ниже.

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

   NET START MSSQLSERVER /f /T3608
   

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

   NET START MSSQL$instancename /f /T3608
   

   Дополнительные сведения см. в разделе "Пуск", "Остановка", "Приостановка", "Возобновление" и перезапуск служб SQL Server.

4. Сразу после запуска службы с флагом трассировки 3608 и /f запустите подключение к серверу sqlcmd, чтобы утвердить доступное отдельное подключение. Например, при локальном выполнении программы sqlcmd на том же сервере, что и экземпляр по умолчанию (MSSQLSERVER), а также для подключения с помощью встроенной проверки подлинности Active Directory выполните следующую команду:

   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
   

5. Закройте служебную программу sqlcmd или SQL Server Management Studio.

6. Остановите экземпляр SQL Server. Например, в командной строке выполните команду NET STOP MSSQLSERVER.

7. Скопируйте файл (файлы) в новое расположение.

8. Перезапустите экземпляр SQL Server. Например, в командной строке выполните команду NET START MSSQLSERVER.

9. Проверьте изменения в файле с помощью следующего запроса.

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

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

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<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.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');
    

12. На этом этапе среда SQL Server должна выполняться как обычно. Однако корпорация Майкрософт рекомендует также изменить запись реестра, указанную в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, где instance_ID имеет вид MSSQL13.MSSQLSERVER. В этом кусте измените SQLDataRoot значение на новый путь к новому расположению master файлов базы данных. Невозможность обновления реестра может привести сбою исправления и обновления.

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

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

Расположение базы данных Resource — \<drive>:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\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\<version>.<instance_name>\MSSQL\Log\.

Измените расположение по умолчанию для базы данных

1. Из SQL Server Management Studio в обозреватель объектов подключитесь к требуемому экземпляру SQL Server. Щелкните экземпляр правой кнопкой мыши и выберите пункт Свойства.

2. В диалоговом окне Свойства сервера выберите пункт Настройки базы данных.

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

4. Остановите и запустите службу SQL Server, чтобы завершить изменение.

Примеры

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

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

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

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

   SELECT name,
          physical_name AS CurrentLocation
   FROM sys.master_files
   WHERE database_id = DB_ID(N'tempdb');
   GO
   

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

3. Измените местоположение каждого файла с помощью ALTER DATABASE.

   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
   

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

4. Остановите и перезапустите экземпляр SQL Server.

5. Проверьте изменение файла.

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

6. Удалите неиспользуемые файлыtempdb из исходного местоположения.

Связанный контент

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