restore problems

I have a db on msde on one server.
I"d like to replicate it by restoring a back up on another PC with a newly
installed MSDE.
I create a shell of the DB
I create a user name which my application uses to login with SQL
authentication.
I give the user db access to the empty-shell to be filled in by the backup
I proceed with the restore.
After the restore is done, the user I created now loses the access to the
db.
When I try to reset the dbaccess for the user, I get an error that says the
user already exists,
This is most liklely because that user name is the owner of some of the
tables in restored DB.
Any ideas as to how to do a restore correctly?
Many Thanks in advance
ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
figuring out what the heck a service account is.
hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:iqOdnZtH1JOec2PdRVn-rA@.speakeasy.net...
> I have a db on msde on one server.
> I"d like to replicate it by restoring a back up on another PC with a newly
> installed MSDE.
> I create a shell of the DB
> I create a user name which my application uses to login with SQL
> authentication.
> I give the user db access to the empty-shell to be filled in by the backup
> I proceed with the restore.
> After the restore is done, the user I created now loses the access to the
> db.
> When I try to reset the dbaccess for the user, I get an error that says
the
> user already exists,
> This is most liklely because that user name is the owner of some of the
> tables in restored DB.
> Any ideas as to how to do a restore correctly?
> Many Thanks in advance
> ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
> figuring out what the heck a service account is.
>
if the "user" shoul'd be the database owner as well, do not make the
corresponing login a dbuser by granting him access to the db..
just restore the db and change the db ownership to your login , which will
automatically grant him access and ownership rights...
EXEC sp_changedbowner 'login' , 'true'
please see
http://msdn.microsoft.com/library/de...ca-cz_30s2.asp
for it's related synopsis and further info
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I guess I shoudl have mentioned that the problem SEEMs to be that the
created user mysteriously loses its "login" name after the restore.
I found out that the query:
sp_change_users_login 'auto_fix', 'username'
Fixed the problem.
I've looked at sp_changedbowner and it just changes the owner of the db. It
does not fix the user problem.
sp_changedbowner accepts a login name as the parameter, and the problem was
that hhuser did not have a login name after the restore.
Now if I only could find a smoother way of doing this whole restore thing
without all the hassle of creating an empty db, creatina login, restoring,
and fixing the login!!
Sure looks like this ms product has a long way to go before it is user
froiendly even in its more common aspects like restoring a legitimate
backup.
|||hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:hc6dndtw6OTxRGLdRVn-hQ@.speakeasy.net...
> I guess I shoudl have mentioned that the problem SEEMs to be that the
> created user mysteriously loses its "login" name after the restore.
> I found out that the query:
> sp_change_users_login 'auto_fix', 'username'
> Fixed the problem.
>
> I've looked at sp_changedbowner and it just changes the owner of the db.
It
> does not fix the user problem.
> sp_changedbowner accepts a login name as the parameter, and the problem
was
> that hhuser did not have a login name after the restore.
> Now if I only could find a smoother way of doing this whole restore thing
> without all the hassle of creating an empty db, creatina login, restoring,
> and fixing the login!!
> Sure looks like this ms product has a long way to go before it is user
> froiendly even in its more common aspects like restoring a legitimate
> backup.
as regard this last sentence, my opinion is not Microsoft "has a long way to
go.." but just us understand how it works..
MSDE/SQL Server is NOT JET, and we have to take care understanding ith'
behaviours..
anyway,
http://www.sqlservercentral.com/colu...okenlogins.asp
Neil Boyle article about this related issue is worth reading, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply