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

Восстановление и предотвращение утери файлов Microsoft SQL Server

Читайте, как восстановить удалённую базу MSSQL используя встроенные в приложение инструменты или сторонние программы. Рассмотрим причины, по которым база может быть утеряна, а также способы восстановления для каждого из них. SQL Server – это система управления реляционными базами данных (СУБД) от Microsoft, которая изначально разрабатывалась компанией как конкурент набиравшим популярность Oracle Database и MySQL.

Основным инструментом интерфейса SQL Server является Microsoft SQL Server Management Studio (SSMS).

Как и большинство СУБД Microsoft SQL Server поддерживает стандарт ANSI SQL. Но, СУБД от Microsoft также использует собственную реализацию стандарта – T-SQL.

Файлы системы Microsoft SQL Server

Файлы базы SQL Server по умолчанию сохраняются на диске С компьютера:

C:\Program Files\Microsoft SQL Server

Причём под каждую базу создаётся отдельная папка с её названием. Например, в нашем случае создано две базы данных Microsoft SQL Server: MSSQL13.SQLEXPRESS, MSSQL13.MSSQLHETMAN.

Данные любой из баз данных MSSQL хранятся в рабочих системных файлах, которых есть три типа:

• *.mdf – это первичный файл данных базы. В таком файле содержатся сведения, которые необходимы для запуска базы, ссылки на другие файлы базы, данные и объекты пользователя. В .mdf файле физически хранятся данные базы.
• *.ndf – вторичные файлы данных базы, которые также используются системой для хранения данных базы.
• *.ldf – файлы журнала транзакций (лог файлы).

Каждый из указанных файлов имеет название базы данных и хранится в папке \DATA:

C:\Program Files\Microsoft SQL Server\Название_Базы_Данных\MSSQL\DATA

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

Причины утери данных MSSQL

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

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

Сбой или выход из строя аппаратного обеспечения. Наиболее частой причиной утери данных базы данных по причине аппаратного обеспечения, является выход из строя дискового накопителя (жесткого диска). Но утерей данных или базы Microsoft SQL Server может также обернутся выход из строя компьютера по любой из причин, во время работы базы данных.

Человеческий фактор. Утеря данных в результате неумышленных действий пользователя или администратора системы.

Способы восстановления базы данных

Есть несколько способов резервирования и восстановления данных базы SQL Server. Использование каждого из них зависит от преследуемой цели: плановое создание бэкапа базы данных или восстановление из него при переносе базы данных на другую машину, или необходимость восстановления данных базы MSSQL в результате её утери или удаления.

Создать копию базы данных для дальнейшего восстановления можно как с помощью встроенных в Microsoft SQL Server Management Studio инструментов, так и вручную. Создание и восстановление базы данных из созданной вручную копии, это более быстрый процесс чем создание и разворачивание резервной копии, но не такой надёжный.

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

Запустите Диспетчер конфигурации SQL Server (Sql Server Configuration Manager)

Выберите Службы SQL Server

В правом окне диспетчера кликните правой кнопкой мыши на базе данных которую необходимо остановить и выберите «Остановить».

Запустить базу данных можно аналогичным образом, выбрав пункт меню «Запустить».

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

• В Transact-SQL:
  SHUTDOWN;
• Из окна командной строки:
  Net stop MSSQLHETMAN
  Net start MSSQLHETMAN
  Где, MSSQLHETMAN – название базы данных

Восстановление удалённой базы с Hetman Partition Recovery

В случае утери или удаления базы данных SQL Server из компьютера, её можно восстановить при условии, что его диск сохранил свою работоспособность. Это можно сделать с помощью программы для восстановления данных жесткого диска Hetman Partition Recovery.

Чтобы восстановить утерянные файлы данных базы MS SQL Server:

Запустите Hetman Partition Recovery и просканируйте с её помощью диск на котором были сохранены файлы данных SQL Server

Перейдите с помощью проводника программы в папку с файлами данных базы

Восстановите необходимые *.mdf, *.ndf, *.ldf файлы данных

Присоедините восстановленные файлы данных к базе SQL Server используя функцию «Присоединить…»

Для этого, войдите в базу данных и кликните правой кнопкой мыши на папке «Базы данных». Выберите меню «Присоединить…» / кнопка «Добавить», после укажите *.mdf файл данных восстановленной базы и нажмите OK.

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

