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

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

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

Все эти факторы влияют как на отказоустойчивость, так и на скорость обработки запросов SQl-сервером, а следовательно и на работоспособность комплекса в целом!
Теперь, когда вы прониклись важностью момента, можно приступить к практическим действиям. Итак:

Перенос пользовательской базы данных¶

1. Договариваемся с творческой частью коллектива, что в определенное время все перестают работать с базой. А именно, прекращают что-то туда добавлять и/или изменять.

2. Останавливаем сервисы, которые работают с МБД в автоматическом режиме, например:

• DB Import — импорт новостных лент
• DDB — распределенная база данных
• Sch_to_DB — репликация расписаний
  иначе, есть вероятность потерять часть информации.

3. Запускаем Microsoft SQL Server Management Studio.

4. Самым первым делом всегда делаем бэкап базы!

5. Далее, смотрим, где лежат файлы нужной нам базы данных (в нашем примере это будет МБД под названием «RADIO-DB»). Для этого, нажимаем на ней ПКМ и открываем Properties (Свойства). Заходим в раздел Files (Файлы) и смотрим раздел Path (Путь):

6. Далее, нажимаем ПКМ на целевой базе и выбираем пункт TasksDetach (ЗадачиОтсоединить):

7. В открывшемся окне ставим обе галочки и нажимаем ОК. После чего, МБД пропадет из списка:

8. Через обычный проводник заходим в каталог, где лежат нужные нам файлы. В нашем примере, это C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA.

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 FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLDATA) все файлы, указанные в скрипте выше, в новый каталог, который мы прописали в том же скрипте.

4. Обязательно добавляем учетную запись группы безопасности. Подробно о том, как это сделать, читайте в конце данной статьи, в разделе «Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы».

5. Запускаем службу SQL.

6. Убедиться, что мы все сделали правильно, можно, посмотрев в свойствах каждой системной БД раздел Files (Файлы). Там должны быть новые пути к обоим файлам (самой БД и логу).

Перенос самой системной базы данных master¶

Да, еще у нас осталась самая системная из всех системных баз — master
— путь, прописанный для этой базы, будет путем по умолчанию для всех вновь создающихся баз на данном сервере. Впрочем, для пользователей Digispot это не очень актуально. Тем более, что мы уже умеем менять пути любым базам.

Итак, master:

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 можно не копировать. Он создастся заново.

Предоставление разрешения на доступ к файловой системе идентификатору безопасности службы¶

1. С помощью проводника Windows перейдите в папку файловой системы, в которой находятся файлы базы данных. Правой кнопкой мыши щелкните эту папку и выберите пункт Свойства.

2. На вкладке Безопасность щелкните Изменитьи затем ― Добавить.

3. В диалоговом окне Выбор пользователей, компьютеров, учетных записей служб или групп щелкните Расположения, в начале списка расположений выберите имя своего компьютера и нажмите кнопку ОК.

4. В поле Введите имена объектов для выбора введите имя идентификатора безопасности службы. В качестве идентификатора безопасности службы компонента Компонент Database Engine используйте NT SERVICEMSSQLSERVER для экземпляра по умолчанию или NT SERVICEMSSQL$InstanceName — для именованного экземпляра.

5. Щелкните Проверить имена , чтобы проверить введенные данные. Проверка зачастую выявляет ошибки, по ее окончании может появиться сообщение о том, что имя не найдено. При нажатии кнопки ОК открывается диалоговое окно Обнаружено несколько имен .Теперь выберите идентификатор безопасности службы MSSQLSERVER или NT SERVICEMSSQL$InstanceName и нажмите кнопку ОК. Снова нажмите кнопку ОК , чтобы вернуться в диалоговое окно Разрешения.

6. В поле имен Группа или пользователь выберите имя идентификатора безопасности службы, а затем в поле Разрешения для <имя> установите флажок Разрешить для параметра Полный доступ.

7. Нажмите кнопку Применить, а затем дважды кнопку ОК , чтобы выполнить выход.

Вот теперь, точно всё. Спасибо за внимание!

P.S. В зависимости от конкретной ОС, конкретной версии SQL сервера, вашей кармы и наличия солнечных вспышек, что-то может пойти не так. Прежде чем приступать к вышеописанным действиям, убедитесь, что:
а) оно вам действительно надо
б) вы морально готовы
ц) вы понимаете, что вы делаете
д) у вас вся ночь впереди, чтобы переустановить SQL заново и развернуть бэкап.

To Move system database files follow these steps:

1. Log in as sa user in SSMS

2. Take a backup of the user created database for safety.

3. Kill all sessions connected to the Server from SSMS.

4. Execute the following command to check the current file location of system databases:

   USE master;
SELECT * FROM sys.master_files;

Identify the path and note the current path of the files.

1. Use TSQL to change the file path for all database except master:

   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_pathos_file_name' )

Eg:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev
, FILENAME = "DestinationPathtempdb.mdf");

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog
, FILENAME = "DestinationPathtemplog.ldf");

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev
, FILENAME = "DestinationPathmodel.mdf");

