Где хранятся файлы базы данных MS SQL Server?

Системные базы данных в MS SQL Server 2012 / 2008

В рамках данной статьи рассмотрим системные базы данных MS SQL Server 2012. Ниже приводится их подробное описание, расположение, а также разбираются вопросы о необходимости резервного копирования системных баз данных.

0. Оглавление

Краткое описание системных баз данных
Подробное описание системных баз данных
Просмотр и редактирование свойств системных баз данных

1. Краткое описание системных баз данных

В процессе установки компоненты Database Engine MS SQL Server генерируются следующие системные базы данных:

Системная база данных	Описание
master	В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
tempdb	Рабочее пространство для временных объектов или взаимодействия результирующих наборов.
msdb	Используется агентом SQL Server для планирования предупреждений и задач.
model	Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
resource	Системные объекты физически хранятся в базе данных resource, но логически отображаются в схеме sys любой базы данных.

2. Подробное описание системных баз данных

Рассмотрим каждую из системных баз данных более подробно:

База данных master — наиболее важная системная база данных Databse Engine. Она содержит в себе всю системную информацию, в том числе общие для всего экземпляра метаданные, такие как сведения об учетных записях входа, конечных точках и связанных серверах, а также параметры конфигурации системы. Кроме того, база данных master содержит информацию обо всех других базах данных. Здесь же SQL Server хранит сведения об инициализации. Таким образом, если база данных master станет недоступна, запустить SQL Server будет невозможно.

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

База данных tempdb — служит рабочим пространством для временных объектов или взаимодействия результирующих наборов. Она доступна всех пользователям подключенных к данному экземпляру SQL Server и используется всеми базами данных, принадлежащих экземпляру SQL Server. База данных tempdb пересоздается при каждом запуске SQL Server. Поэтому в базе данных tempdb ничего не сохраняется от одного сеанса SQL Server до следующего. В данной базе данных содержатся следующие объекты:
- временные объекты, созданные явно, такие как глобальные или локальные временные таблицы, временные хранимые процедуры, табличные переменные и курсоры;
- внутренние объекты, создаваемые компонентой Database Engine, например рабочие таблицы, хранящие промежуточные результаты буферов или сортировки, промежуточные результаты вычислений сложных выражений;
- изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;
- изменения данных для таких функциональных возможностей, как операции с индексами в сети, множественные активные результирующие наборы (режим MARS) и триггеры AFTER.

Резервное копирование: Операции резервного копирования и восстановления базы данных tempdb запрещены.

Замечание: База данных tempdb используется системой довольно активно. По этой причине необходимо убедиться, что объем памяти для этой базы данных достаточно большой. Также можно получить значительный прирост производительности SQL Server путем выноса базы tempdb на отдельный дисковый накопитель, или более быстрый SSD-диск.

База данных model — используется в качестве шаблона для всех баз данных, которые будут создаваться в данном экземпляре SQL Server. Поскольку база данных tempdb создается каждый раз при запуске SQL Server, база данных model всегда должна существовать в системе SQL Server. Все содержимое базы данных model, включая параметры базы данных, копируется в создаваемую базу данных. Соответственно, возможно изменять данную базу данных, добавляя в нее объекты или полномочия. После этого все новые базы данных будут наследовать и эти новые свойства.

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

База данных msdb — используется агентом SQL Server для создания расписания предупреждений и заданий, а также другими компонентами, такими как среда «Среда SQL Server Management Studio», компонента «Service Broker«» и «Database Mail» и др. Например, SQL Server хранит полный журнал резервного копирования и восстановления в базе данных msdb. В эти сведения включено имя стороны, выполнившей резервное копирование, время резервного копирования и устройства или файлы, в которых храниться резервная копия. Среда «SQL Server Management Studio» использует эти сведения для создания плана восстановления базы данных и применения существующих резервных копий журнала транзакций. События резервного копирования для всех баз данных записываются, даже если они создаются средствами пользовательских приложений или сторонних разработчиков.

Резервное копирование : Следует делать резервную копию базы данных msdb, после каждого изменения параметров агента SQL Server.

База данных resource — это доступная только для чтения база данных, которая содержит все системные объекты, включенные в SQL Server. Такие системные объекты SQL Server, как sys.objects, физически хранятся в базе данных resource, но логически присутствуют в схеме sys каждой базы данных. База данных resource не содержит пользовательских данных или метаданных. Содержимое этой базы данных обычно используется для системных обновлений.

Резервное копирование : Резервное копирование базы данных resource не предусмотрено.

3. Просмотр и редактирование свойств системных баз данных

Для того, чтобы просмотреть или изменить свойства системных баз данных, а также узнать расположение файлов баз данных необходимо подключиться к данному экземпляру SQL Server с помощью утилиты «SQL Server Management Studio». В окне программы, раскрыв в дереве обозревателя объектов (Object Explorer) вкладки «Базы данных» (Databases) — «Системные базы данных» (System databases) можно найти все системные базы данных данных за исключением базы resource, которая скрыта от пользователя. Для того чтобы просмотреть параметры конкретной базы данных, необходимо кликнуть по ней правой кнопкой мыши и в контекстном меню выбрать «Свойства» (Properties).

