Microsoft SQL Server forum - Refresh data from production to development

Author

Refresh data from production to development

Bosco

2005-04-26, 3:23 am

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.

Greg

2005-04-26, 3:23 am

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'

johnbandettini@yahoo.co.uk

2005-04-26, 7:23 am

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

Sponsored Links