Post reply

restoring database from one server to another??

  • March 7, 2006 at 8:31 am

    #111874

    Hi!!

    I need your help...

    I have two sql server 2000...now i want to restore one database from one server to another...i took the full database back up and try to restore it on another server but it gives me error regarding .mdf and .ldf files..i created the database on another server having same name....let me know what procedure should i adopt so that database from first server restore successfully on another..also which precausion should i take so that all users and logins installed successfully on another server...i am taking full database strategy...I wanna to do it through EM....one more thing i am accessing both the server through Remote Administrator....I am getting location path error...

    my file for DB on server 1st is

    E:\mssql\data\test_data.mdf and

    E:\mssql\data\test_log.ldf and on 2nd server where i wanna to restore it is on

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf and

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.mdf

    please help in how i can manage to get proper path???

    T.I.A

    Papillon

    Regards,

    Papillon

  • March 7, 2006 at 8:45 am

    #625210

    Hello Papillon,

    Does the second sever has remote access to the backup file of the first server?

    If so, on the Restore option from Enterprise Manager you need to give the backup file name and in the "Options" tab you have to change the physical path from

    E:\mssql\data\test_data.mdf

    to

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf

    also the same for .ldf file.

    Now you execute the stored procedure to find out the logins

    sp_change_users_login 'Report' --- go thru the Books Online for help.

    Hope this will help you.

    Thanks and have a great day!!!


    Lucky

  • March 7, 2006 at 9:17 am

    #625224

    It is really easy once you have done it once or twice

     

    1. from enterprise manager, select the database and mene to all tasks/restore database option
    2. On the Restore Database popup, select the "from device" push button
    3. push the select device button
    4. add or browse your backupfile name
    5. after you add the file, select the OPTIONS tab
    6. check force restore over existing database
    7. change the "move to physical file name" to the new C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf
    8. hit the OK button

    Your database will now restore.

    You could also go old school and use a restore command from Query Analyzer with the move option

    Eric

  • March 7, 2006 at 9:51 am

    #625236

    hi!!

    i restored my DB to my destination server!! but as i logon i am getting err like

    unable to connect to server ABC:

    server:Msg 18452,level 16,State 1

    [Microsoft][ODBC SQL Server Driver][SQL Serve]Login failed for user 'xyz'.Reason: Not associated with a trusted SQL Server connection.

    i checked on destination server, where my user and login is same as my source server except in user [login name] i saw null in front of user 'xyz' where as in source server there is user [login name] 'xyz' in front of user 'xyz'......

    so how can i resolve this problem????

    Regards,

    Papillon

  • March 7, 2006 at 12:08 pm

    #625260

    hi

    i used all the stuff for resolving my login problem but i am unable still i am gettig the same error

    unable to connect to server ABC:

    server:Msg 18452,level 16,State 1

    [Microsoft][ODBC SQL Server Driver][SQL Serve]Login failed for user 'xyz'.Reason: Not associated with a trusted SQL Server connection.

    i used Transfer Logins Task to tansfer all my login then i used

    EXEC sp_change_users_login 'Auto_Fix', 'xyz' to fix the orphaned user it gives me 0 update

    can any body help me out

     

    Regards,

    Papillon

  • March 7, 2006 at 12:25 pm

    #625262

    Try sp_change_users_login again with the Update_One option for the login and database user you want to fix.

    HTH,

    Mark

  • March 7, 2006 at 12:49 pm

    #625265

    hi..

    i used this also it gives me 0 update..

    and then again same error

     

     

    Regards,

    Papillon

  • March 7, 2006 at 2:02 pm

    #625276

    Go to the properties of the server where you did the restore, under the Security Tab and Authentication, try selecting the SQL Server and Windows radio button if it is not already selected.  If that authentication is already selected, try deleting the Login and re-adding it.

    Hopefully this will work,

    Mark

  • March 7, 2006 at 2:22 pm

    #625280

    hi MARK !!!!!!!!!!!!!!!

     

    Simply great!!!!!!!!!!

    It worked!!!!!!!!!

    Thanks!!!!

    Regards,

    Papillon

  • March 8, 2006 at 10:30 am

    #625474

    hi..

    there just one thing i wanna to know is that..

    i restore only one DB from one server to another..ok..here i transfer two users associated with that DB..one is 'xyz' and another is 'dbo'....then i created login by

    -- Server1

    SELECT CONVERT(VARBINARY(32), password)

    FROM syslogins

    WHERE name = 'xyz'

    --Results

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

    0x2131214A.......

    (1 row(s) affected)

    -- Server2

    EXEC sp_addlogin 'xyz', 0x2131214A.......,

    @encryptopt = 'skip_encryption'

    i granted defalut DB (that i restored) access to login 'xyz'

    ok..then i search for orphaned user by..

    sp_change_users_login 'report'

    i got two..one is 'xyz' and other is 'dbo'..

    now i fixed 'xyz' by....

    sp_change_users_login 'auto_fix','xyz'

    again i fired ...

    sp_change_users_login 'report'

    i got DBO as orphaned....

    so i changed DB owner by ...(as given in BOL)

    sp_changedbowner 'sa'

    now there is no more orphaned user!!

    but i still couldnot logged to QA by login 'xyz'..it gives error me logging failed for user 'xyz'..

    then i opened SECURITY>>>Logins>>> right click on 'xyz' and manually type same password..and now i logged on QA with that login name 'xyz' and with same password...but when i checked again on 2nd server with

    SELECT CONVERT(VARBINARY(32), password)

    FROM syslogins

    WHERE name = 'xyz'

    then here i am getting different password than my 1st server password..now i can access DB but that different password makes any difference???? and how i can check my whole efforts is worth???

    or is m i missing some steps????

    Regards,

    Papillon

Viewing 10 posts - 1 through 9 (of 9 total)

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