production env to development env.
what i got is a backup file of a db in the prod env, i now need to
make that into the development env.
I can restore it to the dev env no problem, but the warnings i got are
that the tables owner in prod and dev env need to be different, that
is, owner is A in prod env and owner is B in dev env.
So I need to:
1) restore the db in dev env
2) change table owner from A to B
3) change related triggers
4) change related views
Can anyone suggest me an approach that is most efficient?
Thanks a lot.Hiya Bosco,
I'd create a script that dynamically builds a "sp_changeobjectowner"
statement for all objects that user A owns.. It'll simlify the steps
to this
1. Restore db in dev
2. Create and run permissions script
Syntax is as follows:
EXEC sp_changeobjectowner '<table_name>', 'B'|||Bosco
Here are some aricles that should help you.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
Hope this helps
John
