Results 1 to 14 of 14

Thread: Moving Data and SP from Ms SQL Server 7 to SQL Server 2005

  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Moving Data and SP from Ms SQL Server 7 to SQL Server 2005

    I'm setting upp new machine with Win Srvr. 2003 and SQL server.
    My old Win2K server is running SQL 7 with SP4, I want to move one DB and associated SP from SQL 7 to SQL 2005 but can't succeed

    While on SQL 2005 Srv Managemt, I tried to connect to the SQL 7 and Copy Database (and assiciated SP). I did not take DB offline, but this shoudl not matter...

    I get error on last Action (Execute SQL Server Agent Job), "The job failed".

    PS. Will the SP from SQL 7 work on SQL 2005 ?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Did you use Copy database wizard?

    It sounds like permission issue on SQL Agent service account, do you use same account on both servers?

    Another way will be to backup database in 7.0 and restore it in SQL2K5.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    can't you just take backup and restore it in 2005?

    make sure you set the compatibility to 70.. or else any pre-ANSI 92 standard syntax in stored procs and views will not work.

  4. #4
    Join Date
    Sep 2008
    Posts
    6
    Well, I took full backup of my DB in SQL 7, tried to restore on 2005 but I get error:

    Restore failed for Server 'WEBSERVER3'. (Microsoft.SqlServer.Smo)

    System.Data.SqlClient.SqlError: Directory lookup for the file "D:\SQL Data\MSSQL\Data\minishop.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

    I tried to do use SQL 2005 Wizard, connect to my SQL 7, make backup, all steps was OK but the last one "Execute SQL Agent Server Job Error"
    Last edited by MrPK; 09-05-2008 at 06:52 AM.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    That is because the directory structure for data files is different in two servers, you can use WITH MOVE option in RESTORE DATABASE command to point to new location.

  6. #6
    Join Date
    Sep 2008
    Posts
    6
    OK, hmm sorry I'm so advanced user. When copying with the wizzard I can't choose any parameters, when restoring I can go the script, but what's the syntax ?
    It looks like this right now:

    RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK' WITH FILE = 1, NOUNLOAD, STATS = 10
    GO

    I need to get moved also all SP for thsi DB. IS it included in the backup? Thx.
    Last edited by MrPK; 09-05-2008 at 11:35 AM.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK' WITH
    move 'data_file' to 'new data file path',
    move 'log_file' to 'new log file path' ...

    Books online has sample code.

  8. #8
    Join Date
    Sep 2008
    Posts
    6
    I saw the syntax... in my case the SQL 7 DB is on another server, I mapped this as X: but still no luck.

    RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK'
    WITH FILE = 2, NOUNLOAD, STATS = 10;
    WITH MOVE "X:\minishop.mdf" to "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.mdf";
    WITH MOVE "X:\minishop_log.ldf" to "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.ldf";
    GO


    ERROR:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'X:\minishop.mdf'.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    1. you need only one 'with' for all options.
    2. data_file and log_file should be db's logical file name, you can find that with 'restore filelistonly'.

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    If RESTORE syntax is not working, then you can do this from Management Studio as well. You need to go to Options and specify new locations.

  11. #11
    Join Date
    Sep 2008
    Posts
    6
    Options for what? The SQL 7 DB is running on different server in my network, and it's online. The SQL 2005 DB is on the new server. I could specify all locations in the wizzard, but still no luck.
    Last edited by MrPK; 09-09-2008 at 02:10 AM.

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Did you fix your statement like following?

    RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK'
    WITH FILE = 2, NOUNLOAD, STATS = 10, MOVE 'minishop' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.mdf',
    MOVE 'minishop_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.ldf'
    GO

  13. #13
    Join Date
    Sep 2008
    Posts
    6
    This results in:

    Msg 3234, Level 16, State 2, Line 1
    Logical file 'minishop' is not part of database 'minishop'. Use RESTORE FILELISTONLY to list the logical file names.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    As I said above, you have to find out logical file names with following command and replace them:

    restore filelistonly from DISK = N'E:\minishop_db_200808280532.BAK'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •