Keeping SQL Server Dev Environment up to date

Question

Hi All,

I'm trying to figure out the best way to keep my Development servers synced up to the Production servers. Initially, I was just going to restore the backups from the production environment to the dev environment every week or so, however I realized that this would wipe out any actual development work that had taken place during the week.

I feel like this is a pretty common obstacle, so I'm hoping someone out there can shed some light on how they tackle the issue. I looked online briefly, but didn't really see anything, so any suggestions would be greatly appreciated.

Thanks!

Answer 1

Red Gate have a couple of products that you might want to look at:

• SQL Compare
• SQL Data Compare

They allow you to compare and synchronize schemas and data respectively between two databases.

Answer 2

Any change made to the development environment should, at some point, be pushed to production. Therefore, each change made on the dev environment must exist as a patch (sql script). To keep dev in sync with production you just restore the production backup and apply all the newly developed patches.