Post reply

Move Database from One Server to Another

  • September 29, 2006 at 8:33 am

    #61349

    Hello,

    The background to my problem is that I need to copy a user database from a Version 7 Developer Edition Database to a MSDE 2000 Database.  The databases are located on Seperate Servers.

    My question is

    'If you were starting from first principals what would be the best strategy to adopt to achieve this?'

    I have read about the sp_attach_db and sp_detach_db procedures, I was wondering whether anyone had experience of using them between different versions of the database and whether using these would work, as in the manuals it doesn't seem very clear one way or the other.  If you used this strategy would it require an upgrade of one instance or the other.  Also if I were to use sp_attach_db and sp_detach_db how would I tranfer all the user logins and privilges ?

    I apologise if this seems like rather a noddy question but I new to the world of MS SQL.

    Thanks in Advance

    Marc

  • October 2, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • October 4, 2006 at 12:53 am

    #664012

    In my memory, you can attach a database to a new version. But it is not true vice versus.

    You need to run sp_users_login to fix the orphan users.

    Hopefully, the information will help.

  • October 4, 2006 at 2:50 am

    #664031

    Hi,

    First transfer all the logins from SQL Server 7.0 to SQL Server 2000 through DTS or scripting out the logins at SQL Server 7.0 and running the script at SQL Server 2000.

    If a database is created in SQL Server 7.0 then it can be restored at SQL Server 2000 instance (however the reverse is not possible). First in SQL Server 7.0, right click on the database to be taken to SQL Server 2000; go to All Tasks -> Detach database.

    On SQL Server 2000 where the database has to be restored, right click on the instance on which the database needs to be restored -> Attach database -> Enter the relevent database details. The Windows are quite simpler to be understood.

    then, go to the database (that is restored on SQL Server 2000), and use p_change_user_login stored procedure to fix the orphaned users.

    Please let me know if it helped or if you have any queries.

    All the best.

    Pankaj Khanna
    Database Administrator - SQL Server 2000

    Keep hope to keep you intact...

  • October 4, 2006 at 4:01 am

    #664044

    Thanks very much I will give that a try when i get some down time.

    Thankyou

     

  • October 12, 2006 at 10:38 am

    #665554

    Thanks for the advice - I have successfully dettached/reattached and in the process upgraded my database.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply