Вики IT-KB
Как перенести файлы базы данных SQL Server в другой каталог или на другой диск
Рассмотрим пример перемещения файлов пользовательской базы данных SQL Server в новое месторасположение. В рассматриваемом примере все файлы одной отдельно взятой БД с именем EffectOffice будут перенесены с одного логического диска на другой (с диска T:\ на диск U:\ ).
Перед началом процедуры переноса файлов базы данных остановим cервисы и приложения, работающие с этой базой данных.
Подключимся к экземпляру SQL Server, на котором расположена интересующая нас база данных и выясним текущее размещение файлов БД с помощью запроса:
Выполним запрос на закрытие всех соединений к БД и перевод БД в одно-пользовательский режим:
Переведём базу данных в Offline-режим:
Выполним копирование файлов БД в новое место-расположение с помощью утилиты командной строки robocopy, которая позволит нам сохранить все разрешения на каталоги и файлы на уровне NTFS.
В нашем примере файлы БД копируются из каталога T:\DBCL02-EffectOffice в каталог U:\DBCL02-EffectOffice . Каталог назначения при этом будет создан в процессе копирования и на него будут скопированы все атрибуты исходного каталога.
Выполним замену путей к файлам на уровне SQL Server запросом вида (отдельный запрос по каждому файлу):
Переведём базу данных в Online-режим и обратно включим многопользовательский режим работы с БД
Запустим сторонние службы и приложения, использующие базу данных и убедимся в штатной работе с данными.
После успешного запуска БД и проверок, можем удалить файлы с их исходного местоположения ( T:\DBCL02-EffectOffice )
Дополнительные источники информации:
Проверено на следующих конфигурациях:
| Версия SQL Server |
|---|
| Microsoft SQL Server 2016 Standard Edition SP2 CU14 (13.0.5830.85) |
Автор первичной редакции:
Алексей Максимов
Время публикации: 24.09.2020 09:15
MS SQL Server — как перенести БД на другой диск (раздел)
База данных (далее — БД) в 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«: 
Как переместить файлы базы данных Microsoft SQL Server на другой диск?
В данном материале мы рассмотрим возможность перемещения файлов базы данных 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, который покажет Вам, сколько процессов запущено в конкретной базе данных.
Где, TestBase – это название базы данных (как и во всех представленных ниже SQL запросах).
Если запрос не вернул данных, значит можно приступать к переносу файлов БД, если же, кто-то в ней работает, то необходимо попросить пользователя выйти или в случае необходимости закрыть все процессы самому (например, с помощью инструкции kill или монитора активности SSMS).
Шаг 2
Затем нам необходимо узнать логические имена файлов базы данных, это можно сделать, например, с помощью следующего запроса, который вернет нам: логические имена файлов, текущее физическое расположение файлов и их статус.
Где, Name – это и есть логическое имя файла, оно нам потребуется для переноса файла в новое место.
Шаг 3
Далее мы переводим базу данных в режим «Вне сети». Это делается следующим образом:
Шаг 4
После вышеперечисленных действий осуществляем физическое перемещение файлов БД в новое место (в моем случае я перемещаю файлы в каталог D:\DataBase\).
Шаг 5
Затем изменяем свойства базы данных, а именно путь к файлам БД. Инструкцию ALTER DATABASE необходимо выполнить для каждого файла, который Вы переместили.
Где, параметр NAME и есть логическое имя файла БД, а FILENAME — это новое расположение файла.
Шаг 6
Переводим базу данных обратно в нормальный режим.
Шаг 7
Проверяем новое расположение файлов, для этого еще раз выполним запрос, который мы использовали на шаге 2.
Как видим, у нас отобразилось новое физическое расположение файлов БД, т.е. мы успешно переместили файлы базы данных на новый диск (рекомендую также проверить работу самой БД).
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Перенос файлов баз данных (.mdf и.ldf) на другой диск¶
В некоторых случаях, возникает необходимость перенести файлы баз данных на другой диск. Например, базы лежат в каталоге по умолчанию на системном диске С:, который:
- Имеет маленький размер
- Сильно нагружен ОС и системными запросами
- Довольно медленный
- Помирает
Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:
Перенос пользовательской базы данных¶
1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.
2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:
- DB Import — импорт новостных лент
- DDB — распределенная база данных
- Sch_to_DB — репликация расписаний
иначе, есть вероятность потерять часть информации.
3. Запускаем Microsoft SQL Server Management Studio.
4. Самым первым делом всегда делаем бэкап базы!
5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием «RADIO-DB»). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):
6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт Tasks\Detach (Задачи\Отсоединить):
7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:
8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA.
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 Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.
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 SERVICE\MSSQLSERVER для экземпляра по умолчанию или NT SERVICE\MSSQL$InstanceName — для именованного экземпляра.
Щелкните Проверить имена , чтобы проверить введенные данные. Проверка зачастую выявляет ошибки, по ее окончании может появиться сообщение о том, что имя не найдено. При нажатии кнопки ОК открывается диалоговое окно Обнаружено несколько имен .Теперь выберите идентификатор безопасности службы MSSQLSERVER или NT SERVICE\MSSQL$InstanceName и нажмите кнопку ОК. Снова нажмите кнопку ОК , чтобы вернуться в диалоговое окно Разрешения.
В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для <имя> установите флажок Разрешить для параметра Полный доступ.
Нажмите кнопку Применить, а затем дважды кнопку ОК , чтобы выполнить выход.
Вот теперь, точно всё. Спасибо за внимание!
P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.
detach_db2.PNG Просмотреть (31,7 КБ) Stanislav Serednitskiy, 22/03/2018 17:27
detach_db.PNG Просмотреть (62,9 КБ) Stanislav Serednitskiy, 22/03/2018 17:28
detach_db3.PNG Просмотреть (87,3 КБ) Stanislav Serednitskiy, 22/03/2018 17:56