I came to a new dev team where all development is on the same (instance) of an SQL Server (2005) database once in 1-2 days restored from a current full backup of production database replacing a dev database (simple recovery model).

So, the developers are essentially changinging stored procedures and some testing data whiich they really need between all perturbations (changes made back and forth) and then told that it is again time "X" when a testing database is to be replaced from production in a few minutes in order to backsave all their stored procedures and testing/development data needed for development.

The dev changes are being transferred to testing then to production database. The "updates" from production database to dev database are needed I don't know why but they happen frequently... once every 1-2 day and I can give here only some guesses, but it is status quo in the team.

This is really enerving and without some experience (at least for me ) leads to loosing a lot of developed/changed stored procedures and testing data plus always and for all .

What would be the best approach to get incremental changes (by users, purely in data, without any schema or programming) from production to dev database without replacing mostly incremental data and incremental programming (mostly in stored procedures) changes?

Note that we do not hide testing data in production database (they are mostly administrative data like testing/dev users which are anyway not visibly to normal users)

Update2 (answering comments):

I couldn't answer in comments

The dev changes to database are scripted and kept with solution in VSS but... But they are checked in only after passing testing on separate machine/asp.net site. And this is lagging a month. So, there is discrepancy between changes kept under version control and dev changes being overwritten by backup restore.

the dev database are completely overwritten by full backup restore from production database in order to get recent changes in data. So, any dev changes are being lost and should entered again - the programming ones by scripts (if they were not taken in time and not lost) and data changes by manual entering

  • If you need near to real time data have a look into transactional replication? For keeping dev in sync with prod. If the data not need to be near to real time, I would go for bcp out/ in or SSIS to load data every night or depending on your requirements. – Kin Shah Feb 27 '14 at 1:51
  • Maybe you can convince your team or/and the manager to invest into a SQL Source Control tool. – DenisT Feb 27 '14 at 2:06
  • Really, I started to use local instance of SQL Server but it is painful to see all others around... – Fulproof Feb 27 '14 at 3:41
  • 1
    The dev team needs to stop relying on the database to hold their changes. This means putting everything in scripts; all database objects most definitely, and also unit tests and test data if necessary. If source control isn't being used for database changes, that is the very first thing that needs to be done. – Jon Seigel Feb 27 '14 at 14:43
  • This question is really confusing. You say production to testing, then the opposite. Are you talking about data or DDL or both? Please try to clarify your question. Or draw a diagram. – Neil McGuigan Feb 27 '14 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.