Keeping development databases in multiple environments in sync

Question

I'm early in development on a web application built in VS2008. I have both a desktop PC (where most of the work gets done) and a laptop (for occasional portability) on which I use AnkhSVN to keep the project code synced. What's the best way to keep my development database (SQL Server Express) synced up as well?

I have a VS database project in SVN containing create scripts which I re-generate when the schema changes. The original idea was to recreate the DB whenever something changed, but it's quickly becoming a pain. Also, I'd lose all the sample rows I entered to make sure data is being displayed properly.

I'm considering putting the .MDF and .LDF files under source control, but I doubt SQL Server Express will handle it gracefully if I do an SVN Update and the files get yanked out from under it, replaced with newer copies. Sticking a couple big binary files into source control doesn't seem like an elegant solution either, even if it is just a throwaway development database. Any suggestions?

Answer 1

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

use master
go

if exists (select * from master.dbo.sysdatabases where name = 'your_db')
begin
    alter database your_db set SINGLE_USER with rollback IMMEDIATE
    drop database your_db
end

restore database your_db
from 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 SVN repository or any suitable storage
• Restore current db using bak file

Answer 2

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.

Answer 3

We use a combo of, taking backups from higher environments down.
As well as using ApexSql to handle initial setup of schema.
Recently been using Subsonic migrations, as a coded, source controlled, run through CI way to get change scripts in, there is also "tarantino" project developed by headspring out of texas.

Most of these approaches especially the latter, are safe to use on top of most test data. I particularly like the automated last 2 because I can make a change, and next time someone gets latest, they just run the "updater" and they are ushered to latest.

Answer 4

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