CODEWITHSUNDEEP
sql-serverdatabasemdf
q0987
q0987

Reputation: 35982

SQL - How to backup a database and export as a MDF file with MS SQL 2008 R2

I have created a database "test" with some tables in MS SQL Server 2008 R2 (i.e. MS SQL Server Management Studio).

Now, I need to export this database as a MDF file.

What should I do?

Upvotes: 33

Views: 112174

Answers (8)

Alf Moh
Alf Moh

Reputation: 7427

If you are in Visual Studio, go to SQL Server Object Explorer. Find the database you want, right click and select properties. In the properties tab, look for Data File, next to it will be the location for your mdf file.

Upvotes: 1

Nur Uddin
Nur Uddin

Reputation: 1840

Go to the path C:\Users\your pc name then you will find mdf , ldf file here

Upvotes: 0

Gabriel McAdams
Gabriel McAdams

Reputation: 58261

If you mean that you want to be able to attach the database on another server, then this is what you can do:

  1. detach the database (right click the database and click Detach)
  2. copy the mdf and ldf files to your backup location
  3. attach the database (right click Databases and click Attach)

This is the path where you will find MDF file: 

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

Upvotes: 59

Sudhanshu Mishra
Sudhanshu Mishra

Reputation: 6733

In my experience, its easier to use the commands sp_detach_db (MSDN) and sp_attach_db (MSDN). I was trying to attach a database that was offline when detached and SQL Server Management Studio (2014) kept crashing when I used the Attach option from the right-click context menu. The commands worked - hope this saves someone some time.

NOTE: Run SSMS as an administrator if you see an access denied while trying to execute the attach command

Upvotes: 0

yogesh lodha
yogesh lodha

Reputation: 301

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

This is the path where you will find MDF file

Upvotes: 1

JAN
JAN

Reputation: 21865

I think the best way is to create a .bak file from SQL Server .

Right click on the database => Task => Back Up -> Choose the database in Source , and hit OK .

Upvotes: 2

dza
dza

Reputation: 1504

If what you want is a replica of your SQLEXPRESS/MDF local file- on a remote server (SQL Server 2005)

You can right click in the "Server Explorer" on your db.mdf file and press "Publish to provider..."

You can choose a variety of compatibilities with SQL Server 2005, 2008, etc.

The output is a .sql query file ..

Came across this question and thought this would be a good alternative answer, since I got here by searching for the exact same thing!

Upvotes: 1

danielovich
danielovich

Reputation: 9687

right-click on database from Management Studio -> Detach. Then you have the MDF file you can export to wherever you want :)

Upvotes: 2

Related Questions