В открывшемся окне можно просмотреть или изменить различные свойства базы данных, располагающиеся на соответствующих вкладках. Например, на вкладке «Файлы» (Files) можно узнать владельца базы данных или путь расположения файлов базы.

О том как изменить каталог расположения файлов системной базы данных tempdb я писал здесь.

Файлы базы данных resource имеют имена mssqlsystemresource.mdf и mssqlsystemresource.ldf и располагаются в каталоге с установленным экземпляром SQL Server. По умолчанию это
C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLBinn.

Смотрите также:

Ниже приведена пошаговая инструкция, показывающая как добавить новую базу данных в Microsoft SQLServer 2012 (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен). Запускаем…

Системная база данных tempdb служит рабочим пространством для хранения временных объектов, таких как временные таблицы, промежуточные результаты вычислений, временные хранимые процедуры, результаты буферов и сортировки, внутренние объекты, создаваемые компонентой Database…

Раннее я уже писал о создании резервных копий в MS SQL Server 2012. В данной статье подробно рассмотрим процессе восстановления базы данных из имеющейся резервной копии (резервных копий) в MS SQL…

Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server

Применимо к: SQL Server (все поддерживаемые версии) — только Windows Управляемый экземпляр SQL Azure

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

Для экземпляра SQL Server , включающего Компонент Database Engine, Службы Analysis Servicesи Службы Reporting Services, каждый компонент имеет полный набор файлов данных и исполняемых файлов, а также общие файлы, используемые всеми компонентами.

Чтобы изолировать друг от друга папки установки, формируется уникальный идентификатор экземпляра для каждого из компонентов экземпляра SQL Server.

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

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

Системные базы данных (Master, Model, MSDB и TempDB) и пользовательские базы данных компонента Компонент Database Engine можно установить с использованием протокола SMB в качестве хранилища файлового сервера Server Message Block (SMB). Это относится как к изолированному варианту установки SQL Server , так и к установке кластеров отработки отказа SQL Server . Дополнительные сведения см. в разделе Установка SQL Server с общей папкой SMB в качестве хранилища.

Не удаляйте следующие каталоги или их содержимое: Binn, Data, Ftdata, HTML или 1033. При необходимости можно удалить другие каталоги, однако возможно, что не удастся вернуть утраченную функциональность или восстановить потерянные данные без удаления и повторной установки SQL Server. Не удаляйте и не изменяйте HTM-файлы в каталоге HTML. Они необходимы для правильной работы средств SQL Server .

Общие файлы для всех экземпляров SQL Server

Общие файлы, используемые всеми экземплярами на одном компьютере, устанавливаются в папку :Program FilesMicrosoft SQL Servernnn. — это буква диска, на который устанавливаются компоненты. Обычно по умолчанию диск C. Значение nnn определяет версию. В следующей таблице перечислены версии для путей. — значение версии, используемое в идентификаторе экземпляра, и путь реестра.

Версия	nnn
SQL Server 2019 (15.x)	150	15
SQL Server 2017 (14.x);	140	14
SQL Server 2016 (13.x);	130	13
SQL Server 2014 (12.x)	120	12
SQL Server 2012 (11.x)	110	11

Расположение файлов и сопоставление данных реестра

Во время установки SQL Server для каждого компонента сервера создается идентификатор экземпляра. В этой версии SQL Server сервер состоит из компонента Компонент Database Engine, служб Службы Analysis Servicesи Службы Reporting Services.

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

Для компонента Компонент Database Engine— MSSQL, за которым следуют основной номер версии, символ подчеркивания и дополнительный номер версии (если применимо), затем точка и имя экземпляра.

Для служб Службы Analysis Services— MSAS, за которым следуют основной номер версии, символ подчеркивания и дополнительный номер версии (если применимо), затем точка и имя экземпляра.

Для служб Службы Reporting Services— MSRS, за которым следуют основной номер версии, символ подчеркивания и дополнительный номер версии (если применимо), затем точка и имя экземпляра.

Ниже приведены примеры идентификаторов экземпляров по умолчанию для данной версии SQL Server .

MSSQL.MSSQLSERVER — экземпляр SQL Server по умолчанию.

MSAS.MSSQLSERVER — экземпляр по умолчанию служб SQL Server Analysis Services.

MSSQL.MyInstance — именованный экземпляр SQL Server с именем «MyInstance».

Именованный экземпляр SQL Server , в состав которого входит компонент Компонент Database Engine и службы Службы Analysis Services, имеет имя «MyInstance» и устанавливается каталоге по умолчанию, имеет следующую структуру каталогов.

C:Program FilesMicrosoft SQL ServerMSSQL.MyInstance

