SQLShack
Different methods to attach SQL Server MDF files
This article demonstrates different methods to attach SQL Server MDF files. First, let me explain about the database files.
A SQL Server database has three types of files:
Primary data file OR MDF file
Secondary data file OR NDF file
The secondary datafiles are optional. The purpose of the primary data file and secondary data file (.ndf file) are the same. Secondary data files are useful when we want to stripe the data across multiple drives of the database server. For example, if you want to keep the tables on X drive and indexes on Y drive, then you can keep the tables on the primary data file and indexes on the secondary data file.
Log file OR LDF file
It stores the changes made in the database by insert, update, delete, etc. LDF file has all the information that can be used to recover a database.
How to find the location of the MDF files
We can obtain the location of the database files by querying sys.database_files and sys.master_files dynamic management views. The difference is between sys.master_files provides the physical location of all the databases and sys.database_files provides the information of the specific database.
For the demonstration, I have restored the “WideWorldImportors” demo database. You can download it from here. Now to obtain the location of database files from sys.master_files DMV, execute the following query.
Following is the screenshot of the output:
To obtain the information of the database files from sys.database_files DMV, execute following query:
To view the database files location using SQL Server management studio, open SSMS Connect the database engine Expand databases right-click on “WideWorldImportors” Select properties. See the following image:
In properties, select files. In “Path” and “File Name” columns show the location of the database files. See the following image:
We can attach the SQL Server database files using the following methods:
Attach MDF File using SSMS
To attach a database using SSMS, first, open SSMS connect to the database engine Right-click on “databases” select “Attach.” See the following image:
On the Attach Database dialog box, click on Add (Screen 1). On locate database dialog box (Screen 2), locate the database MDF files which you want to use to create the database. By default, the “locate database files” dialog box uses the default database file location, but you can navigate to the other location of the database file or provide the location in the “database data file location” textbox. Select the desired database file and click OK. See the following image:
On attach database file dialog box, you can review the database details in “Database to attach” and database file details in the “AdventureWorks2017 database details” box. See the following image:
Click on OK to attach the database. Once the database attaches successfully, you can view the database in object explorer. See the following image:
Attach MDF File using T-SQL Query
We can also attach the database using “CREATE DATABASE.. WITH ATTACH” or “exec sp_attach_db” T-SQL commands. The syntax of “CREATE DATABASE.. WITH ATTACH” command is as follows:
For example, if you want to attach the AdventureWorks2017 database, you must execute the following command.
The syntax of the “exec sp_attach_db” command is as following:
We can attach the adventureworks2017 database by executing following query:
Troubleshooting errors while attaching the MDF files
While attaching the database, you might encounter any of the following errors:
Access denied due to lack of permission
You might face an error “unable to open physical database file Operating system error 5: Access denied” This error occurs because of the lack of the permissions on the database file or log files. This can be fixed by any of the following methods:
Unable to downgrade:
While attaching the database, if you see “The database cannot be opened because it is version XXX” error, then make sure that you are not attaching the database files of the higher version to the lower version. For example, if you are trying to attach the database of SQL server 2014 to SQL Server 2008, you will see the following error:
The downgrade was never supported by Microsoft, so you have no options to rectify this issue.
Summary
In this article, I have explained different types of database files (MDF, NDF and LOG files) and different ways to attach the database to SQL Server instance via SSMS and T-SQL. Also, we run through the basic troubleshooting steps to fix the errors which have been occurred while attaching the database.
I have a SQL Server Primary Database file. (.mdf)
I require a program which can open the file and let me edit some data.
Since there are many fields, it tedious to open all the records and modify it.
5 Answers 5
Download SQL Server Management Studio Express and write an update query to change the required fields.
I can almost guarantee that editing the MDF file directly is risky to your data and not supported in any way by Microsoft.
If you have no programming knowledge you should get someone who does to write the update query for you. Alternatively you could read up on basic SQL yourself. Most people can get working with simple SELECT and UPDATE statements quite quickly.
You can’t update the data in an MDF file outside of SQL Server. The file format is not disclosed, and even if you’d manage to somehow make updates in it the integrity checks would at best cause the modified tables to be marked as corrupted, at worse place the entire database offline.
There is only one tool that can open and modify MDF files: a SQL Server instance of the appropriate version, as Ash has directed you. Before doing any modification to the database, I would highly recommend making a copy of the MDF and LDF files.
I share your pain here ;-)). nothing like a small/lean/mean/free utility to do the the job. I’m always hunting for them. Preferably free+portable tools.
Definitively SQL Studio is overkill for what you want. and a huge bloatware. and only for MSSQL. so I would suggest.
Файлы и файловые группы базы данных
Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.
Файлы базы данных
SQL Server имеют три типа файлов.
| Файл | Описание |
|---|---|
| Первичная | Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется расширение MDF. |
| Вторичная | Необязательные определяемые пользователем файлы данных. Данные могут быть распределены на несколько дисков, в этом случае каждый файл записывается на отдельный диск. Для имени вторичного файла данных рекомендуется расширение NDF. |
| Журнал транзакций | Журнал содержит информацию для восстановления базы данных. Для каждой базы данных должен существовать хотя бы один файл журнала. Для файлов журнала транзакций рекомендуется расширение LDF. |
Например, простая база данных с именем Sales включает один первичный файл, содержащий все данные и объекты, и один файл журнала, содержащий сведения журнала транзакций. Более сложная база данных с именем Orders может содержать один первичный файл и пять вторичных файлов. Данные и объекты внутри базы данных распределяются по всем шести файлам, а четыре файла журнала содержат сведения журнала транзакций.
По умолчанию и данные, и журналы транзакций помещаются на один и тот же диск и имеют один и тот же путь для обработки однодисковых систем. Для производственных сред это может быть неоптимальным решением. Рекомендуется помещать данные и файлы журнала на разные диски.
Логические и физические имена файлов
Файлы SQL Server имеют два типа имен файлов.
logical_file_name: имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.
os_file_name: имя физического файла, включающее путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы.
Дополнительные сведения об аргументах NAME и FILENAME см. в статье Параметры ALTER DATABASE ((Transact-SQL)) для файлов и файловых групп.
Файлы данных и файлы журналов SQL Server могут использоваться как в файловой системе FAT, так и в системе NTFS. В системах Windows рекомендуется использовать файловую систему NTFS по причинам ее большей безопасности.
Файловые группы, доступные как для чтения, так и для записи, а также файлы журналов не поддерживаются со сжатой файловой системой NTFS. В сжатую файловую систему NTFS разрешено помещать лишь доступные только для чтения базы данных и доступные только для чтения вторичные файловые группы. Для экономии места настоятельно рекомендуется использовать сжатие данных вместо сжатия файловой системы.
Если на одном компьютере запущено несколько экземпляров SQL Server, каждый экземпляр получает отдельный каталог по умолчанию для хранения файлов баз данных, созданных в этом экземпляре. Дополнительные сведения см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server.
Страницы файлов данных
Страницы файлов данных SQL Server нумеруются последовательно; первая страница файла получает нулевой номер (0). Каждый файл базы данных имеет уникальный цифровой идентификатор. Чтобы уникальным образом определить страницу базы данных, необходимо использовать как идентификатор файла, так и номер этой страницы. В следующем примере показаны номера страниц базы данных, содержащей первичный файл данных объемом в 4 МБ и вторичный файл данных объемом в 1 МБ.
Страница заголовка файла — это первая, содержащая сведения об атрибутах данного файла. Некоторые другие страницы, расположенные в начале файла, тоже содержат системные сведения, например карты размещения. Одна из системных страниц, хранимых как в первичном файле данных, так и в первом файле журнала, представляет собой загрузочную страницу базы данных, которая содержит сведения об атрибутах этой базы данных.
Размер файла
Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.
Дополнительные сведения о страницах и их типах см. в разделе Руководство по архитектуре страниц и экстентов.
Кроме того, можно указать максимальный размер каждого файла. Если максимальный размер файла не указан, файл может продолжать увеличиваться в размерах, пока не займет все доступное место на диске. Эта функция особенно полезна в случаях, когда SQL Server используется в качестве базы данных, внедренной в приложение, где пользователь не имеет удобного доступа к системному администратору. По мере необходимости пользователь может предоставить файлам возможность увеличиваться в размерах автоматически, тем самым снимая с администратора часть забот по наблюдению за свободным пространством базы данных и по распределению дополнительного пространства вручную.
Дополнительные сведения об управлении файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
Файлы моментального снимка базы данных
Вид файла, используемый для хранения копируемых во время записи данных моментального снимка базы данных, зависит от того, создается ли моментальный снимок пользователем или используется внутренними механизмами.
Файловые группы
Все файлы данных хранятся в файловых группах, перечисленных в следующей таблице.
| Файловая группа | Описание |
|---|---|
| Первичная | Файловая группа, содержащая первичный файл. Все системные таблицы являются частью первичной файловой группы. |
| Данные, оптимизированные для памяти | В основе оптимизированной для памяти файловой группы лежит файловая группа файлового потока. |
| Файловый поток | |
| Определяемые пользователем маршруты | Любая файловая группа, созданная пользователем при создании или изменении базы данных. |
Файловая группа по умолчанию (первичная)
Если в базе данных создаются объекты без указания файловой группы, к которой они относятся, они назначаются файловой группе по умолчанию. В любом случае только одна файловая группа создается как файловая группа по умолчанию. Файлы в файловой группе по умолчанию должны быть достаточно большими, чтобы вмещать новые объекты, не назначенные другим файловым группам.
Файловая группа PRIMARY является группой по умолчанию, если только она не была изменена инструкцией ALTER DATABASE. Системные объекты и таблицы распределяются внутри первичной файловой группы, а не новой файловой группой по умолчанию.
Файловая группа данных, оптимизированных для памяти
Дополнительные сведения об оптимизированных для памяти файловых группах см. в разделе Оптимизированные для памяти файловые группы.
Файловая группа файлового потока
Дополнительные сведения о файловых группах файлового потока см. в статьях FILESTREAM и Создание базы данных с поддержкой FILESTREAM.
Пример файлов и файловых групп
Данная иллюстрация обобщает все вышесказанное (кроме данных файлового потока).
Стратегия заполнения файлов и файловых групп
В файловых группах для каждого файла используется стратегия пропорционального заполнения. При записи данных в файловую группу компонент Компонент SQL Server Database Engine записывает в каждый файл количество данных, пропорциональное свободному пространству этого файла, вместо записи всех данных в первый файл до его заполнения. Затем запись производится в следующий файл. Например, если в файле f1 свободно 100 МБ, а в файле f2 — 200 МБ, то в файл f1 записывается одна часть данных, а в файл f2 — две части, и так далее. Таким образом, оба файла будут заполнены примерно в одно и то же время, и достигается простейшее распределение данных между хранилищами.
Например, файловая группа состоит из трех файлов, для всех разрешено автоматическое увеличение. Когда свободное пространство во всех файлах группы закончится, будет расширен только первый файл. Когда заполнится первый файл и в файловую группу снова нельзя будет записывать новые данные, будет расширен второй файл. Когда заполнится второй файл и в файловую группу опять нельзя будет записывать новые данные, будет расширен третий файл. Когда заполнится третий файл и в файловую группу нельзя будет записывать новые данные, будет снова расширен первый файл и т. д.
Правила проектирования файлов и файловых групп
Для файлов и файловых групп действуют следующие правила:
Рекомендации
Рекомендации при работе с файлами и файловыми группами:
Дополнительные рекомендации по управлению файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
SQL Server: импорт базы данных из.МДФ?
У меня есть SQL Server 2008 Express и SQL Management Studio 2008 Express, установленные на моем локальном поле.
Это кажется смехотворно распространенной задачей, которую нужно выполнять тысячи раз в день по всему миру, и я не могу понять, как это сделать в Management Studio Express.
5 ответов
войдите в базу данных через sqlcmd:
а затем выполните команды:
для выполнения этой операции см. следующие изображения:
и следующий шаг-добавить *.mdf файл,
очень важно,.файл mdf должен быть расположен в C. \MSSQL12.SQLEXPRESS\MSSQL\DATA
теперь удалите файл журнала
помимо шагов, упомянутых в опубликованных ответах @daniele3004 выше, мне пришлось открыть SSMS как администратор, иначе он показывал, что основной файл только для чтения ошибка.
перейдите в меню Пуск, перейдите по ссылке SSMS, щелкните правой кнопкой мыши ссылку SSMS, выберите Запуск От Имени Администратора. Затем выполните описанные выше действия.
Если у вас нет файла LDF тут:
1) Положите MDF в C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\
3) убедитесь, что пользователь работает ssms.exe as отрыть этот файл MDF.
4) Теперь, когда вы знаете имя DbName, запустите
BestProg
Пример создания и работы с “ *. mdf ” файлом локальной базы данных Microsoft SQL Server
Содержание
Поиск на других ресурсах:
Условие задачи
Создать базу данных с именем “ Education ”. В базе данных создать две таблицы, которые связаны между собой по некоторому полю.
Структура первой таблицы « Student ».
Структура второй таблицы “Session”.
Выполнение
При создании “ *.mdf ” файла базы данных также создается файл с расширением “ *.ldf ”, который содержит журнал транзакций.
Рис. 1. Вызов Server Explorer
3. Команда “ Add Connection… ”.
Рис. 2. Вызов команды Add Connection из контекстного меню
Рис. 3. Команда “ Connect to Database… ”
4. Окно “ Add Connection ”.
В результате выполнения предыдущей команды откроется окно “ Add Connection ” (рисунок 4). В этом окне пользователь имеет возможность:
Рис. 4. Окно “ Add Connection ”
В результате откроется второе окно “ Change Data Source ” (рисунок 5). В этом окне выбирается источник данных и провайдер.
Система MS Visual Studio предлагает следующие виды источников данных:
Для создания “ *.mdf ” файла базы данных Microsoft SQL Server нужно выбрать источник данных “ Microsoft SQL Server Database File ” как зображено на рисунке 5.
Рис. 5. Окно “ Change Data Source ”
После изменения источника данных в окне Add Connection в поле “ Database file name (new or existing): ” нужно ввести имя создаваемой базы данных. Если нужно выбрать “ *.mdf ” файл уже существующей базы данных, то для этого предназначена кнопка “ Browse… ”.
В нашем случае нужно ввести название базы данных “ Education ”, как изображено на рисунке 6.
Рис. 6. Создание базы данных Education
После подтверждения на “ OK ”, система выведет окно, как изображено на рисунке 7. Предлагается системная папка по умолчанию:
Если нужно установить другую папку, для этого используется кнопка “ Browse… ” из окна “ Add Connection ”.
Рис. 7. Предложение создать файл “ Education.mdf ”
После подтверждения, база данных Education.mdf будет создана (рисунок 8).
Рис. 8. Новосозданная база данных “ Education.mdf ”
На данный момент база данных Education абсолютно пустая и не содержит никаких объектов (таблиц, сохраненных процедур, представлений и т.д.).
Чтобы создать таблицу, нужно вызвать контекстное меню (клик правой кнопкой мышки) и выбрать команду “ Add New Table ” (рисунок 9).
Рис. 9. Команда добавления новой таблицы
Существует и другой вариант добавления таблицы базы данных с помощью команд меню Data (рисунок 10):
Рис. 10. Альтернативный вариант добавления новой таблицы
В результате откроется окно добавления таблицы, которое содержит три столбца (рисунок 11). В первом столбце “ Column Name ” нужно ввести название соответствующего поля таблицы базы данных. Во втором столбце “ Data Type ” нужно ввести тип данных этого поля. В третьем столбце “ Allow Nulls ” указывается опция о возможности отсутствия данных в поле.
Рис. 11. Окно создания новой таблицы
С помощью редактора таблиц нужно сформировать таблицу Student как изображено на рисунке 12. Имя таблицы нужно задать при ее закрытии.
Рис. 12. Таблица Student
Следующим шагом нужно задать ключевое поле. Это осуществляется вызовом команды “ Set Primary Key ” из контекстного меню поля Num_book (рисунок 13). С помощью ключевого поля будут установлены связи между таблицами. В нашем случае ключевым полем есть номер зачетной книжки.
Рис. 13. Задание ключевого поля
После установки первичного ключа окно таблицы будет иметь вид как изображено на рисунке 14.
Рис. 14. Таблица Student после окончательного формирования
Теперь можно закрыть таблицу. В окне сохранения таблицы нужно задать ее имя – « Student» (рисунок 15).
Рис. 15. Ввод имени таблицы Student
Рис. 16. Таблица Session
Таким образом, в базу данных можно добавлять любое количество таблиц.
7. Редактирование структуры таблиц.
Бывают случаи, когда нужно изменить структуру таблицы базы данных.
Рис. 17. Опция “ Prevent Saving changes that require table re-creation ”
После настройки можно изменять структуру таблицы. Для этого используется команда “Open Table Definition ” (рисунок 18) из контекстного меню, которая вызывается для выбранной таблицы (правый клик мышкой).
Рис. 18. Вызов команды “ Open Table Definition ”
Также эта команда размещается в меню Data :
Предварительно таблицу нужно выделить.
8. Установление связей между таблицами.
В соответствии с условием задачи, таблицы связаны между собою по полю Num_book.
Чтобы создать связь между таблицами, сначала нужно (рисунок 19):
Рис. 19. Вызов команды добавления новой диаграммы
Рис. 20. Сообщение о создании объекта-диаграммы
В результате откроется окно добавления новой диаграммы Add Table (рисунок 21). В этом окне нужно выбрать последовательно две таблицы Session и Student и нажать кнопку Add.
Рис. 21. Окно добавления таблиц к диаграмме
В результате будет создан новый объект с двумя таблицами Student и Session (рис. 22).
Рис. 22. Таблицы Student и Session после добавления их к диаграмме
В окне Tables and Columns задается название отношения ( FK_Session_Student ) и названия родительской ( Student ) и дочерней таблиц.
Рис. 23. Окно Tables and Columns
Рис. 24. Окно настройки свойств отношения
После выполненных действий будет установлено отношение между таблицами (рисунок 25).
Рис. 25. Отношение между таблицами Student и Session
Сохранение диаграммы осуществляется точно также как и сохранение таблицы. Имя диаграммы нужно выбрать на свое усмотрение (например Diagram1 ).
Рис. 26. Подтверждение сохранения изменений в таблицах
9. Внесение данных в таблицы.
Система Microsoft Visual Studio разрешает непосредственно вносить данные в таблицы базы данных.
Рис. 27. Команда Show Table Data
Откроется окно, в котором нужно ввести входные данные (рис. 28).
Рис. 28. Ввод данных в таблице Student
При внесении данных в поле Num_book таблицы Session нужно вводить точно такие же значения, которые введены в поле Num_book таблицы Student (поскольку эти поля связаны между собой).
Рис. 29. Сообщение об ошибке ввода данных связанных таблиц Student и Session
Таблица Session с введенными данными изображена на рисунке 30.
Рис. 30. Таблица Session с введенными данными
Итак, база данных создана. Ввод и обработку данных в таблицах можно реализовать программным путем.