Restore GP backup from SQL

moh_baradamoh_barada Posts: 46Member
edited 2008-02-11 in Dynamics GP
Dear all,
I have a customer who took his last backup from SQL server 2000.
the server is down, and he get a new server. the server name is different now and he installed again the sql server 2000 (also different name from the old one). He wants to work again on GP.
What we must do in this case to be able to restore the database of GP successfully. I tried to install gp and restore the database of GP from SQL server 2000 but it's not working due to problem with user logging...
What is the best way to restore SQL backup ,specially taking into consideration the problem of users.
Best Regards
Mohamad Barada
Independent ERP & CRM Consultant
+961 70 912700

Comments

  • krikikriki Posts: 8,820Member, Moderator
    I don't know anything about GP, but I suppose you just need to recreate the users in SQL.
    Check this url (http://support.microsoft.com/kb/246133) to how to copy the logins from the old system.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • geschwintgeschwint StockholmPosts: 10Member
    I don't know GP either but I do know SQL Server 2000-2005 and NAV

    If GP is using SQL Server built-in authentication of users, and I'm talking SQL logins now, not windows logins, one possible solution would be the following steps (works for Nav restore):

    1. Restore the GP database
    2. Recreate the SQL Logins that are missing
    Note: SQL Login and Database User is not the same thing
    The login will get a default database that is not GP. At this point it is not possible to set GP as the default database for this login since the user would exist already in GP.
    3. Use the stored procedure in SQL Server: sp_change_users_login to remap a Database User with its SQL Login.
    Sample: Get a list of database users that has no mapping, EXEC sp_change_users_login 'Report' -- Must be run in the GP database
    Sample: Map the login Sam to the database user Sam, EXEC sp_change_users_login 'Update_One', 'Sam', 'Sam' -- Must be run in the GP database

    After mapping, the login will have all the proper rights it had in the GP database at the time of backup.

    For Nav, I usually make sure to first remap a user with SUPER right and then try to connect just to make sure the method will work. Other circumstances might require another or a modified solution.

    The reason SQL Logins need to be mapped is that the login/user is not the key used to connect a login to a database, instead SQL Server uses GUIDs for that. So, when a database is backed up and restored on another instance of SQL server, the username might be the same as the login, but they are not the same since the GUIDs are different, thus the need for sp_change_users_login. sp_change_users_login updates the sysusers systemtable in the affected database to have the proper GUID.

    When transferring users from once SQL instance to another, the GUIDs are also transferred along with passwords.
    Beautiful things can be performed using XSLT/XPATH/XML, Yes, I'm a geek
  • moh_baradamoh_barada Posts: 46Member
    Thanks guys for your help. I let the customer recreate users from GP and it works fine...
    Best Regards
    Mohamad Barada
    Independent ERP & CRM Consultant
    +961 70 912700
Sign In or Register to comment.