C:Program FilesMicrosoft SQL ServerMSAS.MyInstance

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

Идентификатор экземпляра, отличный от заданного по умолчанию, можно указать во время установки SQL Server . Если пользователь изменяет каталог установки по умолчанию, вместо MicrosoftSQL Server используется MicrosoftSQL Server. Следует заметить, что идентификаторы экземпляров, начинающиеся с символа подчеркивания (_) или содержащие символ решетки (#) или знак доллара ($), не поддерживаются.

Службы Integration Services и клиентские компоненты не привязаны к экземпляру, поэтому им не присваивается идентификатор экземпляра. По умолчанию компоненты, не привязанные к экземпляру, устанавливаются в один каталог: :Program FilesMicrosoft SQL Servernnn. Изменение пути установки для одного компонента приводит к его изменению и для всех остальных компонентов. При последующих установках компоненты, не зависящие от экземпляра, устанавливаются в каталог исходной установки.

SQL Server Службы Analysis Services — это единственный компонент SQL Server, который поддерживает переименование экземпляра после установки. При переименовании экземпляра служб Службы Analysis Services его идентификатор экземпляра не изменится. После переименования экземпляра в каталогах и разделах реестра по-прежнему используется идентификатор экземпляра, созданный во время установки.

В разделе реестра HKLMSoftwareMicrosoftMicrosoftSQL Server создается куст для компонентов, привязанных к экземпляру. Например,

В реестре также хранится сопоставление идентификаторов экземпляров с именами экземпляров. Сопоставление идентификатора экземпляра с именем экземпляра осуществляется следующим образом:

[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoftSQL ServerInstance NamesSQL] » «=»MSSQL«

[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoftSQL ServerInstance NamesOLAP] » «=»MSAS«

[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoftSQL ServerInstance NamesRS] » «=»MSRS«

Указание путей к файлам

В ходе установки вы можете изменить путь установки для следующих компонентов:

Путь установки отображается в программе установки только для компонентов с пользовательской целевой папкой.

Компонент	Путь по умолчанию	Настраиваемый или фиксированный путь
Компонент Database Engine компоненты сервера	Program FilesMicrosoftSQL ServerMSSQL.	Настраивается
Компонент Database Engine файлы данных	Program FilesMicrosoftSQL ServerMSSQL.	Настраивается
Службы Analysis Services сервер	Program FilesMicrosoftSQL ServerMSAS.	Настраивается
Службы Analysis Services файлы данных	Program FilesMicrosoftSQL ServerMSAS.	Настраивается
Службы Reporting Services сервер отчетов	Program FilesMicrosoftSQL ServerMSRS. Reporting ServicesReportServerBin	Настраивается
Службы Reporting Services диспетчер отчетов	Program FilesMicrosoftSQL ServerMSRS. Reporting ServicesReportManager	Фиксированный путь
Службы Integration Services	nnnDTS 1	Настраивается
Клиентские компоненты (за исключением bcp.exe и sqlcmd.exe)	nnnTools 1	Настраивается
Клиентские компоненты (bcp.exe и sqlcmd.exe)	Client SDKODBCnnnToolsBinn	Фиксированный путь
Объекты COM для репликации и размещения на сервере	:Program FilesMicrosoft SQL ServernnnCOM 2	Фиксированный путь
Службы Integration Services библиотеки DLL служб для механизмов преобразования данных в реальном режиме времени и конвейерного преобразования данных и программа командной строки dtexec	:Program FilesMicrosoft SQL ServernnnDTSBinn	Фиксированный путь
Библиотеки DLL, которые обеспечивают управляемое соединение, поддерживаемое для служб Службы Integration Services	:Program FilesMicrosoft SQL ServernnnDTSConnections	Фиксированный путь
Библиотеки DLL для каждого типа перечислителей, которые поддерживают службы Службы Integration Services	:Program FilesMicrosoft SQL ServernnnDTSForEachEnumerators	Фиксированный путь
SQL Server , поставщики инструментария WMI	:Program FilesMicrosoft SQL ServernnnShared	Фиксированный путь
Компоненты, которые разделены между всеми экземплярами SQL Server	:Program FilesMicrosoft SQL ServernnnShared	Фиксированный путь

Убедитесь, что папка Program FilesMicrosoftSQL Server защищена через ограничение разрешений.

Обратите внимание, что диск по умолчанию для расположений файлов — systemdrive, обычно это диск C. Пути установки вложенных компонентов определяются путем установки родительского компонента.

1 Используется общий путь установки для Службы Integration Services и клиентских компонентов. Изменение пути установки для одного компонента влечет изменение пути для других компонентов. При последующих установках компоненты устанавливаются в расположение исходной установки.

2 Этот каталог используется всеми экземплярами SQL Server на компьютере. При применении обновления к любому из экземпляров на компьютере все файловые изменения коснутся каждого из них. При добавлении компонентов в существующую конфигурацию невозможно ни изменить расположение ранее установленного компонента, ни указать расположение нового. Необходимо либо установить дополнительные компоненты в каталоги, созданные программой установки, либо удалить продукт и установить его заново.

Для кластеризованных конфигураций необходимо выбрать локальный диск, доступный на всех узлах кластера.

При задании пути установки во время установки компонентов сервера или файлов данных программа установки использует идентификатор экземпляра в дополнение к заданному положению для программ и файлов данных. Программа установки не пользуется идентификаторами экземпляров для средств и других общих файлов. Идентификатор экземпляра также не используется для программ и файлов данных служб Службы Analysis Services , но используется для репозитория служб Службы Analysis Services .

При указании пути установки для компонента Компонент Database Engine программа установки SQL Server использует этот путь в качестве корневого каталога этой установки для всех папок, относящихся к экземпляру, включая файлы данных SQL. Если в этом случае в качестве корневого каталога указать «C:Program FilesMicrosoftSQL ServerMSSQL. MSSQL», то относящиеся к экземпляру каталоги добавляются в конец этого пути.

Поэтому при использовании функции обновления USESYSDB в мастере установки SQL Server (режим установки с пользовательским интерфейсом) можно попасть в ситуацию, когда продукт окажется установленным в рекурсивной структуре папок. Например, MSSQL14MSSQLMSSQL10_50MSSQLData. Поэтому при использовании функции USESYSDB вместо компонента Компонент Database Engine необходимо указывать путь установки файлов данных SQL.

Обычно файлы данных можно найти в дочернем каталоге с именем Data. Например, если при обновлении вы укажете «C:Program FilesMicrosoftSQL ServerMSSQL. » в качестве корневого каталога данных для системных баз данных, то файлы данных должны располагаться в каталоге «C:Program FilesMicrosoftSQL ServerMSSQL. MSSQLData».

Где хранятся файлы базы данных MS SQL Server?

Главные и дополнительные файлы баз данных в SQL Server 2005, применение неформатированных разделов, оптимальное размещение файлов баз данных и журналов транзакций

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

Вначале приведем немного общих сведений.

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

В каждой базе данных обязательно есть один главный ( primary ) файл. По умолчанию для него используется расширение mdf (хотя использовать именно такое расширение не обязательно — для любых файлов баз данных и журналов транзакций расширения могут быть любыми, а могут и отсутствовать). Удалять этот файл нельзя. Для базы данных можно создать и дополнительные файлы ( secondary ), для которых по умолчанию используется расширение ndf . Точно так же есть главный и дополнительные файлы у журналов транзакций, для них по умолчанию используется расширение ldf .

В принципе, база данных может вообще обходиться без файлов. Вся необходимая информация при этом будет храниться на неформатрированном диске. Такой вариант называется использованием неформатированных разделов ( raw partitions ). Однако, в отличие от связки Unix / Oracle , связка Windows / SQL Server ничего не выигрывает от применения неразмеченных разделов, и поэтому этот вариант используется редко.

А теперь подробнее остановимся на тех решениях, которые вам придется принимать при создании базы данных.

Первое решение — это где будут размещены файлы баз данных и журналов транзакций. По умолчанию и файлы баз данных, и файлы журналов помещаются в каталог С:Program FilesMicrosoft SQL ServerMSSQL.XMSSQLData (где X — номер экземпляра SQL Server 2005). Такое размещение, конечно, не оптимально.

Идеальный вариант, с точки зрения размещения файлов баз данных, — поместить их на отдельный внешний аппаратный RAID -массив. Причем на этом RAID -массиве не должно быть ничего, кроме файлов базы данных, и, кроме того, на нем должно быть как минимум 50% пустого пространства. Такой вариант обеспечивает ряд преимуществ:

q RAID -массив (в зависимости от выбранного уровня) обеспечивает высокую производительность и отказоустойчивость;

q внешний RAID -массив делает процесс восстановления больших баз данных предельно простым и быстрым. Если на сервере возникли какие-то проблемы, а с файлами базы данных все в порядке, достаточно просто подключить RAID -массив к другому серверу и присоединить базу данных (см. разд. 4.2.3);

q если вы выберете внешний RAID -массив, который входит в список совместимого оборудования ( Hardware Compatibility List ) для кластеров Windows Server , то при необходимости вы сможете еще больше повысить отказоустойчивость за счет создания кластера;

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

Конечно, внешний RAID -массив — это идеальный вариант. Но на многих предприятиях денег на него может просто не быть. В этом случае рекомендуется, по крайней мере, использовать для файлов баз данных отдельный быстрый жесткий диск. Категорически не рекомендуется помещать на тот же диск, где находятся файлы баз данных, программные файлы операционной системы и SQL Server . Помните также, что на контроллерах доменов для разделов, на которые помещается база данных Active Directory (по умолчанию она находится в каталоге C:WindowsNTDS ), отключается кэширование на запись: падение производительности может быть просто устрашающим.

Конечно же, никогда нельзя сжимать рабочие файлы баз данных средствами NTFS или помещать их на сжатые диски.

Теперь — о размещении файлов журналов транзакций.

Требования по производительности к ним намного меньше, чем к файлам баз данных (одна из причин связана с тем, что в файлы журналов транзакций записываются только изменения, которые вносятся в базу данных — команды SELECT на них, за исключением специальных случаев, влияния не оказывают). Как размещать эти файлы, зависит от требований к базе данных и бюджета вашего предприятия. Далее представлены разные варианты, начиная от наиболее желательного и заканчивая наименее удачным:

q второй RAID -массив;

q отдельный набор дисков на том же RAID -массиве, что и файлы баз данных;

q два обычных диска, которые зазеркалированы по отношению друг к другу;

q просто обычный отдельный диск;

q размещение на том же диске, на котором размещены файлы баз данных (этот вариант наименее желателен, но именно он по умолчанию выбирается SQL Server 2005 в расчете на однодисковые системы).

Главное, что вам нужно постараться обеспечить, — это размещение журналов транзакций на другом физическом диске по отношению к файлам баз данных. Если вы полните это условие, то в случае отказа жесткого диска сможете провести восстановление на момент сбоя (конечно, при условии, что резервное копирование все-таки производилось). Если же файлы баз данных и журналов транзакций находились на одном диске, и этот диск отказал, восстановиться базу данных удастся только на момент создания последней резервной копии. В этом случае пользователям придется заново вносить данные, например, за последний день или за последнюю неделю — в зависимости от того, когда последний раз производилось резервное копирование.

Второе решение, которые вы должны принять при создании базы данных, — выбор размера файлов баз данных и журналов транзакций.

Конечно, размер файлов баз данных полностью зависит от задачи, для которой используется эта база данных. Однако у вас есть выбор — сразу создать большие файлы баз данных или настроить для них режим автоматического приращения, когда файлы при необходимости будут автоматически увеличиваться. Если такая возможность имеется, всегда нужно с самого начала создавать файлы максимального размера (или, по крайней мере, настраивать автоприращение сразу большими частями, например, в несколько Гбайт), даже несмотря на то, что в течение продолжительного времени значительная часть этих файлов использоваться не будет. Аргументация здесь проста — таким образом вы снижаете фрагментацию файлов баз данных, повышая производительность. По умолчанию для файлов баз данных настраивается худший вариант — автоприращение маленькими «порциями»: 1 Мбайт для файлов базы данных и 10% от существующего размера для файлов журналов транзакций.

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

Настройку режима автоприращения при помощи графического интерфейса можно выполнить в окне New Database при создании новой базы данных или на вкладке General свойств базы данных в SQL Server Management Studio . Режим автоприращения устанавливается в соответствующей строке для каждого файла базы данных нажатием на кнопку в столбце Autogrowth (Автоматический рост) свойств данного файла (см. рис. 4.1).

Если вы уже создавали файлы баз данных большого размера (гигабайты и десятки гигабайт) в SQL Server предыдущих версий, то могли заметить, что их создание требует довольно длительного времени. Связано это было с тем, что SQL Server предыдущих версий «форматировал» пространство внутри создаваемых файлов данных, заполняя его двоичными нулями. В SQL Server 2005 появилась новая возможность, которая называется немедленной инициализацией файлов ( instant file initialization ). Она позволяет не заполнять файлы данных нулями, что резко сокращает время, требуемое для создания файлов баз данных или их увеличения. Однако эта возможность используется только при двух условиях:

q SQL Server работает под управлением операционной системы Windows Server 2003 или Windows XP ;

q учетная запись, от имени которой работает SQL Server , обладает специальной привилегией операционной системы SE_MANAGE_VOLUME_NAME (по умолчанию такая привилегия есть у встроенной группы Administrators ).

Эти же самые принципы относятся и к настройке автоприращения файлов журналов транзакций (заметим только, что немеделенная инициализация файлов при создании файлов журналов не используется). Однако для файлов журналов транзакций возникает еще один вопрос: а какой размер журнала транзакций нужен для конкретной базы данных? Ответ на этот вопрос зависит от множества факторов:

q первый фактор — в каком режиме используется база данных. Если это база данных OLTP (т. е. данные в ней изменяются постоянно, чаще всего пользователями при помощи клиентских приложений), к которой относится абсолютное большинство используемых на предприятиях баз данных, то Microsoft рекомендует устанавливать для журналов транзакций размер от 10 до 25% от общего размера файлов баз данных. Для баз данных Data Warehouse (архивные хранилища, которые в обычном режиме используются только на чтение и пополняются, как правило, средствами массовой загрузки данных или пакетами SSIS / DTS ) достаточно будет и нескольких процентов от объема файлов баз данных;

q второй фактор — какой режим восстановления настроен для базы данных. Режим восстановления ( recovery model ) настраивается на вкладке Options свойств базы данных, подробнее о нем будет рассказано в разд. 4.5. Режим восстановления S imple предъявляет минимальные требования к размеру файлов журнала (поскольку старые записи в журнале сразу же перезаписываются), а режим восстановления F ull требует файлов журнала намного большего размера;

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

Где хранятся базы данных sql server

И ногда бывает нужно определить путь к базе данных в MS SQL Server Express . Сделать это совсем несложно .

Просто в Server Management Studio становимся на нужную БД , в контекстном меню выбираем Свойства :

. и жмём на пункт Файлы формы Свойств базы данных :

Нужную нам информацию о локализации БД узнаём из информации в столбце Путь :

Поставленную задачу по определению физической локализации можно также выполнить и с помощью команды

Еще , возможно , несколько полезных советов для пользователей по работе с MS SQL Server можно посмотреть здесь

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

Сбой и восстановление базы данных TempDB

TempDB – это системная база для временных таблиц. При запуске службы сервера БД, база данных TempDB создаётся заново. В случае выхода из строя накопителя, на котором размещаются файлы базы TempDB, служба не будет запущена, так как этот накопитель более недоступен и создать базу TempDB SQL Server не сможет. Чтобы это исправить, можно установить новый накопитель и назначить для него в системе ту же букву, что была у потерянного накопителя (при этом полный путь к файлам базы TempDB должен быть восстановлен: нужно создать соответствующие каталоги).
В случае, если путь к файлам базы TempDB восстановить не представляется возможным, нужно указать серверу БД другой путь (существующий) для этой базы. Чтобы это сделать необходимо:

1) Запустить службу сервера БД в минимальной конфигурации из командной строки:
Sqlservr.exe -m -c -f -T3608 -T4022

