Вики 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 — Алексей Максимов
Перенос файлов баз данных (.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, выполнить следующий скрипт:
-- #################################################################### -- Script for changing paths to physical files (mdf & ldf) -- of the system databases (exept master). -- Just change "D:\mdb\sys_db" to your preffered path in all strings. -- Don`t forget to restart SQL Server proccess after execution. -- #################################################################### USE master; GO ALTER DATABASE msdb MODIFY FILE (name = 'MSDBDATA', filename = 'D:\mdb\sys_db\MSDBDATA.mdf') ALTER DATABASE msdb MODIFY FILE (name = 'MSDBLOG', filename = 'D:\mdb\sys_db\MSDBLOG.ldf') ALTER DATABASE model MODIFY FILE (name = 'modeldev', filename = 'D:\mdb\sys_db\model.mdf') ALTER DATABASE model MODIFY FILE (name = 'modellog', filename = 'D:\mdb\sys_db\modellog.ldf') ALTER DATABASE tempdb MODIFY FILE (name = 'tempdev', filename = 'D:\mdb\sys_db\tempdb.mdf') ALTER DATABASE tempdb MODIFY FILE (name = 'templog', filename = 'D:\mdb\sys_db\templog.ldf')Его также можно скачать из этого описания и запустить непосредственно на 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
attach_db.PNG Просмотреть (84,4 КБ) Stanislav Serednitskiy, 22/03/2018 18:05
System_DB_files_moving.sql Просмотреть (993 байта) Stanislav Serednitskiy, 22/03/2018 18:49
sql_conf_man.PNG Просмотреть (31,7 КБ) Stanislav Serednitskiy, 22/03/2018 19:14
start_param.PNG Просмотреть (15,3 КБ) Stanislav Serednitskiy, 22/03/2018 19:18
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«:
Нажимаем «ОК«.
Перенос базы на другой жёсткий диск¶
Жёсткий диск/раздел должен существовать в системе и быть подключен физически. Перенос базы данных выглядит так:
Нельзя использовать одну и ту же точку монтирования для файлов и БД.
Если на диске есть требуемые разделы, переходим к п. 2.
Если разделов нет — размечаем диск своей любимой программой разметки , например так:
sudo cfdisk /dev/sdX
и создаём на нём файловую систему командой
sudo mkfs.ext4 /dev/sdX
где Х — это завершение имени диска.
- Останавливаем сервисы командами:
sudo service postgresql stop sudo staffcop stop
- Редактируем файл /etc/fstab
sudo nano -w /etc/fstab
Прописываем туда следующую строку:
/dev/sdX /var/lib/postgresql/ ext4 rw,noatime 0 2
Где /dev/sdX — ваш жёсткий диск, /var/lib/postgresql/ — каталог, в котором будет отображаться содержимое диска, ext4 — тип файловой системы (если вы используете другую фс — jfs/xfs/reiser и т.д. эта опция меняется.) rw — означает разрешение на чтение-запись на диск. Также можно прописывать диск по UUID. Получить UUID диска можно командой:
sudo blkid
В этом случае первая часть записи примет вид UUID= , где в кавычки нужно вписать резальтат вывод вышеуказанной команды.
Разделы с GPT-форматированием можно примонтировать только по UUID-диска!
- Создаём папку для резервного копирования, и перемещаем данные из каталога базы данных туда.
mkdir /home/user/rezerv && sudo mv /var/lib/postgresql/* /home/user/rezerv
Где user — это домашний каталог пользователя, скорее всего, у вас он отличается. Узнать домашний каталог пользователя можно командой
env | grep -E "home|HOME"
результатом выдачи данной команды будет домашний каталог пользователя, от имени которого эта команда выполнена.
- Проверяем корректность монтирования командой
sudo mount -a
эта команда монтирует диск, который уже прописан в fstab, но еще не примонтирован.
Соответственно, если мы что-то вписали неверно либо ошиблись, то мы увидим ошибки монтирования, и, соответственно, получим возможность исправить допущенную ошибку. Проверяем корректность монтирования диска командой типа
df -h
Также можно проверить, что данный раздел доступен на запись. Например, создадим текстовый файл и проверим его наличие командами
touch /var/lib/postgresql/11/main/test_write.txt && ls -l /var/lib/postgresql/11/main/
Либо просто выполнив команду mount без параметров: ее результатом станет вывод всех монтированных систем; наше новое устройство должно быть монтировано rw.
- Копируем всё на жд,
sudo cp -R /home/user/rezerv/* /var/lib/postgresql/
В случае, если вы перемещали данные в другой каталог, произведите изменения в соответствии с реальным расположением файлов.
- Меняем владельца на postgres.
sudo chown -R postgres:postgres /var/lib/postgresql/11/main
- Выставляем ему права доступа на 700.
sudo chmod -R 700 /var/lib/postgresql/11/main
- Запускаем сервисы postgresql, staffcop и nginx.
sudo service postgresql start sudo staffcop start
sudo staffcop sql
в появившемся приглашении пишем analyze; ждём.
ошибки типа «ПРЕДУПРЕЖДЕНИЕ: «pg_shdescription» пропускается — только суперпользователь может анализировать этот объект» не критичны, т.к. говорят о том, что команда, запущенная с данными правами, не смогла проанализировать служебные таблицы БД. Это и не требуется.
- Заходим в веб-интерфейс, проверяем что всё работает, все отчёты видны и тд. Если всё в порядке, можно удалять резервные файлы
rm -R /home/user/rezerv
Как и любой rm следует применять с осторожностью.
© Copyright Atom Security, Inc.