ALTER DATABASE model
MODIFY FILE ( NAME = modellog
, FILENAME = "DestinationPathmodellog.ldf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
, FILENAME = "DestinationPathmsdbdata.mdf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
, FILENAME = "DestinationPathmsdblog.ldf");

Now the file location has been changed.

Make sure to move both ldf and mdf files

1. In SSMS right click the Server and select properties . Inside properties go to Database Settings . Change the database default locations for Data and Log to the destination path. Log off from the server.

   Eg : change C:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATA to E:projectsDataBaseFilesMSSQLDATA

2. Stop the instance of SQL Server.

3. Copy the file or files to the new location. Use Robocopy to move the files in order to copy the acccess permissions to the destination folder. Open cmd and run as administrator and use the following command:

   robocopy /sec sourceFolder destinationFolder

It’s better to go to the source location to run the command. Delete other files other than system database files which are copied. Eg:

 robocopy /sec .DATA E:projectsDataBaseFilesMSSQLDATA

(Here we are moving all system database files to new location.)

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

Perform the follwing steps in SQL Server Configuration Manager:

In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

-dC:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATAmaster.mdf
-lC:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATAmastlog.ldf

If the planned relocation for the master data file is E:SQLData, the parameter values would be changed as follows:

-dE:projectsDataBaseFilesMSSQLDATAmaster.mdf
-lE:projectsDataBaseFilesMSSQLDATAmastlog.ldf

Stop the instance of SQL Server by right-clicking the instance name and choosing Stop. Restart the instance of SQL Server.

1. Log in as sa user in SSMS and verify the location of the database files by executing the following query:

   USE master;
SELECT * FROM sys.master_files;

All done.

To Move system database files follow these steps:

1. Log in as sa user in SSMS

2. Take a backup of the user created database for safety.

3. Kill all sessions connected to the Server from SSMS.

4. Execute the following command to check the current file location of system databases:

   USE master;
SELECT * FROM sys.master_files;

Identify the path and note the current path of the files.

1. Use TSQL to change the file path for all database except master:

   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_pathos_file_name' )

Eg:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev
, FILENAME = "DestinationPathtempdb.mdf");

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog
, FILENAME = "DestinationPathtemplog.ldf");

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev
, FILENAME = "DestinationPathmodel.mdf");

ALTER DATABASE model
MODIFY FILE ( NAME = modellog
, FILENAME = "DestinationPathmodellog.ldf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
, FILENAME = "DestinationPathmsdbdata.mdf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
, FILENAME = "DestinationPathmsdblog.ldf");

Now the file location has been changed.

Make sure to move both ldf and mdf files

1. In SSMS right click the Server and select properties . Inside properties go to Database Settings . Change the database default locations for Data and Log to the destination path. Log off from the server.

   Eg : change C:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATA to E:projectsDataBaseFilesMSSQLDATA

2. Stop the instance of SQL Server.

3. Copy the file or files to the new location. Use Robocopy to move the files in order to copy the acccess permissions to the destination folder. Open cmd and run as administrator and use the following command:

   robocopy /sec sourceFolder destinationFolder

It’s better to go to the source location to run the command. Delete other files other than system database files which are copied. Eg:

 robocopy /sec .DATA E:projectsDataBaseFilesMSSQLDATA

(Here we are moving all system database files to new location.)

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

Perform the follwing steps in SQL Server Configuration Manager:

In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

-dC:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATAmaster.mdf
-lC:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESSMSSQLDATAmastlog.ldf

If the planned relocation for the master data file is E:SQLData, the parameter values would be changed as follows:

-dE:projectsDataBaseFilesMSSQLDATAmaster.mdf
-lE:projectsDataBaseFilesMSSQLDATAmastlog.ldf

Stop the instance of SQL Server by right-clicking the instance name and choosing Stop. Restart the instance of SQL Server.

1. Log in as sa user in SSMS and verify the location of the database files by executing the following query:

   USE master;
SELECT * FROM sys.master_files;

All done.

Рассмотрим пример перемещения файлов пользовательской базы данных 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-EffectOfficeEffectOffice.mdf' );
 
ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_log', 
 Filename = 'U:DBCL02-EffectOfficeEffectOffice_log.LDF' );
 
ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Version_dat', 
 Filename = 'U:DBCL02-EffectOfficeEffectOffice_Version_dat.ndf' );
 
ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Message_dat', 
 Filename = 'U:DBCL02-EffectOfficeEffectOffice_Message_dat.ndf' );
 
ALTER DATABASE [EffectOffice] MODIFY FILE ( Name = 'EffectOffice_Blob_dat', 
 Filename = 'U:DBCL02-EffectOfficeEffectOffice_Blob_dat.ndf' );

Переведём базу данных в Online-режим и обратно включим многопользовательский режим работы с БД

ALTER DATABASE [EffectOffice] SET ONLINE
ALTER DATABASE [EffectOffice] SET MULTI_USER

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

После успешного запуска БД и проверок, можем удалить файлы с их исходного местоположения (T:DBCL02-EffectOffice)

Дополнительные источники информации:

Проверено на следующих конфигурациях:

Автор первичной редакции:
Алексей Максимов
Время публикации: 24.09.2020 09:15

Системная база данных tempdb служит рабочим пространством для хранения временных объектов, таких как временные таблицы, промежуточные результаты вычислений, временные хранимые процедуры, результаты буферов и сортировки, внутренние объекты, создаваемые компонентой Database Engine и пр. Кроме того, все изменения данных в базах данных, в которых используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков, а также изменения данных для таких операций, как операции с индексами в сети, множественные активные результирующие наборы (режим MARS) и триггеры AFTER также хранятся в системной базе данных tempdb. Таким образом, данная база данных используется системой довольно активно, и возможно получить значительный прирост производительности путем переноса файлов базы данных tempdb на отдельный дисковый накопитель, более быстрый SSD-диск или даже RAM-диск. О перемещении системной базы данных tempdb в MS SQL Server 2012 (справедливо для более ранних версий) и пойдет речь в данной статье.

1. Указание каталога хранения файлов базы данных tempdb во время установки  MS SQL Server 2012

Указать отдельный каталог расположения файлов базы данных tempdb, можно еще на стадии установки MS SQL Server 2012, на странице настройки компоненты Database Engine на вкладке «Каталоги данных» (Data Directories).

Подробнее про установку MS SQL Server 2012 можно прочить здесь.

2. Изменения каталога хранения файлов базы данных tempdb для существующего экземпляра MS SQL Server

Если же имеется уже установленный экземпляр MS SQL Server, то изменить каталог хранения файлов базы данных tempdb не удастся с помощью стандартных средств отсоединение и присоединение базы данных. В этом случае поможет инструкция ALTER DATABASE языка Transact-SQL. Подключимся к экземпляру MS SQL Server, для которого необходимо выполнить изменения, с помощью программы  «SQL Server Management Studio».

Просмотрим текущее расположение файлов базы данных tempdb. Для этого в обозревателе объектов (Object Explorer) раскроем вкладки «Базы данных» (Databases) — «Системные базы данных» (System Databases). Кликнем правой кнопкой мыши по базе данных tempdb и в контекстном меню выберем пункт «Свойства» (Properties).

В открывшемся окне свойств перейдем на вкладку «Файлы» (Files), где можно увидеть имена файлов (File Name), логические имена файлов (Logical Name) и путь расположения каждого из файлов (Path).

Для того чтобы изменить путь расположения необходимо выполнить запрос:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQL_Temptempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:SQL_Temptemplog.ldf');
GO

Где E:SQL_Temp новый путь расположения файлов базы данных tempdb. Каталоги файла данных базы — файла tempdb.mdf (или файлов, если их несколько) и файла журнала транзакций — tempdb.ldf могут различаться и, соответственно, могут быть изменены независимо. Для того чтобы выполнить запрос, необходимо нажать кнопку на панели задач «Создать запрос» (New Query), в окне нового запроса ввести текст запроса и нажать кнопку «Выполнить» (Execute), расположенную там же, на панели задач.

После чего необходимо перезапустить экземпляр SQL Server. Сделать это можно, здесь же, кликнув правой кнопкой мыши по имени сервера в обозревателе объектов, и выбрав в контекстном меню пункт «Перезапустить» (Restart).

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

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

3. Универсальный скрипт для продвинутых

-------------------------------------------
-- Скрипт перемещает все файлы базы данных tempdb в указанный каталог
-- Версия от 27.10.2021
-- Свежие версии скриптов: https://github.com/Tavalik/SQL_TScripts
 
-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- Новый каталог для базы tempdb
DECLARE @Path as NVARCHAR(400) = 'E:SQL_Temp'
 
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @SQLString NVARCHAR(400)
 
-------------------------------------------
-- ТЕЛО СКРИПТА
USE master;
 
-- Цикл по всем файлам базы данных tempdb
DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR 
	(
		SELECT
			name,
			physical_name
		FROM sys.master_files 
		WHERE database_id = DB_ID('tempdb')
	)
OPEN fnc;
FETCH fnc INTO @logicalName, @physicalName;
WHILE @@FETCH_STATUS=0
 
	BEGIN
		SET @SQLString = '
		ALTER DATABASE tempdb
		MODIFY FILE (NAME = ' + @logicalName 
		+ ', FILENAME = ''' + @Path + '' 
		+ REVERSE(SUBSTRING(REVERSE(@physicalName), 1, CHARINDEX('', REVERSE(@physicalName))-1)) 
		+ ''');'
 
		PRINT @SQLString
		EXEC sp_executesql @SQLString
 
		FETCH fnc INTO @logicalName, @physicalName;
	END;
 
CLOSE fnc;
DEALLOCATE fnc;

Больше полезных скриптов здесь: https://github.com/Tavalik/SQL_TScripts