Нужно указывать полный путь к файлу sqlservr.exe (или запустить CMD.EXE из каталога, где лежит этот файл):
«C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVE RMSSQLBinnsqlservr.exe » -m -c -f -T3608 -T4022

Описание параметры запуска:
-m: запускает SQL Server в однопользовательском режиме, Контрольная точка не срабатывает;
-с: ускоряет запуск из Командной строки. Запускается в отдельном окне как приложение, а не как служба;
-f: запускает SQL Server в минимальной конфигурации;
-T: включает определённый флаг трассировки. 3608: запрещает автоматически запускать и восстанавливать все БД, кроме master (используется для перемещения системных БД).
4022: обход автоматически запускаемых процедур.

2) Подключиться к запущенному серверу БД с помощью программы sqlcmd.exe:

2.1) Переход в каталог с утилитами SQL Server:
cd «C:Program FilesMicrosoft SQL Server100ToolsBinn»
2.2) Запуск sqlcmd с параметрами подключение к серверу под sa
sqlcmd -S Server -U sa -P 111

Описание параметров sqlcmd:
-S: имя сервера БД к которому происходит подключение;
-U: Логин (имя входа) под которым происходит подключение к серверу;
-P: пароль.

3) Изменить пути к файлам БД TempDB с помощью T-SQL (указать новые существующие пути):

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘С:TempDB empdb.mdf’) ;
GO
Ответ сервера на успешный запрос:
Файл «tempdb» был изменен в системном каталоге. Данный новый путь будет использ
ован при следующем запуске этой базы данных.

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘C:TempDB emplog.ldf’) ;
GO

