Restoring a 2005 database from Prod to Dev

[TheBugSlayer]

Hi All.
I am asked to refresh the dev server with data from prod. I got a few objects in dev that are not in prod (principals, schemas, tables, sps, etc). This is how I plan to do it:

1- Import new dev tables into prod.
2- Script the entire dev database. Probably each object in a separate script file.
3- Backup dev.
4- Backup prod (this happens daily).
5- Restore the latest prod backup on dev recreating the database.
6- Recreate the objects in dev that are not in prod from the script in (2). This includes logins.

I believe something happens to the SIDs when you restore logins but don' t quite remember.

If you have any suggestions or know any better way I will be happy to hear them.

Thank you kindly.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mkal]

All of the sql logins will be orphaned when restoring a backup from one sql server to a different sql server. Your right in your thinking that it has to do with the SIDS since they will end up being different on the server your restoring too.

It's a simple process to update the logins by running the stored procedure sp_change_users_login, check BOL for more information.

I'm a little confused as to why you need step 6. If you've already added the new objects from dev to prod prior to the backup you shouldn't need to run the scripts from step 2. The objects should be there after the restore. Am I missing something here?

As an alternative to scripting this all out you could use SSIS and the copy database task.

 

[TheBugSlayer]

Thanks MKAL for the response. Step six is because I am not planning on recreating all objects that I have in dev on prod. So the logins in dev will not be created in prod but on dev after restore. I will assess the SSIS option. Please do let me know if you come up with anything else. Thanks again.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)