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

Как в Microsoft SQL Server перенести tempdb на другой диск

Microsoft SQL Server использует tempdb при выполнении запросов и является служебной базой и хранит в себе временные оьъекты (временные таблицы, процедуры, вычисления, сортировки, объекты и т.д.). Как правило база tempdb всегда сильно нагружена и её ускорение даст нам небольшой прирост в производительности. Одна из рекомендаций — перенос базы tempdb на другой дискю Лучше всего переносить на SSD диск.

Это основная причина для переноса tempdb на другой диск и эта процедура довольно простая.

Расположение файлов базы tempdb по умолчанию

Во время установки MS SQL Server будет предложена Настройка компонента Database Engine, где в вкладке Каталоги данных мы можем выбрать каталог хранения базы данных tempdb. По умоланию база tempdb распологается в следующем каталоге:

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA

Выполняем запрос на перенос базы tempdb на другой диск

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

USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TEMP_DB\tempdb.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\TEMP_DB\templog.ldf') GO

После выполнения запроса вам потребуется Перезапустить Microsoft SQL Server. В процессе перезапуска пользователей не выбросит из 1С �� так что жмём смелее! После перезапуска сервер самостоятельно создаст базу tempdb там, где вы указали. Старые файлы необходимо самостоятельно подчистить ��.

Перезапуск службы Microsoft SQL Server

Также помимо файлов tempdb.mdf и templog.ldf могут быть ещё несколько штук типа tempdb_mssql_1.ndf. Их таже необходимо перенести в новый каталог:

ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2, FILENAME = 'D:\TEMP_DB\tempdb_mssql_1.ndf' ) ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3, FILENAME = 'D:\TEMP_DB\tempdb_mssql_2.ndf' )

Проверяем новые пути tempdb через запрос

После того, как мы перенесли все файлы в новую директорию и перезапустили службу MS SQL SERVER мы можем проверяем новые пути расположения баз данных tempdb выполнив запрос ниже:

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

В ответ мы получим таблицу с новыми путями до файлов баз данных TEMPDB

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

Как перенести файл базы данных SQL Server

В данной статье покажу 2 способа перемещения файла(ов):

1. Через «Отсоединить/Присоединить» базу;
2. Через Offline/Online базы.

Для данного эксперимента то же самое железо и программно обеспечение, которое описано в статье по созданию базы для 1с

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

Запускаем Management Studio через меню Пуск:

Также можно запустить командным способом: Win+R далее вводим команду SSMS далее OK:

Далее неоходимо будет выбрать пользователя для входа в систему, в данном случае был выбран пользователь из-под которого был установлен MS SQL Server, который является администратором в данном экземпляре SQL Server. Жмём «Соединить»(Connect):

Если небходимо подключиться к удалённому серверу без доменной авторизации, то необходимо будет использовать «Проверка подлинности SQL Server»(SQL Server Authentication):

И далее в соответствующих полях необходимо ввести логин(Имя для входа) и пароль — вводим все данные и жмём Соединить:

После успешного подключения в левой части экрана будет модальное окно «Обозреватель объектов»(Object Explorer):

Также будем использовать уже ранее созданную базу данных [db_for_1c], в которой уже на этапе создания была создана дополнительная файловая группа помимо PRIMARY и эта дополнительная группа является группой по умолчанию.

Для начала нужно определиться какой файл и куда будем перемещать. Для этого в «Обозреватель Объектов»(Object Explorer) в списке баз данных на нужной нам базе правой кнопкой мыши кликаем один раз и во всплывшем диалоговом окне левой кнопкой мыши кликаем один раз на «Свойства»(Properties):

Для эксперимента возьмём файл db_for_1c_file1 — это его логическое имя. Свойство необходимо, чтобы взять отуда логическое имя файла и сопоставить его с физическим файлом.

Также можно сделать запрос к системной таблице sys.database_files , но обязательно в контексте нужной базы данных:

 use db_for_1c go 
SELECT name , physical_name FROM sys.database_files 

, запускаем его с помощью F5 или кнопкой «Выполнить» с зелёным треугольником, результат выполнения данного запроса ниже:

Чуть-чуть о контексте исполнения запросов — базу можно выбрать двумя способами: 1-й графически (выбрать в левом верхнем углу, по умолчанию будет master), 2-й способ — использовать конструкцию USE [database_name]. Если использовать 1-й способ, тогда USE использовать необязательно, но тоже можно — никаких конфликтов не будет.

Приступим к первому способу перемещения файла или файлов, для этого на нужной базе правой кнопкой кликаем по базе и вызываем диалоговое окно, также когда мы заходили в свойства, только сейчас нужно кликнуть по «Задачи»(Tasks) всплывает новое диалоговое окно — жмём на самый верхний пункт «Отсоединить»(Detach. ). см. скрин ниже:

Во всплывшем окне «Отсоединение базы данных»(Detach Database) устанавливаем птичку «удалить соединения»(Drop Connections) и жмём OK:

База «исчезла» из «Обозреватель объектов»(Object Explorer):

Далее с помощью «Мой компьютер» или других файловых менеджеров переносим файл в другой каталог(диск), например из D:\c\base\sql\ в D:\c\base\sql\test_path\ файл db_for_1c_file1.ndf

Следующим действим необходимо снова присоединить базу: правой кнопкой мыши кликаем на «Базы данных»(Databases) затем кликаем левой кнопкой на «присоединить»(Attach. )

В окне «Присоединение баз данных»(Attach Databases) под полем «Базы данных для присоединения:» (Databases to attach:) кликаем по кнопке «Добавить. «(Add. )

Необходимо найти файл с метаданными, как правило данный файл имеет расширение mdf, в нашем случае это файл db_for_1c.mdf, после того как мы его нашли жмём ОК:

В поле «Сведения о базе данных»(Database details) в колонке Текущий путь к файлу(Current File Path) необходимо прописать корректные пути для файлов базы данных.

После того, как пути будут прописаны — жмём ОК.

База данных вновь появилась в списке:

Выводы первого способа: данный способ можно использовать совершенно не зная T-SQL только кликая мышкой и делая copy-paste, но минус данного способа, если много файлов и пути собьются, то придётся их перепрописывать.

Во-втором способе переместим обратно из D:\c\base\sql\test_path\ в D:\c\base\sql\ файл db_for_1c_file1.ndf

При работающей базе запустим следующий SQL-запрос:

USE master;
GO
ALTER DATABASE db_for_1c
MODIFY FILE
(
NAME = db_for_1c_file1,
FILENAME = N’D:\c\base\sql\db_for_1c_file1.ndf’
);

, где в NAME — прописываем логическое имя файла, а FILENAME — физическое имя файла с указанием полного пути

и запускаем его с помощью F5 или кнопкой «Выполнить» с зелёным треугольником:

следующим шагом переводим базу в автономный режим(Take Offline):

установим птичку «Удалить все активные подключения» и жмём OK:

База отключена, о чём свидетельствует статус «Вне сети»(Offline):

после этого перемещаем файл из D:\c\base\sql\test_path\ в D:\c\base\sql\

и переводим базу в оперативный режим (Bring Online):

базу успешно снова ввели в работу (Online), о чём свидетельствует сообщение об успешно проделанной работе:

Проверяем базу путём открытия списка таблиц:

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

PS: Какой из способов использовать каждый должен решать сам, хотя нужно знать оба способа. Желаю успехов в работе.

Скачать файлы с sql кодом

Как переместить файлы базы данных Microsoft SQL Server на другой диск?

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

USE master; GO SELECT db_name(dbid) AS [DatabaseName], loginame AS [UserName] FROM sys.sysprocesses WHERE db_name(dbid) = N'TestBase'

Где, TestBase – это название базы данных (как и во всех представленных ниже SQL запросах).

Если запрос не вернул данных, значит можно приступать к переносу файлов БД, если же, кто-то в ней работает, то необходимо попросить пользователя выйти или в случае необходимости закрыть все процессы самому (например, с помощью инструкции kill или монитора активности SSMS).

Шаг 2

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

USE master; GO SELECT Name, Physical_name AS CurrentLocation, State_desc FROM sys.master_files WHERE database_id = DB_ID(N'TestBase')

Где, Name – это и есть логическое имя файла, оно нам потребуется для переноса файла в новое место.

Шаг 3

Далее мы переводим базу данных в режим «Вне сети». Это делается следующим образом:

ALTER DATABASE TestBase SET OFFLINE;

Шаг 4

После вышеперечисленных действий осуществляем физическое перемещение файлов БД в новое место (в моем случае я перемещаю файлы в каталог D:\DataBase\).

Шаг 5

Затем изменяем свойства базы данных, а именно путь к файлам БД. Инструкцию ALTER DATABASE необходимо выполнить для каждого файла, который Вы переместили.

ALTER DATABASE TestBase MODIFY FILE ( NAME = TestBase, FILENAME = 'D:\DataBase\TestBase.mdf'); GO ALTER DATABASE TestBase MODIFY FILE ( NAME = TestBase_log, FILENAME = 'D:\DataBase\TestBase_log.ldf'); GO

Где, параметр NAME и есть логическое имя файла БД, а FILENAME — это новое расположение файла.

Шаг 6

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

ALTER DATABASE TestBase SET ONLINE;

Шаг 7

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

SELECT Name, Physical_name AS CurrentLocation, State_desc FROM sys.master_files WHERE database_id = DB_ID(N'TestBase')

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

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

На этом у меня все, надеюсь, материал был Вам полезен, пока!