Ответ сервера на успешный запрос:
Файл «templog» был изменен в системном каталоге. Данный новый путь будет использ
ован при следующем запуске этой базы данных.

Для проверки изменений можно выполнить следующий запрос:
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N’tempdb’);
GO

4) Закрыть sqlcmd и командное окно сервера SQL Server через нажатие клавиш Ctrl+C;

5) Запустить сервер SQL Server в нормальном режиме (через запуск службы).

Сбой и восстановление баз данных msdb и model

msdb – это системная база, где хранится вся информация по заданиям, расписаниям, история резервных копий для всех БД и т.д. Нужна для работы службы SQL Server Agent.
model – это системная база, используется, как шаблон для всех создаваемых пользовательских БД.
В случае сбоя этих системной базы, а также в случае перестроения системной БД master необходимо выполнить восстановления этих баз из резервных копий. Поэтому необходимо постоянно создавать резервные копии этих баз (особенно базы msdb), рекомендуется создавать полные копии после каждого изменения.

Для восстановления системных баз msdb и model необходимо выполнить скрипт T-SQL (через панель объектов в Management Studio не получится), его можно выполнить либо в Management Studio, либо через утилиту sqlcmd.exe.

Скрипт для восстановления БД msdb (аналогичный скрипт будет работать и для БД model):

