Как определить дефолтную локацию для файлов БД

С оригиналом поста можно ознакомиться в блоге https://blogs.msdn.com/alexejs/archive/2009/06/25/9803218.aspx.

 

 

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

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

2. Местоположение одного из системных объектов, таких, как системные базы (https://msdn.microsoft.com/ru-ru/library/ms143752.aspx), Error Log и т.д.

3. Файловый каталог для системных объектов. Достаточно экзотическая вещь, поскольку имеет смысл в начальный момент времени. Системные объекты на то и системные, что создаются только на этапе установки. В процессе инсталляции SQL Server для них задается некоторый каталог. Не все они лежат там кучно, к тому же впоследствии администратор может их из него переместить. Тогда следует ориентироваться на п.2, а каталог для системных объектов останется чисто номинальной сущностью. Тем не менее.

Ниже будет показано, как определять каждое из трех значений.

Начнем с этапа установки (для ясности). Здесь показано, что на комп, где уже установлено 2 экземпляра SQL Server, которых зовут SQLEXPRESS и INSTANCE2 ставится третий SQL Server. У каждого экземпляра свои базы (в т.ч. системные) и настройки.

рис.1

Обращение к экземпляру происходит в форме <имя машины>\<имя экземпляра>. Третий экземпляр ставится как экземпляр по умолчанию. У экземпляра по умолчанию имя пустое, т.е. к нему можно обращаться просто по имени машины. Не то, чтобы экземпляр по умолчанию был совсем безымянный; у него есть имя = MSSQLSERVER, но оно служебное и имеет некоторые особенности. Сейчас об этом не будем распространяться, чтобы не усложнять. Понятно, что двух экземпляров с одним именем на одной машине быть не может, следовательно, экземпляров по умолчанию может быть не более одного.

рис.2

Всего на одном компе можно залудить максимум 50 экземпляров SQL Server, кроме редакции Workgroup, которой не более 16, как написано здесь - https://msdn.microsoft.com/ru-ru/library/ms143432.aspx. Из текста мне лично непонятно, означает ли это, что можно собрать вперемешку SQL Server Enterprise, SQL Server Standard и SQL Express общим числом полсотни или по полсотни каждого наименования. На практике я пока не проверял, равно как и никто из знакомых. Настоящих буйных мало L (с) Высоцкий.

Меняем предложенную по умолчанию директорию, куда будет ставится экземпляр, на c:\Инстанс по дефолту:

 

рис.3

На основе директории, куда будет происходить установка экземпляра, сетап предлагает поддиректории для пользовательских баз (туда будут класться mdfы и ndfы) – это п.1 в нашей терминологии, журналов пользовательских баз (туда будут класться ldfы) и т.д. Обратите внимание, что директорию для системных баз поменять нельзя. Она задается жестко относительно директории, куда ставится экземпляр. Данная папка – это п.3.

 

рис.4

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

 

рис.5

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

 

рис.6

После того, как она успешно завершается, видим, что директория, которая нам показывалась как вместилище системных баз (п.3),

действительно содержит master, msdb, и т.д., а tempdb, хотя тоже является системной базой, но для нее мы указали отдельные директории на рис.5.

рис.7

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

рис.8

 

рис.9

и файлы журнала транзакций в директории журналов транзакций:

 

рис.10

Можно было не ходить в эксплорер, а посмотреть, какие файлы относятся к базе, при помощи системного представления sys.database_files или хранимой процедуры sp_helpfile.

рис.11

 

Теперь рассмотрим, где хранится информациях об этих путях. Эта информация хранится в реджистри внутри каждого экземпляра. Установленные на машине экземпляры перечислены в ключе HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL. Параметрами внутри ключа являются идентификаторы экземпляров. Мы помним (рис.2), что MSSQLSERVER – это идентификатор экземпляра по умолчанию. Значением каждого параметра является ключ, ведущий к детальной информации об этом экземпляре.

рис.12

Например, экземпляру с идентификатором MSSQLSERVER соответствует значение MSSQL10.MSSQLSERVER, что означает, что информация об этом экземпляре хранится под ключом HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER. В частности, дефолтный каталог для файлов данных пользовательских БД находится в подключе \MSSQLServer\DefaultData:

 

рис.13

На этом основании некоторые товарищи в своих рекомендациях, не мудрствуя лукаво, пишут: дефолтный каталог данных для пользовательских БД надлежит брать из реджистри по адресу HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\DefaultData. Товарищи ошибаются. Посмотрим в соседний экземпляр SQL Server по имени INSTANCE2:

рис.14

Видите ли вы здесь параметр DefaultData? Правильно, не видите, потому что он появляется, когда дефолтный каталог данных для пользовательских баз (п.1) начинает отличаться от каталога с системными базами (п.3). При установке экземпляра MSSQLSERVER User Database Directory была изменена (рис.5), а при установке INSTANCE2 я этого не делал. Стало быть пока в нем User Database Directory и System Database Directory означают одно и то же, как на рис.4. Параметр DefaultData возникнет, как только между ними возникнет разница. Зайдем в свойства экземпляра INSTANCE2

рис.15

и изменим в Database Settings то, что называется Database Default Locations:

 

рис.16

 

Тотчас в реестре под экземпляром Instance2, т.е. в ключе HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCE2\MSSQLServer возникнет параметр DefaultData (и DefaultLog, коль скоро местоположение вновь создаваемых логов тоже поменялось).

 

рис.17

Теперь значения DefaultData и DefaultLog будут использоваться в качестве путей, по которым будут создаваться файлы БД при создании баз в экземпляре INSTANCE2. Естественно, для каждой базы можно оговорить свои полные имена файлов, из которых она будет состоять, в графическом редакторе

рис.18

или в Т-SQL, но если этого не сделать, то файлы данных будут теперь создаваться в c:\Instance2_DATA, а файлы журналов – в c:\Temp\Instance2_Log.

В связи с тем, что для вновь создаваемых баз можно всегда прописать полные пути ее файлов, Database Default Locations не имеет принципиального значения, если вдруг диск, на котором лежали эти фолдеры, переполнился. Более актуально в этом плане перемещение уже созданных баз. Перемена Database Default Locations не затрагивает уже созданные базы, файлы которых остаются там же, где лежали.

Процедура перемещения пользовательских баз описывается здесь - https://msdn.microsoft.com/ru-ru/library/ms345483.aspx, а системных – здесь: https://msdn.microsoft.com/ru-ru/library/ms345408.aspx. Они отличаются друг от друга тем, что системные базы не надо предварительно загонять в offline, а ради пользовательских не надо перестартовывать SQL Server. Для мастера нужно еще подправить параметры старта SQL Server. Рассмотрим в качестве примера перемещение БД master. На раз делаем alter database, в котором меняем директории (и, возможно, имена) мастеровских файлов:

ALTER DATABASE master MODIFY FILE ( NAME = master , FILENAME = 'c:\temp\master.mdf' )

ALTER DATABASE master MODIFY FILE ( NAME = mastlog , FILENAME = 'c:\temp\mastlog.ldf' )

shutdown

рис.19

На два стопим данный экземпляр SQL Server. Его имя можно видеть на скриншоте внизу в желтой строке. Это дефолтный экземпляр.

На три идем в реджистри и меняем в HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Ключ экземпляра>\MSSQLServer\Parameters первый и третий параметры старта SQL Server, которые содержат полные имена файлов данных и журнала БД master:

рис.20

На четыре переименовываем эти файлы в файловой системе.

На пять стартуем экземпляр. Все.

рис.21

Можно убедиться, что теперь БД master использует новые файлы:

select * from sys.master_files

 

рис.22

Несмотря на то, что каталог с системными базами для экземпляра остался C:\Инстанс по дефолту\MSSQL10.MSSQLSERVER\MSSQL\DATA, БД master там уже нет. Аналогично, можно растащить оттуда по разным местам другие системные базы.

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

declare @path nvarchar(500)

--Определяем имя текущего инстанса

declare @instanceName nvarchar(100)

select @instanceName = isnull(cast(serverproperty('InstanceName') as nvarchar(100)), 'MSSQLSERVER')

 select @instanceName

--Определяем соответствующий инстансу ключ

declare @regInstanceKey nvarchar(500), @s nvarchar(500)

exec xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',

                         @key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\SQL',

                         @value_name = @instanceName,

                         @value = @regInstanceKey out

set @regInstanceKey = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\' + @regInstanceKey

--Читаем из него все параметры с их значениями в таблицу @t

declare @t table (name nvarchar(4000), value sql_variant)

set @s = @regInstanceKey + '\MSSQLSERVER'

insert into @t exec xp_instance_regenumvalues @rootkey = 'HKEY_LOCAL_MACHINE', @key = @s

 select * from @t

select @path = cast(value as nvarchar(500)) from @t where name = 'DefaultData'

--Если параметра с именем DefaultData нет, значит, дефолтная директория юзеровских баз не трогалась

--и совпадает с системной (см. рис.4, 5 - System Database Directory), которая получается из

--подключа @regInstanceKey\Setup, параметр SQLDataRoot и получается прибавлением к нему \Data

if @@rowcount = 0 begin

 set @s = @regInstanceKey + '\Setup'

 exec xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @s, @value_name = 'SQLDataRoot', @value = @path out

 set @path += '\Data'

end

select @path

Скрипт 1

рис.23

В select @instanceName = ... можно воткнуть не автоматическое определение текущего инстанса, а присвоение желательного. Тогда он будет возвращать фолдер данных, в принципе, для любого экземпляра.

Примечание. Расширенные хранимые процедуры для работы с реджистри xp_instance_reg* лежат в xpstar.dll вместе со своими собратьями расширенными хранимыми процедурами xp_reg*:

рис.24

Чем они отличаются внутри себя содержательно, я не знаю, об этом нужно спрашивать Колю с Яном, а с точки зрения вызовов в xp_instance_reg* нужно ставить двойные косяки перед куском ключа, содержащем пробелы. Пример мы уже наблюдали выше:

exec xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',

                         @key = 'SOFTWARE\Microsoft \\ Microsoft SQL Server \\ Instance Names\SQL',

                         @value_name = @instanceName,

                         @value = @regInstanceKey out

Обычно народ этого не знает, просто делает Copy Key Name из regedit и получает NULL. Озабоченно чешет репу, долго проверяет, что copy-paste работает правильно, потом плюет и уходит на xp_reg*. В них пробелы роли не играют:

--exec xp_regread @rootkey = N'HKEY_LOCAL_MACHINE',

--@key = N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',

--@value_name = N'SQLEXPRESS',

--@value = @x out

По большому счету эти процедуры имели значение до 2005-го года, потому что сейчас элементарно написать CLRные функции, реализующие функции работы с реджистри. Например, удобнее написать перечисление параметров внутри ключа в виде TVF, чем делать insert ... exec процедуры, поскольку возможность делать select из хранимой процедуры до сих пор не реализована. В принципе, даже перечислять ничего не нужно, достаточно проверить существование параметра с заданным именем под заданным ключом и вытащить его значение, если существует. Предоставляется читателям в качестве самостоятельного упражнения.

Местоположение системных баз, как было видно на рис.22, получается из системного представления sys.master_files. Каждую базу можно со временем унести в другое место, поэтому правомерно говорить о фолдере, в котором на данный момент проживают данные или лог конкретной системной базы, нежели об их централизованном хранении. Скажем, появившаяся в 2005-м mssqlsystemresource с самого начала держится особняком. Например, если нужно получить фолдер, в котором лежат данные БД msdb, это делается так:

declare @path nvarchar(4000)

select @path = physical_name from sys.master_files where database_id = db_id('msdb') and type_desc = 'ROWS'

declare @n int = len(@path) - charindex('\', reverse(@path)) + 1 --определяем последнее вхождение \

select left(@path, @n - 1) --собсно, результат

Скрипт 2

 

Местоположение БД master, как мы видели (рис.20), можно также взять из реджистри, равно как и местоположение Error Loga.

БД mssqlsystemresource по умолчанию лежит не в Data, а в binn (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Ключ экземпляра>\Setup, SQLBinRoot).

 

Предупреждение.

Обращаться с реджистри нужно осторожно, чтобы случаем там чего не запортить. В SSMS не рекомендуется без надобности делать exec sp_MSforeachdb 'DROP DATABASE ?', в командной строке – format c:, на кухне – проливать горячий кофе себе на штаны и выше, а также стирать домашних животных в стиральной машине, сушить в микроволновке и пр.