Database under source control again!

Question

We use MS SQL Server and C#. Our database is under sourse control and I will tell you some details of our implementation. We had implemented two operations:

1. Export database to plain-text files. Database schema files: tables.sql relationships.sql views.sql ... and table contents files: Data/table1.txt Data/table2.txt ... It is easy to review database changes using source control logs because all these files has plain-text format. Imlementation is based on classes from namespace Microsoft.SqlServer.Management.Smo.

2. Import database from this plain-text files. Implementation is strightforward - just execute sql statements from *.sql files, and then execute a bunch of inserts.

So we have two bat-files: create-test-databse.bat and export-test-database.bat. When a developer needs a new test database he just executes the bat-file and waits for a minute. Every functional test, which needs a database creates a new database, uses it, and then kills it. But I should say that it is not very fast operation. :(

So what instruments do YOU use to put your database under source control? I mean how do you implement operations "create test database" and "export test database" for example?

Answer 1

We use Visual Studio for Database Professionals. Don't leave home without it.

Answer 2

I use VSTS for DB Pros. You point it at your SQL server and it analyzes your database and creates the individual files for you. You can even have it generate test data for you. The next release will include support for third party providers (think Oracle, MySQL, DB2).

The really great feature in here is the validation. We found that parts of our database were totally broken (they were vestigial, not used by the code anymore). It basically makes it possible to deploy your database on demand.

Answer 3

I think you are asking two questions here. The first is how to get your database under source control. Your solution is interesting, and I've also used Visual Studio Team Edition for Database Professionals (here's a tutorial I wrote on TDD of stored procedures using it)

The second is how to you set up your database for integration tests. Setting up and tearing down the entire database seems like it might be a bit overkill. There are several solutions out there. I've used DBFit. Roy Osherove published a tool called XtUnit a while back that I haven't played with. And, of course, you could always setup your tests to do a transaction start in the SetUp, and a Rollback during teardown.