Как изменить путь к базе данных sql

Просмотр или изменение расположения по умолчанию для файлов данных и журнала

Рекомендуемым способом защиты файлов данных и журналов является защита с помощью списков управления доступом (ACL). Задайте в качестве расположения списков ACL корневой каталог, где создаются файлы.

Просмотр или изменение расположений по умолчанию для файлов базы данных

В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите Свойства.
На странице «Свойства» левой панели откройте вкладку Параметры базы данных .
В области Места хранения, используемые базой данных по умолчаниюможно просмотреть текущие расположения, используемые по умолчанию для новых файлов данных и файлов журнала. Чтобы изменить местоположение по умолчанию, введите новый путь по умолчанию в поле Данные или Журнал или нажмите кнопку обзора, перейдите к нужному пути и выберите его.

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

Перемещение пользовательских баз данных

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

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

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

Для запланированного перемещения файлов журнала или данных выполните следующие действия.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

ALTER DATABASE database_name SET OFFLINE;

Для выполнения этого действия требуется эксклюзивный доступ к базе данных. Если открыто другое соединение к базе данных, инструкция ALTER DATABASE будет заблокирована до тех пор, пока не будут закрыты все соединения. Чтобы переопределить это поведение, используйте предложение WITH . Например, чтобы автоматически выполнить откат и разорвать все остальные соединения с базой данных, выполните инструкцию:

ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE database_name SET ONLINE;

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'');

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

Чтобы переместить файл во время процесса запланированного обслуживания дисков, необходимо выполнить нижеприведенные шаги.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'');

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

Если файл необходимо переместить в новое место из-за аппаратного сбоя, выполните следующие действия.

Если базу данных запустить нельзя, она находится в подозрительном режиме или в невосстановленном состоянии, то файл может быть перемещен только членом предопределенной роли sysadmin.

Остановите экземпляр SQL Server, если он запущен.
Запустите экземпляр SQL Server в режиме восстановления только для главного сервера, введя одну из следующих команд в командной строке.
В случае с экземпляром по умолчанию (MSSQLSERVER) выполните следующую команду.

NET START MSSQLSERVER /f /T3608

NET START MSSQL$instancename /f /T3608

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' );

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'');

Примеры

В следующем примере файл журнала базы данных AdventureWorks2022 переносится в новое место во время запланированного перемещения.

USE master; GO -- Return the logical file name. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG'; GO ALTER DATABASE AdventureWorks2022 SET OFFLINE; GO -- Physically move the file to a new location. -- In the following statement, modify the path specified in FILENAME to -- the new location of the file on your server. ALTER DATABASE AdventureWorks2022 MODIFY FILE ( NAME = AdventureWorks2022_Log, FILENAME = 'C:\NewLoc\AdventureWorks2022_Log.ldf'); GO ALTER DATABASE AdventureWorks2022 SET ONLINE; GO --Verify the new location. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG';

См. также

ALTER DATABASE (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL)
Отсоединение базы данных и подключение (SQL Server)
Перемещение системных баз данных
Перемещение файлов базы данных
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
Запуск, остановка, приостановка, возобновление и перезапуск компонента Database Engine, агента SQL и службы браузера SQL Server

Как изменить каталог хранения баз данных на сервере SQL Server 2008 R2

По умолчанию каталог где создаются/разворачиваются базы данных (файлы с расширением mdf & ldf (логи)) это системный диск C:, а путь до самого файла следующий:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
И вот в ходе одного эксперимента столкнулся — что если развернуть из бекапа базу данных то получится ошибка, о том что не достаточно свободного места для ее разворачивания.

Если я правильно перевожу описание ошибки, то базе нужно около 36Gb свободного места, а у меня только свободно: 24,3 Gb
Поэтому я для себя уже вынес итог своего бездумного развертывания сервиса базы данных, а именно, что базы данных нужно хранить отдельно от системы дабы не уронить ее или застопорить.
Далее я покажу, что нужно сделать чтобы произвести настройку, чтобы создаваемые и восстанавливаемые базы должны находиться, к примеру на логическом диске D: (данный логический диск специально добавлен в систему и имеет повышенный размер по сравнению с дефолтной настройкой системы которую я обычно для тестовых систем создаю: System (Disk C: — 50Gb)

Добавил в системе еще один диск на 50Gb (старайтесь всегда брать с запасом от размера самой базы, к примеру 50% — я уже так на собственном опыте столкнулся.)
Теперь я покажу конечно же в шагах как изменить дефолтное месторасположение каталога для баз данных, лог файлов и каталог куда выполняется бекап по умолчанию:
Дефолтные пути можно посмотреть так:
Start — All Programs — Microsoft SQL Server 2008 R2 — запускаем оснастку: SQL Server Management Studio, подключаемся к серверу базы данных, выделяем левой кнопкой строку: srv-bd (SQL Server 10.50.1600 — SRV-BD\Administrator) и через правый клик мышью открываем Properties (Свойства) — Database Settings

Дефолтные пути расположения файлов базы данных и логов

и здесь же их можно изменить на каталог на добавленном логическом диске D:\Data
изменения применятся, когда будет перезапущена службу сервера и агента или же просто перезагрузить сервер.
Либо изменить пути месторасположения через запрос с вот таким вот скриптом в специально подготовленные каталоги:
d:\>tree DB

Folder PATH listing for volume New Volume

Volume serial number is 38F1-E3AB

Start — All Programs — Microsoft SQL Server 2008 R2 — запускаем оснастку: SQL Server Management Studio, подключаемся к серверу базы данных, выделяем левой кнопкой строку: srv-bd (SQL Server 10.50.1600 — SRV-BD\Administrator) — New Query

Перенос файлов баз данных (.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

Читать:

Как создать свой мессенджер на telegram