Refresh / Synch Dev Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Refresh / Synch Dev Database

Author

Topic

CanadaDBA

583 Posts

Posted - 2011-01-05 : 10:35:46

Once in a while I have to refresh Development databases from Production. I have some scripts for each database and do the work in Dev as below:

- Drop and recreate the database
- Import all tables from Prod.
- Add Users, Schemas, Roles, and Stored Procedures
- Add Identity to tables
- Add indexes, constrains, and Triggers
- Add Permission Rights

But there are some disadvantage to this work:
1. generating the original scripts is time consuming.
2. keep the scrips up-to-date is hard and you have to regenerate from scratch to be in safe side.
3. the refresh process is a manual work.

Can you suggest any approach to Refresh Dev database, without restoreing the production backup? How should I synch my Dev databases?

Thanks,

Canada DBA

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 12:04:46

We just copy data, table by table, "UpSerting" changes only:

Delete it no longer present on Target
Update it present on both but one, or more, columns is different
Insert it only present on Source

All deletions are performed first in child-parent order where FKeys are present
All Updates next, then all Inserts in parent-child order.

We do have some situations where parent-child order is not enough - on those we disable Fkeys and re-enable them afterwards.

Can be a bit of a pain if the database structure on DEV has changed.

We make all DDL changes via scripts - so if we add a column, or change one, we script that - we NEVER just change the object in situ.

Just we can take an earlier version of the database and run all the "upgrade" scripts on it to bring it to a later version.

So we can:

Copy data to a same-version DEV database
Then upgrade the DML to latest version
(and then run all scripts for changed Sprocs / Triggers etc.)

You could do similar by using RedGate Database Compare which give Version 1 and Version 2 databases would script all the changes to get from Version 1 to Version 2 - which you could then apply to another "Version 1" database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources