Monday, April 11, 2011

Codeplex : ExportSqlScript

I'm currently looking a replacing a product scripting system. It is command line driven (not a bad thing), but suffers from being overly complex. To apply changes, they need to be scripted to 3 places which is far from ideal. I need to create database build scripts that will support multiple versions, service packs and patches for a core product.

Redgate's SQL Compare is the obvious answer to see the initial state of play, but I cannot roll that out to the deployment team and tell them to get on with it. As much as I'd like to.

For this reason, some googling had led me to the perfect starting block, a codeplex project called 'ExportSqlScript'.

It is command line driven, and runs like this

ExportSQLScript.exe localhost adventureworks /ot:Tree /of:CreationOrder.txt /od:"b:\test2\ExportSqlScript" /ssq

This creates a folder for each object type (screenshot below) as well as CreationOrder.txt

CreationOrder.txt can be used to recreate the database as it takes into account object dependencies i.e. it creates views and constraints AFTER dependent tables and a functions / stored procedure after related tables / views. In theory it should match Jamie Thomon's Dependency Order script, that I always keep handy.

You can run scripts in order like this -
for /F "" %i in (CreationOrder.txt) do osql -Uuser -Ppassword -S localhost -d databasename -f 65001 -i %i

(Change %i for %%i inside a batch file)

No comments: