Learn more about SQL Server tools

   
   



















































Attaching a SQL 2005 MDF file to SQL 2008 DB

Asked 1/3/2012 11:06:33 AM by Ahmed Zubair

Hi Guys,

Can I attach an MDF file from a SQL Server 2005 database to a SQL Server 2008 R2 database?

I am trying to do that but the MDF file is not visible when I browse to attach it.

Thanks,
Ahmed



Answered 1/3/2012 11:11:43 AM by Greg Robidoux (1) vote answer is useful vote answer is NOT useful

Yes you can attach a SQL 2005 database to SQL 2008, but you can not go the other way.

Once you attach the database, SQL Server will upgrade the system tables and meta data in the database to be compatible with SQL 2008.

Make sure that the file is in a folder that the SQL Server account has access to read.  You may need to either move the file or grant permissions to the SQL Server service account to see the files.


Answered 1/4/2012 2:02:57 AM by Vahid Hosseini (0) vote answer is useful vote answer is NOT useful

I think best soulotion is Backup and Restore. this way is low risk.


Answered 1/4/2012 3:26:42 AM by Ahmed Zubair (0) vote answer is useful vote answer is NOT useful

Thanks Guys! 

As suggested by Greg, the issue was with the permissions on the folder where the files were placed. 

What is the difference between attaching MDF & LDF files and doing a Backup and Restore? 


Answered 1/4/2012 4:11:41 AM by Vahid Hosseini (0) vote answer is useful vote answer is NOT useful

When Microsoft release new version of Sql Server generally MDF&LDF files can not attach to new version for example 2005 to 2008 because a metadata saved in header of files thet in new version is difficult with older version so you can't attached.then better you get Backup and then restore on new version however you should be carful when restore files in options of menue when you choose path files in end of path you should'nt remove file extension like FileName.MDF or FileName.LDF.

D:\Data\TestDB.MDF

E:\Log\TestDB.LDF


Answered 1/4/2012 4:18:47 AM by Vahid Hosseini (0) vote answer is useful vote answer is NOT useful

difference between attaching MDF & LDF files and doing a Backup and Restore in Attach you really  phisycal file move to new version but in backup you copy a version of data  that can is last version of database.



Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information