none
Attaching a SQL 2005 MDF file to SQL 2008 DB

    Question

  • 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

    Tuesday, January 03, 2012 4:03 PM

Answers

  • Have you detached the SQL 2005 database before trying to attach it on SQL 2008 R2 instance?

    USE master;
    GO
    EXEC sp_detach_db @dbname = N'DatabaseName';
    GO

    USE master;
    Go
    EXEC sp_attach_db @dbname = N'DatabaseName',
       @filename1 =
          N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.mdf',
       @filename2 =
          N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.ldf';
    GO

    Thank you,

     


    Anup | Database Consultant

    Blog: www.sqlsailor.com Twitter: Follow me !

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, January 03, 2012 6:03 PM

All replies

  • Ahmed

    Yes, it is possible.. if you know the mdf file location use sp_attach_db sp.See BOL for more info

     

    VT

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, January 03, 2012 4:32 PM
  • Have you detached the SQL 2005 database before trying to attach it on SQL 2008 R2 instance?

    USE master;
    GO
    EXEC sp_detach_db @dbname = N'DatabaseName';
    GO

    USE master;
    Go
    EXEC sp_attach_db @dbname = N'DatabaseName',
       @filename1 =
          N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.mdf',
       @filename2 =
          N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Name.ldf';
    GO

    Thank you,

     


    Anup | Database Consultant

    Blog: www.sqlsailor.com Twitter: Follow me !

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, January 03, 2012 6:03 PM
  • Ahmed, Ideally yes, you should be able to attach .MDF files from 2005 to 2008 server.

    if you are not finding the files, Please check:

    1. Are you running both the instances on same windows server or seperate servers.
    2. If it on same server, the correct path of the file location. Also ensure you disconnect the DB on SQL 2005.
    3. If on a different server, did you move the copied files to the 2008 server? :)
    4. May be, are you connecting SSMS from a remote computer? if yes, again, the browser will be connected to the target machine and if the files are not copied to that location you will not be able to.

    Thank you


    Reddappa
    • Proposed as answer by Reddappa Thursday, January 05, 2012 11:08 AM
    Wednesday, January 04, 2012 9:30 AM