Keeping development databases in multiple environments in sync

There are obviously a number of ways to approach this, so I am going to list a number of links that should provide a better foundation to build on. These are the links that I've referenced in the past when trying to get others on the bandwagon.

• Database Projects in Visual Studio .NET
• Data Schema - How Changes are to be Implemented
• Is Your Database Under Version Control?
• Get Your Database Under Version Control
• Also look for MSDN Webcast: Visual Studio 2005 Team Edition for Database Professionals (Part 4 of 4): Schema Source and Version Control

However, with all of that said, if you don't think that you are committed enough to implement some type of version control (either manual or semi-automated), then I HIGHLY recommend you check out the following:

• Red Gate SQL Compare
• Red Gate SQL Data Compare

Holy cow! Talk about making life easy! I had a project get away from me and had multiple people in making schema changes and had to keep multiple environments in sync. It was trivial to point the Red Gate products at two databases and see the differences and then sync them up.

In addition to your database CREATE script, why don't you maintain a default data or sample data script as well?

This is an approach that we've taken for incremental versions of an application we have been maintaining for more than 2 years now, and it works very well. Having a default data script also allows your QA testers to be able to recreate bugs using the data that you also have?

You might also want to take a look at a question I posted some time ago:

Best tool for auto-generating SQL change scripts

You can store backup (.bak file) of you database rather than .MDF & .LDF files.
You can restore your db easily using following script:

use mastergoif exists (select * from master.dbo.sysdatabases where name = 'your_db')begin    alter database your_db set SINGLE_USER with rollback IMMEDIATE    drop database your_dbendrestore database your_dbfrom disk = 'path\to\your\bak\file'with move 'Name of dat file' to 'path\to\mdf\file',     move 'Name of log file'  to 'path\to\ldf\file'go

You can put above mentioned script in text file restore.sql and call it from batch file using following command:

osql -E -i restore.sql

That way you can create script file to automate whole process:

• Get latest db backup from SVNrepository or any suitable storage
• Restore current db using bak file