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

Posted by

По умолчанию каталог где создаются/разворачиваются базы данных (файлы с расширением 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 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

D:\DB

├───BACKUP

├───DATA

└───LOGS

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

USE [master]

GO

EXEC xp_instance_regwrite

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultData',

REG_SZ,

N'D:\DB\Data'
GO

EXEC xp_instance_regwrite

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultLog',

REG_SZ,

N'D:\DB\Logs'
GO

EXEC xp_instance_regwrite

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'BackupDirectory',

REG_SZ,

N'D:\DB\Backup'

GO

Создаю запрос на изменение каталог базы данных, бекапа и логов

и нажимаем Execute (Запустить запрос), если в окне (Messages) наблюдаем следующий скриншот, составленный выше скрипт успешно произвел изменения месторасположения каталога под базы данных, логов базы данных и будущих бекапов (будет подставляться данный каталог когда в ручную создаем бекап или же посредством Maintenance Plan (План Обслуживания))

(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Query executed successfully.

Запрос на изменение успешно отработал

Теперь чтобы изменения применились следует:

Закрыть оснастку SQL Management Studio (File — Exit — (Save changes to the following items? — отвечаем No)) , созданный запрос (SQLQuery1.sql) можно не сохранять, он больше не понадобиться.

И все также как и в действиях по заметке по перемещению системной базы tempdb перезапустить службы сервера и агента SQL или же перезагрузить сервер.
А теперь восстанавливаем из бекап базу данных, шаги этой процедуры описаны в заметке.
Вот собственно и всё, я успешно перенёс базу на другой диск увеличенного объема, впрочем учиться нужно как на своих ошибках так и на чужих, а лучше читать best practics по развертыванию и использования, с уважением автор блога Олло Александр aka ekzorchik.