Batch script for re-creating database from source control

Question

Hi Everyone,

As far as I understand, if I want to get my database under source control then I need to be checking in change scripts for each change and then running them from some revision to get the correct DB.

I am trying to make a batch file that will be checked in too, that allows other developers on the team to re-build the DB locally without too much trouble. I believe sqlcmd is the way to achieve this. I have it setup to enumerate all files in the dir of .sql files and run sqlcmd for each.

My question is who has done this before and do you have an advice on the best way to achieve this? Is the way I intend to do this the best way or is there a better way?

Hope that's not too vague.

Thanks in advance,

Martin.

Answer 1

One thing to be aware of is that you need to create objects in the order of dependency. So you cannot simply iterate files.

We ended up having a batch file that would list all the objects in order of dependency

Actually we had 2 batch files one called say createDBObject.bat:

:: Parameters Required:
:: %1 UserID
:: %2 Password
:: %3 Server
:: %4 Database
:: %5 file with scripted object
::
echo. >> CreateDBObjectsLog.txt
echo  %5 >> CreateDBObjectsLog.txt
osql -U%1 -P%2 -S%3 -i%5 -d%4 -n >> CreateDBObjectsLog.txt
echo * %5

and then the other with the list of all the db objects:

:: Parameters Required:
:: %1 UserID
:: %2 Password
:: %3 Server
:: %4 Database
::
echo object  in %4 database on %3 server
echo Please Wait ...

if exist CreateDBObjectsLog.txt del CreateDBObjectsLog.txt


call createDBObject.bat %1, %2, %3, %4, ScriptedTable1
call createDBObject.bat %1, %2, %3, %4, ScriptedTable2
...
call createDBObject.bat %1, %2, %3, %4, ScriptedTableN

call createDBObject.bat %1, %2, %3, %4, ScriptedView1

call createDBObject.bat %1, %2, %3, %4, ScriptedSP1

etc

Now we use SQL Compare Pro that automates all those tasks

You can also check related question: Is there a “poor man’s” alternative to RedGate for scripting out entire database schema?

Answer 2

If you are using a ORM tool like NHibernate you save yourself the trouble of having DB change scripts because the ORM can recreate the database from it's mapping files (that are under source control).

That's a pretty easy way of having the appropriate db version for every revision.

I also then recreate the whole schema whenever executing tests to make sure I have a consistent state.

I recently blogged about that: http://www.tigraine.at/2008/10/30/sourcecontrol-and-databases-when-orm-comes-in-handy/

I also once had a project that had Sql update scripts, and we just had a little helper tool built (very very basic) that opened the folder, sorted all scripts (we were naming them 1 - foo.sql, 2 - bar.sql) and executed them in order against the DB.

If a developer had a new script he simply added it to the end (34 - bla bla.sql).

Answer 3

If your company is willing to invest in some good tools it is worth checking SQL Compare Pro. It is well suited for automating/simplifying the tasks that you described. They have 14-days free fully functioning trial available so you can test it before investing any money.

Answer 4

It's an interesting point regarding the OR mapping framework being able to create the schema - I hadn't thought of that. I'm actually using LINQ To SQL, but I believe the schema and DB can still be re-created via the CreateDatabase() call.

Has anyone else taken this approach to database source control via LINQ To SQL?

Answer 5

I developed a small utility that helps me version my tables, SPs, triggers and Views by exporting them to text files: gljakal's Sql Exporter. It has a GUI mode and a command line mode (so I can use it in batch files).

Works on SQL 2005+.