I have been tasked with setting up a SQL Development environment, and i was wondering if anyone has any advice on getting this right?

So far, I have restored the prod DB onto another box, and changed all our SSIS packages to use XML configuration files so we can deploy them to either environment without changing the DB connection setting.

My plan was to restore the prod DB over the dev DB every 2 weeks (assuming its agreed by all the other developers). Also, i was thinking i would have a UAT DB restored every 1 or 2 weeks to test deploying the changes to prod. The UAT DB would only exist for a day or 2 to test things, and then the actual deployment would happen into the prod environment.

Can anyone thnk of anything else that would be good to do, or has any advice on other considerations etc?

  • We need a lot more context to answer your questions. How many developers? How many projects? What is the cycle time on your projects? Do you have development projects (writing a lot of code) or mainly ETL projects? Do you have multiple databases interacting with each other? Sep 6, 2014 at 12:52
  • What are the problems with your current system that the new environment is going to solve?
    – JeffO
    Oct 6, 2014 at 15:19
  • I'm curious to know why you need Production data constantly refreshed to a DEV database on such a tight interval in the first place. Could you tell us more about your situation?
    – ravibhagw
    Nov 5, 2014 at 16:24

1 Answer 1


You want developers reaching a consensus whenever the development database is restored? Good luck with that!

I'm used to working with a similar system, but one in which the development and UAT databases are copied down from production at midnight every night. One of the great benefits of this is that your reality and the reality of production are never different for more than 24 hours (baring other changes in development). To compensate for the work lost every night, each developer has to log their scripts with a process that automatically re-runs them after the database servers have been restored from the production server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.