SQL server : How to re-link the authority of a restored DB with those IDs on the server?
Posted on 2013-01-28
We are now planning to upgrade one of our old system which use Windows 2000 and SQL 7 to Windows server 2003 (this ols system can only support up to Windows server 2003) and MS SQL server 2008. We have backup the whole DB into a bak file and transfer to the new server.
The new server is just installed with the Windows server 2003 together with all the windows patches. And the SQL server 2008 is also a blank SQL server. We created a blank DB with the same name to that of the DB that contains in the bak file and restore.
After the restoration completed (complete restore with original DB authorities). We found that the master DB don't have the corresponding IDs (since we can't restore the master DB as well), therefore, we try to create all the IDs one by one manually. Yet, even though the ID is really not appear in the master DB, but when we try to create, it prompts out that the ID already exist and can't create!!!
I found the restored DB is still marked with the ID that we are going to create but those IDs are actually not exist in the master DB.
May I know how to resolve this?
1) Any short-cut to re-generate those IDs without the need to create manually?
2) Any easy way to establish the same authority settings in the new DB server (both in master DB and the DB that need to restore)
Kindly please help.