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

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

• Имеет маленький размер
• Сильно нагружен ОС и системными запросами
• Довольно медленный
• Помирает

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:

Перенос пользовательской базы данных¶

1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.

2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:

• DB Import – импорт новостных лент
• DDB – распределенная база данных
• Sch_to_DB – репликация расписаний
  иначе, есть вероятность потерять часть информации.

3. Запускаем Microsoft SQL Server Management Studio.

4. Самым первым делом всегда делаем бэкап базы!

5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием "RADIO-DB"). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):

6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт TasksDetach (ЗадачиОтсоединить):

7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:

8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA.

9. Копируем эти файлы в новый каталог на новый диск и снова открываем Microsoft SQL Server Management Studio.

10. Нажимаем ПКМ на разделе Databases (Базы данных), выбираем пункт Attach (Присоединить) и в открывшемся окне нажимаем кнопку Add (Добавить) и выбираем нужный нам файл RADIO-DB.mdf уже из нового каталога:

Убеждаемся, что пути у нас теперь новые и нажимаем ОК.

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

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

Но, остались еще системные базы данных (спрятаны в разделе System Databases). Это msdb, model и tempdb, которые в общем-то тоже будет неплохо перенести на быстрый и отказоустойчивый диск. Тем более, что среди них есть одна, очень для нас важная база – tempdb. Именно через нее проходят все запросы, прежде чем попасть в пользовательскую МБД. Перенести системные базы ничуть не сложнее, чем пользовательские. И для этого надо:

1. Используя Microsoft SQL Server Management Studio, выполнить следующий скрипт:

Его также можно скачать из этого описания и запустить непосредственно на SQl-сервере.

2. Останавливаем службу SQL.

