topic How to restore SDE database in SQL SERVER2008 in Data Management Questions

How to restore SDE database in SQL SERVER2008

jessieleo — Tue, 18 May 2010 13:06:51 GMT

Hi,

Does anyone have experience to restore SQL SERVER database from SDE point of view?
I restored a database in SQL Server2008, the database name exactly the same as the original one. while I couldn't access from ArcCatalog, the error message was "Bad Login User". I check the permission of user, there was no problem.

Thank you for your help.

jessiezhangleo@hotmail.com

Jessie

Re: How to restore SDE database in SQL SERVER2008

CherylCleghorn — Wed, 19 May 2010 13:20:44 GMT

Jessie

Did you sync the logins for the database users with the instance logins using the exec sp_change_users_login?
Eg
use <database>
go
EXEC sp_change_users_login 'Update_One', '<user>', '<password>'
go

If the login does not already exist on the instance, you can also use auto_fix if, according to microsoft, you are not in security-sensitve situations. e.g.
EXEC sp_change_users_login 'Auto_Fix', '<user>',NULL, '<password>'

More info can be found at Microsoft's MSDN Library doc.

Cheryl

Re: How to restore SDE database in SQL SERVER2008

tongkimongki — Thu, 20 May 2010 09:22:51 GMT

Hope this help:

*For sqlserver 2005 you can run this (this might work, i havnt tested yet). This is used when you do restore by copying the mdf file. I dont know why these codes do not work in sqlserver 2008

Use sde
Exec sp_change_users_login 'update_one', 'sde', 'sde'
Exec sp_change_users_login 'update_one', 'username', 'pwd'

Use yourdb
Exec sp_change_users_login 'update_one', 'sde', 'sde'
Exec sp_change_users_login 'update_one', 'username', 'pwd'

*For sqlserver 2008 you can run this. This works when you restore database from backup file :

use sde
go
EXEC sp_change_users_login 'Auto_Fix', 'sde',NULL, 'pwd'
go

use yourdb
go
EXEC sp_change_users_login 'Auto_Fix', 'user',NULL, 'pwd'
go

use yourdb
go
EXEC sp_change_users_login 'Auto_Fix', 'sde',NULL, 'sde'
go

use sde
go
EXEC sp_change_users_login 'Auto_Fix', 'user',NULL, 'sde'
go