Как создать копию базы SQL Server для дальнейшего восстановления, импорта или переноса

Чтобы избежать утери данных базы MSSQL в случае возникновения непредвиденных обстоятельств, в случае необходимости импорта базы или её переноса из одной машины на другую, в Microsoft SQL Server Management Studio (SSMS) предусмотрен целый ряд инструментов на разные случаи, часть из которых мы уже упоминали в данной статье.

Создать резервную копию… / Восстановить

Чтобы создать резервную копию базы данных, кликните на папке с её названием правой кнопкой мыши и выберите Задачи / Создать резервную копию…

В результате, в папке \Backup

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLHETMAN\MSSQL\Backup

будет создан *.bak файл с резервной копией базы данных.

Чтобы восстановить резервную копию базы данных, кликните на папке с её названием правой кнопкой мыши и выберите Задачи / Восстановить, и укажите путь к файлу резервной копии.

Импорт данных… / Экспортировать данные…

C помощью функции Импорта / Экспорта данных Microsoft SQL Server можно скопировать данные из источника в файл назначения или сервер. Данная функция поддерживает такие источники данных:

• SQL Server
• Microsoft Access
• Microsoft Excel
• Неструктурированные файлы

Другими словами, из SQL Server базы данные можно экспортировать на другой SQL Server или в файл Access, Excel, неструктурированный файл. Из этих же источников можно импортировать данные в SQL Server.

Чтобы экспортировать данные базы, кликните на папке с её названием правой кнопкой мыши и выберите Задачи / «Экспортировать данные…».

После чего укажите с помощью открывшегося Мастера импорта и экспорта SQL Server, Источник и Куда копировать данные.

Импортировать данные в базу можно аналогичным образом, с помощью меню Задачи / «Импорт данных…».

Отсоединить… / Присоединить…

Самым подходящим способом создания копии базы данных для переноса её на другую машину, есть функция Отсоединить… / Присоединить…

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

Такие файлы данных можно перенести в другое удобное пользователю место без риска утери данных из соответствующей базы и подсоединить их к SQL Server другого компьютера (с версией не ниже отсоединённой).

Чтобы отсоединить базу данных, кликните на папке с её названием правой кнопкой мыши и выберите Задачи / «Отсоединить…» / Ok.

Чтобы присоединить базу данных, кликните на папке «Базы данных» правой кнопкой мыши и выберите «Присоединить…» / Добавить, после чего укажите путь к *.mdf файлу базы данных которую необходимо присоединить.

Примечание. В случае необходимости, с помощью Hetman Partition Recovery можно восстановить файл резервной копии базы данных (*.bak), Иморта/Экспорта базы данных или файлы отсоединённой базы (*.mdf, *.ndf ,*.ldf) с дальнейшим их присоединением или восстановлением в Microsoft SQL Server.

Автор: Vladimir Mareev, Технический писатель

Владимир Мареев — автор и переводчик технических текстов в компании Hetman Software. Имеет тринадцатилетний опыт в области разработки программного обеспечения для восстановления данных, который помогает ему создавать понятные статьи для блога компании. Спектр публикаций довольно широк и не ограничивается только лишь темой программирования. Статьи включают также разнообразные обзоры новинок рынка компьютерных устройств, популярных операционных систем, руководства по использованию распространенных и специфических программ, примеры решений возникающих системных или аппаратных проблем и многие другие виды публикаций. Подробнее

How can I find out the location of my (localdb) SQL Server 2012 database and back it up?

How can I find out the physical location of this database. How can I back this up? Can I just make a copy of the files, move these to another location and start the database again.

Here is my connection string:

8 Answers 8

It is quite confusing for people who touch with Entity Framework the first time.

If you use Code First, an mdf file is generated at %USERPROFILE% (e.g. C:\Users\<username> ).

If you use Database First, you create a database under SQL Server Object Explorer (not Server Explorer!), an mdf file will be generated at %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB .

By default, LocalDB database creates “*.mdf” files in the C:/Users/»username» directory.

Are you saying you can see it listed in SQL Server Management Studio? Right click on DataBase -> Properties -> Files will tell you where on your hard disk it lives. If you backup the mdf, be sure to back up the ldf too.

Alternatively, you can right click on the DB, and choose Tasks -> Backup . This will make a a single .bak file for you, and you don’t need to worry about the mdf/ldf.

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

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

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

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

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

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 Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\.

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

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

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

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