Как создать базу данных sql на другом диске

Обновлено: 07.07.2024

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

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

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов 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 Server изначально не был установлен.

Оригинальная версия продукта: SQL Server
Исходный номер КБ: 295732

Сводка

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

Если вы попытайтесь создать базу данных на другом совместном накопителе кластера, если SQL Server не зависит от этого диска, вы можете получить ошибку, аналогичную:

Сервер: Msg 5184, Level 16, State 2, Line 1. Не удается использовать файл "%.*ls" для кластерного сервера. Можно использовать только отформатированные файлы, для которых кластерный ресурс сервера имеет зависимость.
Сервер: Msg 1802, Уровень 16, состояние 1, строка 1
Сбой CREATE DATABASE. Некоторые имена файлов, перечисленные в списке, не удалось создать. Проверьте предыдущие ошибки.

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

Кроме того, при попытке создания полно текстового каталога индекса на диске, на котором ресурс SQL Server не зависит, отображается следующая ошибка:

Сервер: Msg 7627, Level 16, State 1, Procedure sp_fulltext_database, Полный текстовый каталог строки 61 в каталоге "Y:\FTDATA" для кластерного сервера не может быть создан. Можно использовать только каталоги на диске в кластерной группе сервера.

Дополнительные сведения

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

В ситуации когда необходимо перенести базу данных 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 предлагается в нескольких специально разработанных редакциях:…

Многим новичкам поначалу может казаться, что выполнить бэкап в MS SQL сразу, куда-нибудь на сетевой диск, NAS или «расшаренную» папку в локальной сети нельзя, так как нет возможности по умолчанию в Management Studio выбрать «сетевую папку» или любое другое устройство в нашей локальной сети. (Кстати видео уроки по этой теме и не только есть во всех подробностях на курсе: Администратор 1С, если статьи будет мало!)

Но конечно это не так, и все решается довольно просто.

Готово!

Теперь можно прямо, используя Management Studio, указать через двойной слеш IP адрес (или хост имя сервера), затем имя общего сетевого ресурса, и указав имя файла, можно сделать бэкап или восстановление базы (Как на рисунке ниже).

Аналогично можно выполнить как бэкап так и восстановление при помощи Transact-SQL.

Но в MS SQL можно упростить эту задачу, подключив удаленное устройство, грубо говоря, как «сетевой диск», и уже в следующий раз, когда потребуется выполнить бэкап, просто выбрать его, что собственно упростит и ускорит задачу по резервному копированию и восстановлению баз данных.

Теперь нам не придется снова и снова указывать полный путь к «расшаренной папке» достаточно будет лишь выбрать устройство, за которым «шара» уже будет закреплена.

Существует два простых способа, чтоб это сделать.

Второй, используя Transact-SQL.

И так способ первый:

Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>

В данном материале мы рассмотрим возможность перемещения файлов базы данных 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 необходимо выполнить для каждого файла, который Вы переместили.

Шаг 6

Переводим базу данных обратно в нормальный режим.

Шаг 7

Проверяем новое расположение файлов, для этого еще раз выполним запрос, который мы использовали на шаге 2.

Как видим, у нас отобразилось новое физическое расположение файлов БД, т.е. мы успешно переместили файлы базы данных на новый диск (рекомендую также проверить работу самой БД).

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Читайте также: