[sql-server] SQL Server: Importing database from .mdf?

I have an .mdf file on my local box.

I have SQL Server 2008 Express and SQL Management Studio 2008 Express installed on my local box.

How in the world do I import this .mdf file as a new database into my SQL Server?

This seems like a ridiculously common task that must be performed thousands of times a day across the globe, and I cannot figure out how to do it in Management Studio Express.

What am I missing?

This question is related to sql-server database import mdf ssms

The answer is


If you do not have an LDF file then:

1) put the MDF in the C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\

2) In ssms, go to Databases -> Attach and add the MDF file. It will not let you add it this way but it will tell you the database name contained within.

3) Make sure the user you are running ssms.exe as has acccess to this MDF file.

4) Now that you know the DbName, run

EXEC sp_attach_single_file_db @dbname = 'DbName', 
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\yourfile.mdf';

Reference: https://dba.stackexchange.com/questions/12089/attaching-mdf-without-ldf


Open SQL Management Studio Express and log in to the server to which you want to attach the database. In the 'Object Explorer' window, right-click on the 'Databases' folder and select 'Attach...' The 'Attach Databases' window will open; inside that window click 'Add...' and then navigate to your .MDF file and click 'OK'. Click 'OK' once more to finish attaching the database and you are done. The database should be available for use. best regards :)


Apart from steps mentioned in posted answers by @daniele3004 above, I had to open SSMS as Administrator otherwise it was showing Primary file is read only error.

Go to Start Menu , navigate to SSMS link , right click on the SSMS link , select Run As Administrator. Then perform the above steps.


To perform this operation see the next images:

enter image description here

and next step is add *.mdf file,

very important, the .mdf file must be located in C:......\MSSQL12.SQLEXPRESS\MSSQL\DATA

enter image description here

Now remove the log file

enter image description here


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to import

Import functions from another js file. Javascript The difference between "require(x)" and "import x" pytest cannot import module while python can How to import an Excel file into SQL Server? When should I use curly braces for ES6 import? How to import a JSON file in ECMAScript 6? Python: Importing urllib.quote importing external ".txt" file in python beyond top level package error in relative import Reading tab-delimited file with Pandas - works on Windows, but not on Mac

Examples related to mdf

Attach (open) mdf file database with SQL Server Management Studio SQL Server: Importing database from .mdf? What is an MDF file?

Examples related to ssms

The backend version is not supported to design database diagrams or tables How to export all data from table to an insertable sql format? Insert Data Into Temp Table with Query Incorrect syntax near '' How to find server name of SQL Server Management Studio Recover unsaved SQL query scripts SQL query, if value is null then return 1 How to connect to LocalDb How do you view ALL text from an ntext or nvarchar(max) in SSMS? How to install SQL Server Management Studio 2012 (SSMS) Express?