RESTORE DATABASE [msdb] FROM DISK = N’D:BackupSYSmsdb.rez’ WITH FILE = 1,
MOVE N’MSDBData’ TO N’C:MSDBData.mdf’,
MOVE N’MSDBLog’ TO N’C:MSDBLog.ldf’,
NOUNLOAD, REPLACE
GO

Дополнительный материал:
Вопросы восстановления БД msdb и model:
http://msdn.microsoft.com /ru-ru/library/ms190749.a spx
Вопросы резервного копирования и восстановления системных баз:
http://msdn.microsoft.com /ru-ru/library/ms190190.a spx

Сбой и восстановление базы данных master

master – это главная системная база SQL Server, в ней хранится вся конфигурация сервера и конфигурация подключенных БД. При сбое этой базы сервер SQL Server не может быть запущен (даже в минимальной конфигурации).
При попытке запустить Сервер БД в журнале событий приложения появятся ошибка:
Во время запуска при открытии файла «C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAma ster.mdf» для получения данных конфигурации произошла ошибка 2(Не удается найти указанный файл.). Возможно, ошибка вызвана неверным параметром запуска. Проверьте параметры запуска и исправьте или удалите их при необходимости.

Для восстановления работоспособности БД master (и всего сервера БД) нужно выполнить:

