469,917 Members | 1,836 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Refresh data from production to development

Hi all, I've been assigned a task of refreshing data from the
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.
Jul 23 '05 #1
2 5662
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'

Jul 23 '05 #2

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


Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Aleksandar Andjelkovic | last post: by
1 post views Thread by JIM.H. | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.