Restore GP backup from SQL

moh_barada

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.

kriki

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.

geschwint

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.

moh_barada

Thanks guys for your help. I let the customer recreate users from GP and it works fine...