Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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\"

share|improve this question

migrated from stackoverflow.com Jan 15 '11 at 1:17

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
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 –  echoScout Jan 18 '11 at 14:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.