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):
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