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
- Log File OR LOG File
Primary data file OR MDF file
The SQL Server database stores data in MDF files. Typically, .mdf is a preferred extension of the primary database file. It is not a type of file. You can use another extension (*.gbn) to create a primary database file without any error. The primary data file contains columns, fields, rows, indexes, tables, and data added by an application. It also contains the vital information of the database.
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.
|
1 2 3 4 |
use master go select db_name(database_id) as [Database Name],type_desc as [File Type], physical_name as [Database File Location] from sys.master_files where database_id>=5 --exclude system databases |
Following is the screenshot of the output:
To obtain the information of the database files from sys.database_files DMV, execute following query:
|
1 2 3 |
use WideWorldImporters go select db_name()as [Database Name],type_desc as [File Type], physical_name as [Database File Location] from sys.database_files |
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:
- Using the SQL Server Management Studio
- Using T-SQL Script
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:
|
1 2 3 4 5 6 |
USE [master] GO create database <DatabaseName> ON (name='LogicalName of the Data file', FileName='Data File Name'), (name='LogicalName of the Log file', FileName='Log File Name') FOR ATTACH; |
For example, if you want to attach the AdventureWorks2017 database, you must execute the following command.
|
1 2 3 4 5 6 7 |
USE [master] GO CREATE DATABASE [AdventureWorks2017] ON ( FILENAME = N'C:\MSSQL\SQLData\AdventureWorks2017.mdf' ), ( FILENAME = N'C:\MSSQL\SQLLog\AdventureWorks2017_log.ldf' ) FOR ATTACH GO |
The syntax of the “exec sp_attach_db” command is as following:
|
1 2 3 4 5 |
USE [master] GO EXEC sp_attach_db @dbname = N'DatabaseName', @filename1 = '<Location of the database file>', @filename2 = '<Location of the Log file>'; |
We can attach the adventureworks2017 database by executing following query:
|
1 2 3 4 5 |
USE [master] GO EXEC sp_attach_db @dbname = N'AdventureWorks2017', @filename1 = 'C:\MSSQL\SQLData\AdventureWorks2017.mdf', @filename2 = 'C:\MSSQL\SQLLog\AdventureWorks2017_log.ldf'; |
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 <File Name> 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:
- Run SQL Server management studio as an administrator and attach the database
- Explicitly grant full control access to the MDF file and LDF file of the database. To do that, Right-click the database files Select the security tab select the appropriate user and grant full control to the user
- If none of the above solutions work, copy the database files to the default database file locations. When we copy these files to the default database file location, the user will get the required permissions automatically
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022