locked
SQL Server 2005 - Moving from Svr2003 to svr2008 RRS feed

  • Question

  • Hello,

    I've just built a 2008 Server and installed SQL Server 2005 on it. This server will act as a replacement for a 2003 server which is of course going end of life.

    I've been reading and following (and trying) tech net articles left, right and centre but am really struggling with this:  I want to backup, move  or migrate my databases from the old 2003 server on to my 2008 server. Does anyone have a definitive guide for achieve this? I've backed up and tried to restore with this error being the result:

    Variables to aid in people's thinking:

    • old server hostname 'Milhouse'
    • New server hostname: 'Delta'
    • Old server 2003
    • New Server 2008
    • Files from old server are sitting in same directory on the new server, that being: C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\Data

    It's been a long day. What am I missing here?

    Thanks in advance

    Mike

    Monday, July 13, 2015 4:20 PM

Answers

  • Hi Mike,

    If you check the first error you posted it says Operating system error 3.

    When ever you get Operating System errors and it gives a number and not what the actual error is  , Open a command prompt window and type "net helpmsg ErrorNumber"  so in this case "net helpmsg 3"

    You get this output then "The system cannot find the path specified."

    Now look at the error again the path is "c:\program files\microsoft sql server 2005\mssql.1\mssql\data\emeascheduel.mdf" and the path that you showed in the next pic is "c:\program files\microsoft sql server 2005\mssql.1\data\emeascheduel.mdf"

    which means the path is wrong. Put in the correct path and it should work.

    Note: Don't put your mdf and ldf files in C drive which is operating system drive, use a different drives for it.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Charlie Liao Monday, July 20, 2015 3:35 AM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:48 AM
    Tuesday, July 14, 2015 9:56 AM

All replies

  • Try using Creat database for attach tsql command and see if it works

    USE [master]
    GO
    CREATE DATABASE [EMEASchedule] ON 
    ( FILENAME = N'C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\Data\EMEASchedule.mdf' )
    FOR ATTACH
    GO
    


    Hope it Helps!!

    Monday, July 13, 2015 4:37 PM
  • Hi Stan210, thanks for your reply. I gave this a try. Parsing suggests the query is perfect, however upon execution I get this error:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA\EMEAschedule_log.LDF" may be incorrect.
    Msg 5170, Level 16, State 1, Line 1
    Cannot create file 'C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\Data\EMEASchedule_log.LDF' because it already exists. Change the file path or the file name, and retry the operation.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'EMEASchedule'. CREATE DATABASE is aborted.

    I've tried renaming the .ldf file to EMEASchedule1_log.ldf to see what happens and this is then the error I receive:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA\EMEAschedule_log.LDF" may be incorrect. Msg 5123, Level 16, State 1, Line 1 CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server 2005\MSSQL.1\Data\EMEASchedule_log.LDF'. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'EMEASchedule'. CREATE DATABASE is aborted.

    Any other ideas? Thanks in advance.

    Regards

    Michael

    Tuesday, July 14, 2015 7:34 AM
  • Hi Mike,

    If you check the first error you posted it says Operating system error 3.

    When ever you get Operating System errors and it gives a number and not what the actual error is  , Open a command prompt window and type "net helpmsg ErrorNumber"  so in this case "net helpmsg 3"

    You get this output then "The system cannot find the path specified."

    Now look at the error again the path is "c:\program files\microsoft sql server 2005\mssql.1\mssql\data\emeascheduel.mdf" and the path that you showed in the next pic is "c:\program files\microsoft sql server 2005\mssql.1\data\emeascheduel.mdf"

    which means the path is wrong. Put in the correct path and it should work.

    Note: Don't put your mdf and ldf files in C drive which is operating system drive, use a different drives for it.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Charlie Liao Monday, July 20, 2015 3:35 AM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:48 AM
    Tuesday, July 14, 2015 9:56 AM