1. Перестроение системных БД
Для выполнения этой операции нужен дистрибутив SQL Server 2008. Нужно запустить инсталлятор setup.exe со специальными параметрами:

Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=ИмяЭкземпляра /SQLSYSADMINACCOUNTS=accounts /SAPWD= НовыйПарольsa ]
Параметры:
/QUIET – программа будет работать без видимого интерфейса;
/ACTION=REBUILDDATABASE – программа заново создаст системные базы данных;
/INSTANCENAME – имя экземпляра SQL Server, по умолчанию MSSQLSERVER / SQLSYSADMINACCOUNTS — учетные записи Windows (или группы безопасности), которые будут администраторами (права sysadmin) на этом экземпляре. Например: «BUILTINAdministrators».
/SAPWD – новый пароль для системного пользователя sa.

Выполнять эту команду следует из командной строки(cmd.exe), чтобы отслеживать процесс восстановления (если будут ошибки, то они появятся в виде сообщений в этом же окне). В случае успешного выполнения этой операции в командной строке не будет сообщений.

2. Восстановление системной БД master из резервной копии

Для восстановления БД master необходимо запустить сервер SQL Server в однопользовательском режиме (сначала нужно остановить службу MSSQLSERVER):

Sqlservr.exe -m –c
Нужно указывать полный путь к файлу sqlservr.exe (или запустить CMD.EXE из каталога, где лежит этот файл):
«C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVE RMSSQLBinnsqlservr.exe » -m –c

Затем нужно подключиться к серверу под sa (можно из Management Studio) и выполнить инструкция T-SQL:

RESTORE DATABASE [master] FROM DISK = N’D: BackupSYSmaster.rez’ WITH FILE = 1 WITH REPLACE
GO

3. После восстановления БД master необходимо восстановить системные БД msdb и model (см. выше).

Создание и удаление базы данных

Создание базы данных

Для создания базы данных используется команда CREATE DATABASE .

Чтобы создать новую базу данных откроем SQL Server Management Studio. Нажмем на назначение сервера в окне Object Explorer и в появившемся меню выберем пункт New Query .

В центральное поле для ввода выражений sql введем следующий код:

Тем самым мы создаем базу данных, которая будет называться «usersdb»:

Для выполнения команды нажмем на панели инструментов на кнопку Execute или на клавишу F5. И на сервере появится новая база данных.

После создания базы даных, мы можем установить ее в качестве текущей с помощью команды USE :

Прикрепление базы данных

Возможна ситуация, что у нас уже есть файл базы данных, который, к примеру, создан на другом компьютере. Файл базы данных представляет файл с расширением mdf, и этот файл в принципе мы можем переносить. Однако даже если мы скопируем его компьютер с установленным MS SQL Server, просто так скопированная база данных на сервере не появится. Для этого необходимо выполнить прикрепление базы данных к серверу. В этом случае применяется выражение:

В качестве каталога для базы данных лучше использовать каталог, где хранятся остальные базы данных сервера. На Windows 10 по умолчанию это каталог C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA . Например, пусть в моем случае файл с данными называется userstoredb.mdf. И я хочу этот файл добавить на сервер как базу данных. Вначале его надо скопировать в выше указанный каталог. Затем для прикрепления базы к серверу надо использовать следующую команду:

После выполнения команды на сервере появится база данных contactsdb.

Удаление базы данных

Для удаления базы данных применяется команда DROP DATABASE , которая имеет следующий синтаксис:

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

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

Support » Поддержка Digispot II

Стартовая страница
Оглавление
История страниц

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

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

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

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

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

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

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

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

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

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

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

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

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

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

detach_db2.PNG Просмотреть (31,7 КБ) Станислав Середницкий, 22/03/2018 17:27

detach_db.PNG Просмотреть (62,9 КБ) Станислав Середницкий, 22/03/2018 17:28

detach_db3.PNG Просмотреть (87,3 КБ) Станислав Середницкий, 22/03/2018 17:56