3. Копируем из старого каталога (помним наш пример: C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.

4. Обязательно добавляем учетную запись группы безопасности. Подробно о том, как это сделать, читайте в конце данной статьи, в разделе "Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы".

5. Запускаем службу SQL.

6. Убедиться, что мы все сделали правильно, можно, посмотрев в свойствах каждой системной БД раздел Files (Файлы). Там должны быть новые пути к обоим файлам (самой БД и логу).

Перенос самой системной базы данных master¶

Да, еще у нас осталась самая системная из всех системных баз – master
– путь, прописанный для этой базы, будет путем по умолчанию для всех вновь создающихся баз на данном сервере. Впрочем, для пользователей Digispot это не очень актуально. Тем более, что мы уже умеем менять пути любым базам.

1. Для изменения пути к БД master, нам понадобится оснастка SQL Server Configuration Manager (Диспетчер конфигурации SQL Server). Запускаем ее и открываем свойства SQL Server:

2. В свойствах SQL Server`а открываем вкладку Startup Parameters (Параметры запуска):

и по очереди меняем все указанные пути на новые.
– каждая строка начинается со своего символа -d, -e или -l. Ни в коем случае не меняйте их и не удаляйте!

3. Каждое изменение пути подтверждаем нажатием кнопки Update.

4. Теперь останавливаем сервис, копируем файлы master.mdf и mastlog.ldf из старого каталога в новый. После чего запускам сервис. ERRORLOG можно не копировать. Он создастся заново.

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

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

На вкладке Безопасность щелкните Изменитьи затем ― Добавить.

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

В поле Введите имена объектов для выбора введите имя идентификатора безопасности службы. В качестве идентификатора безопасности службы компонента Компонент Database Engine используйте NT SERVICEMSSQLSERVER для экземпляра по умолчанию или NT SERVICEMSSQL$InstanceName — для именованного экземпляра.

Щелкните Проверить имена , чтобы проверить введенные данные. Проверка зачастую выявляет ошибки, по ее окончании может появиться сообщение о том, что имя не найдено. При нажатии кнопки ОК открывается диалоговое окно Обнаружено несколько имен .Теперь выберите идентификатор безопасности службы MSSQLSERVER или NT SERVICEMSSQL$InstanceName и нажмите кнопку ОК. Снова нажмите кнопку ОК , чтобы вернуться в диалоговое окно Разрешения.

В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для установите флажок Разрешить для параметра Полный доступ.

Нажмите кнопку Применить, а затем дважды кнопку ОК , чтобы выполнить выход.

Вот теперь, точно всё. Спасибо за внимание!

P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.

detach_db2.PNG Просмотреть (31,7 КБ) Станислав Середницкий (Москва), 22/03/2018 17:27

detach_db.PNG Просмотреть (62,9 КБ) Станислав Середницкий (Москва), 22/03/2018 17:28

detach_db3.PNG Просмотреть (87,3 КБ) Станислав Середницкий (Москва), 22/03/2018 17:56

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

SQL Server SQL Server позволяет переносить в новое место файлы данных, журнала и полнотекстового каталога пользовательской базы данных; новое место указывается при помощи предложения FILENAME инструкции ALTER DATABASE . In SQL Server SQL Server , you can move the data, log, and full-text catalog files of a user database to a new location by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. Этот метод подходит для перемещения файлов базы данных в пределах одного экземпляра SQL Server SQL Server . This method applies to moving database files within the same instance SQL Server SQL Server . Для переноса базы данных на другой экземпляр SQL Server SQL Server или другой сервер применяются операции резервного копирования и восстановления или отключения и подключения. To move a database to another instance of SQL Server SQL Server or to another server, use backup and restore or detach and attach operations.

Замечания Considerations

Чтобы обеспечить целостность работы пользователей и приложений при перемещении базы данных на другой экземпляр сервера, необходимо повторно создать некоторые или все метаданные базы данных. When you move a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all the metadata for the database. Дополнительные сведения см. в статье Управление метаданными при обеспечении доступности базы данных на другом экземпляре сервера (SQL Server). For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

Некоторые функции компонента Компонент SQL Server Database Engine SQL Server Database Engine изменяют способ, с помощью которого Компонент Database Engine Database Engine хранит информацию в файлах базы данных. Some features of the Компонент SQL Server Database Engine SQL Server Database Engine change the way that the Компонент Database Engine Database Engine stores information in the database files. Эти функции зависят от конкретных выпусков SQL Server SQL Server . These features are restricted to specific editions of SQL Server SQL Server . База данных, содержащая данные функции, не может быть перемещена в выпуск SQL Server SQL Server , который их не поддерживает. A database that contains these features cannot be moved to an edition of SQL Server SQL Server that does not support them. Используйте динамическое административное представление sys.dm_db_persisted_sku_features чтобы просмотреть список всех зависящих от выпуска функций, включенных в текущей базе данных. Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

Для выполнения процедур, описанных в данном разделе, необходимо логическое имя файлов базы данных. The procedures in this topic require the logical name of the database files. Это имя можно получить из столбца name представления каталога sys.master_files . To obtain the name, query the name column in the sys.master_files catalog view.

Начиная с SQL Server 2008 R2 SQL Server 2008 R2 , полнотекстовые каталоги интегрированы в базу данных, а не хранятся в файловой системе. Starting with SQL Server 2008 R2 SQL Server 2008 R2 , full-text catalogs are integrated into the database rather than being stored in the file system. Полнотекстовые каталоги теперь перемещаются автоматически при перемещении базы данных. The full-text catalogs now move automatically when you move a database.

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

Для запланированного перемещения файлов журнала или данных выполните следующие действия. To move a data or log file as part of a planned relocation, follow these steps:

Выполните следующую инструкцию: Run the following statement.

Переместите файл или файлы в новое расположение. Move the file or files to the new location.

Для каждого перемещенного файла выполните следующую инструкцию: For each file moved, run the following statement.

Выполните следующую инструкцию: Run the following statement.

Проверьте изменения в файле с помощью следующего запроса. Verify the file change by running the following query.

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

Чтобы переместить файл во время процесса запланированного обслуживания дисков, необходимо выполнить нижеприведенные шаги. To relocate a file as part of a scheduled disk maintenance process, follow these steps:

Для каждого перемещаемого файла выполните следующую инструкцию. For each file to be moved, run the following statement.

Остановите работу экземпляра SQL Server SQL Server или выключите систему для проведения работ по обслуживанию дисков. Stop the instance of SQL Server SQL Server or shut down the system to perform maintenance. Дополнительные сведения см. в статье Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.

Переместите файл или файлы в новое расположение. Move the file or files to the new location.

Перезапустите экземпляр SQL Server SQL Server или сервер. Restart the instance of SQL Server SQL Server or the server. Дополнительные сведения см. в разделе Запуск, остановка, приостановка, возобновление и перезапуск ядра СУБД, агента SQL Server и обозревателя SQL Server. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service

Проверьте изменения в файле с помощью следующего запроса. Verify the file change by running the following query.

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

Если файл необходимо переместить в новое место из-за аппаратного сбоя, выполните следующие действия. If a file must be moved because of a hardware failure, use the following steps to relocate the file to a new location.

Если базу данных запустить нельзя, она находится в подозрительном режиме или в невосстановленном состоянии, то файл может быть перемещен только членом предопределенной роли sysadmin. If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

Остановите работу экземпляра SQL Server SQL Server , если он запущен. Stop the instance of SQL Server SQL Server if it is started.

Запустите экземпляр SQL Server SQL Server в режиме восстановления «только master», запустив из командной строки одну из следующих команд. Start the instance of SQL Server SQL Server in master-only recovery mode by entering one of the following commands at the command prompt.

В случае с экземпляром по умолчанию (MSSQLSERVER) выполните следующую команду. For the default (MSSQLSERVER) instance, run the following command.

В случае с именованным экземпляром выполните следующую команду. For a named instance, run the following command.

Для каждого перемещаемого файла используйте команды sqlcmd или SQL Server Management Studio SQL Server Management Studio для выполнения следующей инструкции. For each file to be moved, use sqlcmd commands or SQL Server Management Studio SQL Server Management Studio to run the following statement.

Дополнительные сведения об использовании программы sqlcmd см. в статье Использование программы sqlcmd. For more information about how to use the sqlcmd utility, see Use the sqlcmd Utility.

Завершите работу программы sqlcmd или SQL Server Management Studio SQL Server Management Studio . Exit the sqlcmd utility or SQL Server Management Studio SQL Server Management Studio .

Остановите экземпляр SQL Server SQL Server . Stop the instance of SQL Server SQL Server .

Переместите файл или файлы в новое расположение. Move the file or files to the new location.

Запустите экземпляр SQL Server SQL Server . Start the instance of SQL Server SQL Server . Например, выполните команду NET START MSSQLSERVER . For example, run: NET START MSSQLSERVER .

Проверьте изменения в файле с помощью следующего запроса. Verify the file change by running the following query.

Примеры Examples

В следующем примере файл журнала базы данных AdventureWorks2012 AdventureWorks2012 переносится в новое место во время запланированного перемещения. The following example moves the AdventureWorks2012 AdventureWorks2012 log file to a new location as part of a planned relocation.

База данных (далее — БД) в MS SQL Server занимает достаточно много места на жесткой диске и иногда требуется перенести ее на другой раздел или диск.

Для того, чтобы это сделать необходимо:

1. Войти в консоль MS SQL Server Managmet Studio (Пуск — Программы — MS SQL Server)

2. В окне «Object Explorer» раскрыть список (+) баз данных (Databases)

3. Для начала определите, где хранятся файлы БД. Для этого нажмите правой кнопкой мыши на БД, которую мы хотим перенести (для примера возьмем БД «test«) и выберите пункт Properties (Свойства):

перейдите в раздел «Files«, в колонке «Path» отображается путь, где хранятся файлы БД (test) и лог-файла (test_log):

4. Открепляем БД. Для этого нажимаем правой кнопкой мыши на БД и выбрираем «Tasks» — «Detach«:

5. В окне «Detach Database» ставим галки «Drop Connections» и «Update Statistics«:

Нажать кнопку «ОК«. После чего БД исчезнет в списке баз данных (Databases)

6. Переносим БД (test) и лог-файла (test_log) в новый раздел (например, в раздел D:data)

7. Нажимаем правой кнопкой мыши на «Databases» и выбираем пункт «Attache» (Прикрепить):

7. В окне «Attach Databases» указываем новый путь к файлам БД. Для этого нажимаем кнопку «Add«:

8. Выбираем нашу БД «test.mdf«: