Как перенести файлы базы данных sql на другой диск

Как перенести файлы базы данных sql на другой диск

12 апреля, 2022 kevich

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

Как переместить базу данных tempdb в MS-SQL

Иногда случается, что необходимо перенести служебную базы данных tempdb в другой каталог или на другой диск, например, в случае, когда при установке эта база была установлена на медленный диск, например, на системный, как это часто случается в случае типовой установки MS-SQL сервера. Обычными графическими средствами MS-SQL Enterprise Manager ( EM ) перенос сделать нельзя, в отличие от обычной пользовательской базы, которой можно сделать « detach / attach ».

Но то, чего нельзя сделать графическими средствами, можно сделать средствами T-SQL . Для этого нужно выполнить в Query Analyzer совсем небольшой скрипт.

Use master
go
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = ‘tempdev’ , FILENAME = ‘E:worksqltempdb.mdf’ )
go
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = ‘templog’ , FILENAME = ‘E:worksqltemplog.ldf’ )
go

В приведенном примере база данных tempdb переносится в каталог « E:worksql », на диске « E: ». Но этим действия не заканчиваются. Данный скрипт только вносит изменения в служебные данные сервера. Чтобы данная операция была выполнена до конца, необходимо перезапустить MS-SQL сервер. В момент запуска база данных tempdb создастся уже в новом каталоге. После того, как сервер запустился, файлы базы tempdb по старому ее адресу можно спокойно удалить, так как сами они не удаляются.

Перенос tempdb на другой диск

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

Запланированный перенос базы данных

В случае если вам просто необходимо перенести рабочую базу данных, то в manegement studio необходимо выполнить запрос:

• database_name — имя базы данных, которую необходимо перенести;
• logical_name — логическое имя файла;
• disk:\new_path\new_file_name — новый путь к файлу данных.

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

Перемещение базы данных в случае сбоя

Такая ситуация может возникнуть, если восстановить базу данных в прежнее место невозможно, а без этой базы данных SQL сервер не запускается. Например, как я уже писал в начале, вышедший из строя диск с базой tempdb приведет к остановке MSSQL и невозможности его запуска.

Процедура действий в данном случае почти такая же как и при запланированном переносе. Все операции производим через командную строку cmd с правами администратора. Для начала необходимо запустить SQL сервер в режиме восстановления:

Затем запустив консольную команду sqlcmd выполнить все те же команды по указанию нового пути к файлам данных для БД. Например для tempdb будут примерно такие команды:

После того как введены все SQL запросы в интерактивном режиме sqlcmd необходимо ввести команду GO, чтобы выполнить этим самые запросы, а затем EXIT, чтобы выйти из интерактивного режима sqlcmd. Папка c:\tempdb (или та куда вы переносите базу данных tempdb) должна быть заранее создана. Если вы восстанавливаете не tempdb, а любую другую БД, то необходимо в эту папку так же положить файлы БД из резервной копии с именами совпадающими с теми, что указаны в SQL запросах.

Теперь можно перезапускать MSSQL сервер в стандартном режиме:

Если используется не экземпляр по умолчанию, а именованый, то необходимо заменить MSSQLSERVER на MSSQL $instancename, где instancename — наименование экземпляра MSSQL.

Если все сделано верно, то службы MSSQL запустятся и продолжат работу в штатном режиме.

Перенос TempDB в SQL Server 2019

Временная база данных (tempdb) в SQL Server — это системная база данных, доступная всем пользователям, подключенным к экземпляру SQL Server. В tempdb хранятся как в ременные пользовательские объекты, так и в нутренние объекты, создаваемые ядром СУБД.

Tempdb пользуется особой популярностью, поэтому для нее настоятельно рекомендуется размещать файлы данных и журнала транзакций на специально выделенных дисках, отдельно от операционной системы и других баз данных. И желательно на максимально быстром хранилище с низким временем задержки, типа SSD NVMe или Intel Optane.

Также для ускорения ввода-вывода рекомендуется создавать несколько файлов данных в зависимости от количества логических процессоров, выделенных для сервера базы данных (Database Engine). Если число логических процессоров меньше восьми, рекомендуется создать файл данных для каждого логического процессора, е сли равно или больше восьми, то используйте восемь файлов данных.

В идеале размещение баз лучше планировать заранее, но, к сожалению, это не всегда возможно. И может возникнуть ситуация, когда требуется переместить файлы базы на уже работающем сервере. Для tempdb эта процедура несложная, рассмотрим ее по пунктам.

Первым делом выделяем новый диск H, на котором будут находиться файлы TempDB. Размещать файлы базы в корне диска не рекомендуется, даже если диск целиком выделен под их хранение, поэтому создаем отдельную директорию, назовем ее TempDB. На эту директорию необходимо выдать права на чтение и изменение учетной записи, от имени которой работает служба MS SQL (SQL Server Database Engine).

Теперь нам надо выяснить текущее расположение файлов tempdb. Сделать это можно из графической оснастки, открыв в свойствах базы вкладку Files

либо с помощью вот такого sql-запроса:

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

В поле Name мы получаем логическое имя файла, а в поле CurrentLocation путь к файлу. Как видите, в нашем примере имеется 8 файлов базы и один файл журнала транзакций.

Для каждого файла нужно выполнить следующий код:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ‘new_path\file_name’ )

• database_name — имя базы (в нашем случае tempdb);
• logical_name — логическое имя файла базы;
• new_path\file_name — новый путь и физическое имя файла.

Можно немного облегчить себе жизнь и сгенерировать код с помощью запроса (перед выполнением нажмите CTRL + T для возврата результата в виде текста):

SELECT ‘ALTER DATABASE »tempdb» MODIFY FILE ( NAME = ‘+[name]+’, FILENAME = ‘+[physical_name]+’ )’
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);

В полученном запросе остается только поменять старый путь на новый. В результате получим:

ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = tempdev, FILENAME = H:\TEMPDB\tempdb.mdf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = templog, FILENAME = H:\TEMPDB\templog.ldf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp2, FILENAME = H:\TEMPDB\tempdb_mssql_2.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp3, FILENAME = H:\TEMPDB\tempdb_mssql_3.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp4, FILENAME = H:\TEMPDB\tempdb_mssql_4.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp5, FILENAME = H:\TEMPDB\tempdb_mssql_5.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp6, FILENAME = H:\TEMPDB\tempdb_mssql_6.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp7, FILENAME = H:\TEMPDB\tempdb_mssql_7.ndf )
ALTER DATABASE ‘tempdb’ MODIFY FILE ( NAME = temp8, FILENAME = H:\TEMPDB\tempdb_mssql_8.ndf )

Выполняем полученный запрос.

Рестартуем службу MS SQL (SQL Server Database Engine). Проверяем в настройках tempdb расположение файлов.

И затем идем в новую папку и проверяем, что в ней появились файлы.

На этом процесс переноса завершен и можно удалить файлы из старого расположения. В завершение некоторые важные моменты:

• Tempdb создается заново всякий раз, когда стартует служба SQL. Поэтому файлы не требуется переносить с места на место, система сама создаст их во время старта службы;
• Пока не будет создан основной файл и файл лога — служба полностью не стартует и СУБД будет недоступна;
• Для подстраховки можно не перезапускать службу MS SQL, а полностью остановить ее, переименовать старую директорию с tempdb, и только потом запускать службу. Так будет сразу видно, что всё работает, а в случае проблем можно быстро откатить изменения;
• Если файлы большие – желательно дать учётной записи SQL право Perform Volume Maintenance Tasks ( Выполнение задач по обслуживанию томов ).