Post reply

restoring from sql 2000 to 2005

  • February 19, 2009 at 10:37 am

    #197306

    Hi

    I have taken a backup of my database in sql 2000 and am restoring it in sql 2005. My database name in sql 2000 was CLF and the user id I had created for this database in sql 2000 was CLUSER.

    Can anyone please tell me the proper procedure of migrating my database from sql 2000 to sql 2005 and I should have the same user rights to the new db in 2005 with same user id as CLUSER.

    Thanks for reading

  • February 19, 2009 at 3:17 pm

    #947196

    either backup and restore or detach and attach to the new server. Script off the login if possible or create a new server login on the sql 2005 server and sync to the database user using the stored procedure

    sp_change_users_login with the 'Update_One' action (check BOL for more info)

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 19, 2009 at 3:21 pm

    #947199

    Make sure you run Upgrade Advisor for the SQL 2000 database first to see if there are any issues that you can take care of before migrating. This is a good 'how to' article: http://technet.microsoft.com/en-us/library/cc966519.aspx

    Greg

  • February 19, 2009 at 10:40 pm

    #947319

    Thanks for the help

    What I did was

    1) Took backup of my database in sql 2000 (db name is CLG and login and user is CLGUSER)

    2) I created a login in sql 2005 as CLGUSER

    3) logged in sql 2005 with the default 'sa' login and restored the database as CLG in sql 2005

    4) Then selected the CLG database in query analyser within the 'sa' login and executed the script

    sp_change_users_login 'Update_One', 'CLGUSER', 'CLGUSER'

    5)After the above script got executed successfully I closed the query analyser and logged in again using CLGUSER login.

    6) I select the CLG database and when I try to select a table of CLG

    Select * from ClusterMaster

    I get a error which says

    "An error occurred while executing batch. Error message is: The directory name is invalid."

    I get the above error only when I am trying to login and access from the server but however if I try and login using the sql client from another machine I get the select output fine. What could be the reason for this and I hope my migration to sql 2005 was fine. Pls suggest...

  • March 4, 2009 at 2:00 pm

    #953585

    Try this http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ffc3467d-e61b-4d8e-afc1-d39251090605/

  • March 5, 2009 at 10:25 am

    #954188

    did you check the user mapping?

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

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