5

Using SQL Server 2008, how do I export a database to mdf so that I can attach it on another machine? (using "Attach")

I tried detaching it (the english opposite of attach), but it didn't work out too well.

edit: I don't have write access to the mdf files already there in "[SQL Server root path]\DATA\"

Improve this question
0

3 Answers 3

Reset to default
7

Rather than copy the MDF files around, a better solution is to take a backup of the database and then restore that backup onto your target SQL instance.

Assuming you are using SQL Management Studio, you can do this by right clicking on your database and choosing:

Tasks -> Backup

The goto your target DB instance and right click on the database folder and choose:

Restore Database
Improve this answer
1

Personally I use a combination of

  • SQL Backup
  • SQL Hyperbac
  • SQL Packager

(I think SQL Scripts manager & using Ola's scripts may help. They are free, not that I have used them) Sure that there are loads of others, eg using BCP to migrate data & SQL Compare to migrate Schema.

Improve this answer
1

Bear in mind that the SQL Server instance you are moving them to must be of equal or greater version number than the instance you are moving from. You can't move a database to an older version of SQL Server. To check the version number on both machines, you can use this query:

SELECT SERVERPROPERTY('productversion')

Then you can either detach/copy/reattach or backup/restore as your needs require.

Improve this answer
1
  • Also bear in mind that, when moving to a greater version, the compatibility of the database you move remains the same until you change it. ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 } msdn.microsoft.com/en-us/library/bb510680.aspx Commented Jan 18, 2011 at 14:26

You must log in to answer this question.