Базы данных, ау! Или где хранятся данные MySQL на вашем компьютере

Дата публикации: 2016-04-05

От автора: а что это вы под ногами ищете? Базу данных? Поверьте, скорее все, она никуда не делась, и лежит «целехонькая» на вашем ПК или сервере! Там уже смотрели? Наверное, вы просто не знаете, где хранятся данные MySQL. Об этом мы вам сегодня и расскажем.

Импорт-экспорт данных в MySQL

Чаще всего найти утерянные базы данных нужно для импорта или копирования. К счастью, для этого не обязательно знать место их «дислокации» на дисковом пространстве. Программные интерфейсы для работы с СУБД MySQL имеют встроенные средства переноса и создания копий БД и таблиц.

В наших «широтах» самой востребованной остается оболочка phpMyAdmin, предназначенная для администрирования системы управления БД MySQL. На примере этого веб-приложения мы и рассмотрим встроенные средства, которые позволят нам остаться в неведении, где находится MySQL.

В phpMyAdmin создание копий баз данных происходит через вкладку «Экспорт». Для дублирования БД или таблицы слева в списке выберете нужный для копирования элемент. После этого перейдите в раздел «Экспорт». В окне параметров в подразделе «Способ экспорта» должно быть установлено значение «Обычный». Затем в списке выделите базу данных (таблицу). После чего нажимаете в самом низу окна «Ок».

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

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

Место хранения оригиналов баз

Знание встроенного функционала phpMyAdmin поможет быстро создать копии нужных данных. Но если на компьютере не установлена ни одна из программных оболочек для администрирования СУБД. А вам нужно перенести БД на другой сервер. Или изменить хранящуюся в таблицах информацию. При этом выполнять запросы придется через консоль, где обязательно прописывается путь к БД. Как видите знать, где хранится база данных MySQL, важно и нужно.

Вот еще один пример «из жизни начинающего админа». Предположим, первоначально вы пользовались Denwer. Но в один «непогожий» день вы нечаянно удалили часть файлов локального сервера. В результате чего он больше не запускается, и вы начинаете «рыть» оставшиеся папки в надежде спасти хотя бы базы данных. Но в этом случает также «удобнее» знать, где копать. Вот пример пути, где в веб-сервере хранятся БД: F:Webserverusrlocalmysql-5.5data, где F – это диск, на котором был установлен Denwer.

Для открытия файлов скопированных БД потребуется phpMyAdmin. Если вы установили сервер СУБД без оболочки, тогда по умолчанию место, где хранятся базы данных MySQL, находится здесь: C:Documents and SettingsAll UsersApplication DataMySQLMySQL Server 5.1data

Файл my.ini

Все описанные методы связаны с осуществлением поиска (в зависимости от варианта использования СУБД) той или иной папки, в которой программы (или сервер) сохраняют созданные базы. Но неужели пользователь никак не может повлиять на это, и самостоятельно указать место на дисковом пространстве MySQL, где хранятся все БД.

Такой вариант настройки был предусмотрен создателями этой системы управления. Все основные параметры задаются в файле my.ini. Он расположен в той же директории, куда установлена и система СУБД.

Данный тип файлов относится к конфигурационным, и открывается с помощью обычного «Блокнота». Чтобы задать «свое» место для всех созданным БД, откройте файл my.ini. Затем найдите в нем раздел [mysqld], и в параметре datadir пропишите путь к нужной папке. После сохранения внесенных изменений (Файл> Сохранить) все существующие базы будут перенесены в указанное вами место файловой системы компьютера или сервера.

Средства SQL

Все описанные выше варианты узнать в MySQL, где лежит БД, являются второстепенными. Потому что СУБД не понимает человеческой речи и письменности. Для общения с ней существует специальный язык SQL. Аббревиатура переводится как язык структурированных запросов (structured query language). То есть с помощью операторов и команд этого языка можно влиять на структуру содержащейся в источнике информации: делать выборки, обновлять данные, вставлять или удалять.

Но не будем слишком забираться в «дремучий лес» профессиональных знаний, нас интересует единственная команда SHOW. Точнее, один из ее вариантов SHOW VARIABLES. Эта команда SQL используется в администрировании для получения служебной информации о конкретном сервере или базе.

SHOW VARIABLES выводит список переменных и их значений из INFORMATION_SCHEMA, которая представляет собой «служебную» БД, в которой содержится информация обо всех базах, подключенных к серверу. Пример запроса: SHOW VARIABLES;

Но из всего этого списка нас интересует лишь одна переменная, с помощью которой можно «узнать» у сервера MySQL, где хранится база. Это переменная basedir, в значение которой записан путь к основной директории. Пример запроса: SHOW VARIABLES LIKE ‘basedir’;

А сейчас прекращайте понапрасну «рыться» в файловой системе своего ПК. Теперь вы точно знаете, как правильно «спросить» у СУБД MySQL, где хранятся базы. Надеемся, нам удалось научить вас этому!

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока