Перенос mdf ldf в sql server на другой компьютер
Системными БД в SQL являются master , msdb , model , tempdb и малоизвестная служебная БД mssqlsystemresource .
Бывает нужно перенести системные БД SQL в другую папку или на другой диск. Мне вот довелось. Делается это в 2 приема.
Сначала SQL Server нужно будет сначала перенсети master и mssqlsystemresource, а затем оставшиеся msdb, model, tempdb.
В моем примере буду переность базы из
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
в
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ NewFolder \
Запускаем SQL Server Configuration Manager
SQL Server (MSSQLSERVER) -> Properties
Вкладка Advanced, Startup Parameters
Изменяем пути
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NewFolder\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NewFolder\mastlog.ldf
Останавливаем службу SQL
Запускаем SQL в могнопольном режиме и работаем только с базой master
Для этого в cmd набираем команду
net start MSSQLSERVER /c /m /T3608
запускаем sqlcmd -A
набираем 3 строки для изменеия пути к mssqlsystemresource.mdf
1> ALTER DATABASE mssqlsystemresource
2> MODIFY FILE (name = 'data', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\mssqlsystemresource.mdf')
На выходе ответ об успешном применении
The file "data" has been modified in the system catalog. The new path will be used the next time the database is started.
набираем 3 строки для изменеия пути к mssqlsystemresource.ldf
1> ALTER DATABASE mssqlsystemresource
2> MODIFY FILE (name = 'log', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\mssqlsystemresource.ldf')
На выходе ответ об успешном применении
The file "log" has been modified in the system catalog. The new path will be used the next time the database is started.
Выходим из sqlcmd командой QUIT
Останавливаем службу SQL
Переносим файлы .mdf и .ldf от баз mssqlsystemresource и master в новое место
Стартуем SQL как обычно
Далее переносим базы msdb, model, tempdb
в MS SQL Server Management Studio выполняем запросы:
ALTER DATABASE msdb
MODIFY FILE (name = 'MSDBDATA', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\MSDBDATA.mdf')
ALTER DATABASE msdb
MODIFY FILE (name = 'MSDBLOG', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\MSDBLOG.ldf')
ALTER DATABASE model
MODIFY FILE (name = 'modeldev', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\model.mdf')
ALTER DATABASE model
MODIFY FILE (name = 'modellog', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\modellog.ldf')
ALTER DATABASE tempdb
MODIFY FILE (name = 'tempdev', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE (name = 'templog', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewFolder\templog.ldf')
Останавливаем SQL
Переносим файлы .mdf и .ldf в новое место
Стартуем SQL
Всё.
У меня есть база данных и вы хотите переместить файлы .mdf и .ldf в другое место. Но я не хочу останавливать службу MSSQLSERVER , и я не хочу экспортировать ее на другой сервер.
Как я могу это сделать?
Вам не нужно останавливать службу SQL Server для перемещения файлов базы данных, но вам нужно отключить конкретную базу данных. Это связано с тем, что вы не можете перемещать файлы во время их доступа, а использование базы данных в автономном режиме останавливает использование файлов приложением SQL Server.
Процесс их перемещения довольно прост. Detach /Attach уже был описан, но это не так сложно.
Изменить расположение файлов с помощью команды ALTER DATABASE :
Обратите внимание: вам не нужно объявлять старое местоположение в этой команде. Изменение этого пути не вступает в силу немедленно, но будет использоваться при следующем запуске базы данных.
Установить базу данных в автономном режиме
(Я использую WITH ROLLBACK IMMEDIATE , чтобы выгнать всех и отменить все транзакции, открытые в настоящий момент)
Переместить /Скопировать файлы в новое место
Просто скопируйте файлы с помощью своего любимого метода (нажмите «n Drag, XCopy, Copy-Item, Robocopy)
Принесите базу данных онлайн
Вы можете увидеть это более подробно здесь .
Файлы MDF и LDF защищены и не могут быть перемещены во время работы базы данных.
Если вы не возражаете, чтобы база данных не работала, вы можете DETACH ее переместить, а затем ATTACH .
Теперь вы должны быть в порядке. Информацию о DETACH - ATTACH можно найти .
В ссылке о DETACH - ATTACH есть рекомендация использовать инструкцию ALTER DATABASE , если поддерживать базу данных на том же экземпляр SQL Server. Дополнительная ссылка в Перенос пользовательских баз данных .
Если вы хотите, чтобы он работал во время перемещения, выполните BACKUP - RESTORE . В процессе восстановления вы можете определить новое местоположение файлов базы данных.
Чтобы переместить файлы системной базы данных, выполните следующие действия:
Войдите как пользователь в SSMS
Сделайте резервную копию созданной пользователем базы данных для обеспечения безопасности.
Убейте все сеансы, подключенные к серверу из SSMS.
Выполните следующую команду, чтобы проверить текущее расположение файлов в системных базах данных:
USE master;
SELECT * FROM sys.master_files;
Определите путь и обратите внимание на текущий путь к файлам.
Используйте TSQL для изменения пути к файлу для всей базы данных, кроме master:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
Теперь местоположение файла было изменено.
Обязательно перемещайте файлы ldf и mdf
В SSMS щелкните правой кнопкой мыши Сервер и выберите свойства. Внутри свойств перейдите в Настройки базы данных. Измените базы данных по умолчанию для данных и журнала на путь назначения. Выйдите из сервера.
Например: change C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ в E:\projects\DataBaseFiles\MSSQL\DATA\
Остановить экземпляр SQL Server.
Скопируйте файл или файлы в новое место. Используйте Robocopy для перемещения файлов, чтобы скопировать разрешения доступа в папку назначения. Откройте cmd и запустите его как администратор и используйте следующую команду:
robocopy /sec sourceFolder destinationFolder
Лучше перейти в исходное местоположение, чтобы запустить команду. Удалите другие файлы, кроме файлов системной базы данных, которые копируются. Например:
(Здесь мы перемещаем все файлы системных баз данных в новое место.)
- В меню «Пуск» выберите «Все программы», «Microsoft SQL Server», «Средства настройки» и «Диспетчер конфигурации SQL Server».
Выполните следующие шаги в диспетчере конфигурации SQL Server:
В узле SQL Server Services щелкните правой кнопкой мыши экземпляр SQL Server (например, SQL Server (MSSQLSERVER)) и выберите «Свойства». В диалоговом окне Свойства SQL Server (имя экземпляра) нажмите вкладку «Параметры запуска». В поле «Существующие параметры» выберите параметр «d», чтобы переместить файл основных данных. Нажмите «Обновить», чтобы сохранить изменения. В поле «Укажите параметр запуска» измените параметр на новый путь к основной базе данных. В поле «Существующие параметры» выберите параметр «l», чтобы переместить файл главного журнала. Нажмите «Обновить», чтобы сохранить изменения. В поле «Укажите параметр запуска» измените параметр на новый путь к основной базе данных.
Значение параметра для файла данных должно соответствовать параметру -d, а значение для файла журнала должно соответствовать параметру -l. В следующем примере показаны значения параметров для местоположения файла основных данных по умолчанию.
Если запланированное перемещение для файла основных данных E: \ SQLData, значения параметров будут изменены следующим образом:
Остановите экземпляр SQL Server, щелкнув правой кнопкой мыши имя экземпляра и выбрав «Стоп». Перезапустите экземпляр SQL Server.
Войдите в систему как пользователь sa в SSMS и проверьте расположение файлов базы данных, выполнив следующий запрос:
USE master;
SELECT * FROM sys.master_files;
Я не уверен, что это лучший способ (я бы приветствовал любые комментарии, чтобы рассказать мне, как это не так), но это очень просто (и быстро, если у вас небольшая база данных):
Сначала создайте резервную копию базы данных в файле .bak. Затем восстановите базу данных из того же .bak-файла, выбрав новые .mdf и .ldf-файлы в параметрах файла для задачи восстановления.
Я бы не делал этого в производственной среде ouside окна обслуживания, так как вы не можете получить доступ к базе данных во время восстановления. Однако другие методы, которые я видел выше, будут иметь сходные недостатки. После выполнения задачи восстановления вам не нужно удалять старый файл. Это делается автоматически.
Есть способ переместить файлы данных базы данных (еще не уверен, есть ли способ сделать это для файлов журналов), не отключая базу данных в автономном режиме.
Краткая версия заключается в том, что вы добавляете другой файл базы данных в новое место, а затем используете DBCC Shrinkfile с опцией EMPTYFILE для перемещения данных из старого файла в новый файл. Когда это будет сделано, вы можете удалить старый файл данных.
Не мое решение, я сам искал это решение и нашел его очень полезным для нашей производственной среды.
Мастер копирования базы данных можно использовать в режиме резервного копирования и восстановления. Щелкните правой кнопкой мыши на db задача Копировать базу данных
В некоторых случаях, возникает необходимость перенести файлы баз данных на другой диск. Например, базы лежат в каталоге по умолчанию на системном диске С:, который:
- Имеет маленький размер
- Сильно нагружен ОС и системными запросами
- Довольно медленный
- Помирает
Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов 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 — для именованного экземпляра.
В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для <имя> установите флажок Разрешить для параметра Полный доступ.
Вот теперь, точно всё. Спасибо за внимание!
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 с одного экземпляра MS SQL Server на другой, или изменить каталог хранения файлов базы данных, помогут операции отсоединения (Detach) и присоединения (Attach) баз данных MS SQL Server. Об этих операциях в MS SQL Server 2012 (справедливо и для более ранних редакций MS SQL Server) и пойдет речь в данной статье.
0. Оглавление
1. Отсоединение базы данных в MS SQL Server 2012
Запускаем программу «SQL Server Management Studio». В Microsoft Windows Server 2012 R2 ее можно найти в списке всех программ.
В появившемся окне отсоединения базы данных возможно очистить существующие соединения с базой установив флаг «Удалить соединения» (Drop Connections) (не рекомендуется, правильно будет попросить пользователей закрыть клиентские программы), а также обновить статистику для базы данных отметив соответственно флаг «Обновить статистику» (Update Statistics). Выбрав необходимые операции нажимаем «ОК».
После чего выбранная база данных исчезнет из списка баз данных экземпляра MS SQL Server.
Но файлы базы данных останутся на жестком диске в том каталоге, где они находились до отсоединения базы данных.
2. Присоединение базы данных в MS SQL Server 2012
После чего, наша база данных появится в списке баз данных конечного экземпляра MS SQL Server.
Смотрите также:
Может случиться так, что в процессе установки MS SQL Server 2012 были установлены компоненты, необходимость в которых со временем отпала (или были установлены по ошибке). В этом случае, в целях…
В данной статье будут рассмотрены способы лицензирования Microsoft SQL Server 2012. Будет приведен краткий обзор каждого из способов лицензирования, а также указаны возможные варианты лицензирования для различных выпусков MS SQL Server…
Ниже приводится список существующих редакций Microsoft SQL Server 2012, а также приводится краткий обзор каждой из них. Microsoft SQL Server 2012 предлагается в нескольких специально разработанных редакциях:…
Читайте также: