SQL Server: Importing database from .mdf?

Question

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?

Geniegenii

Answer 1

See: How to: Attach a Database File to SQL Server Express

Login to the database via sqlcmd:

sqlcmd -S Server\Instance

And then issue the commands:

USE [master]
GO
CREATE DATABASE [database_name] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\<database name>.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\<database name>.ldf' )
 FOR ATTACH ;
GO

Disturbing

And, though it's linked to (indirectly) from that article, here's how to do it in SSMS. – Satisfy

Answer 2

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 :)

Follett

Like daniele3004 said: very important, the .mdf file must be located in C:......\MSSQL12.SQLEXPRESS\MSSQL\DATA – Coauthor

@Coauthor either that, or the folder that the MDF is in needs to have write permission for the SQL Server user account. – Muntin

Aside from having permissions to access the folder and mdf file, I just clicked the missing ldf file in the list (it said something like: file not found) and then clicked Remove, to remove it from the list. Then attach process just worked. – Notebook

Answer 3

To perform this operation see the next images:

and next step is add *.mdf file,

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

Now remove the log file

Scrubland

Answer 4

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.

Endothermic

Answer 5

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

Jenijenica

Answer 6

When hosting MS SQL in docker like:

version: '3.4'    
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sqlserver
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Samle123$
    ports:
      - "1433:1433"
    volumes:
      - S:\Database\data:/var/opt/mssql/data

it's enough to:

• add mdf and ldf(option) files to data folder (there: S:\Database\data)
• in SSMS Attach database and selecting copied mdf file.

Slant