Problem: How can I QUICKLY create an install script automatically in sql server every time I change my database? Creating an install script in sql server is pretty easy thanks to the "Script Database As..." command. However, this is generally a long winded process that doesn't give you too much control.
In this post, I propose a solution to create build scripts with the following requirements
- Don't need to write any code
- Easily maintainable
- Can script schema AND data
- Can specify which objects to script
- Can run automatically before an MSI setup/deployment project is built
- It has to be FREE
The first thing you need to do is download Microsoft's Database Publishing Wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en)
Next, in your development sql server, add two new accounts named: ScriptSchema and ScriptData. Make sure they are created as a sql login. At this point, don't give them any other access than public access to the database you want to script.
Open the database you want to script. For each table/view/stored proc etc that you want to include in the install, grant view definition access to the ScriptSchema user as follows:
grant view definition on <schema>.<object name> to scriptschema
Next, for each table that you want to script the data for, grant select permission on that table to the ScriptData user, ie:
grant select on <schema>.<table name> to scriptdata
You can double up, so for instance if you want to script the creation and population of data of a certain table called [dbo].[customers] you'd run the two commands:
grant view definition on [dbo].[customers] to scriptschema
grant select on [dbo].[customers] to scriptdata
Once you've set up the permissions, you can check out what will be scripted by connecting to the database as the scriptschema and scriptdata users. As you've now defined what will be scripted, it's time to do the scripting itself.
Jump out into a cmd.exe, and run the two commands;
sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptschema; pwd=password;" schema.sql -schemaonly -f
sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptdata; pwd=password;" data.sql -dataonly -f
The first command connects to the database using the scriptschema account, and generates the definition of all the objects it can see, ie: those which you gave grant view definition access to.
Likewise the second command connects to teh database using the scriptdata account and generates the scripts to insert the data, based on all the tables it can select from.
You now have two scripts: schema.sql, data.sql. You can add these to your msi/setup projects in visual studio (outside the scope of this post). Furthermore you can then add the two commands mentioned to the pre-build events of your setup's resource project to build the install scripts prior to the MSI etc being built.
I really like this solution to automatically generate sql install scripts. I feel it's elegant, there's no code to be maintained, and no manual updating of sql scripts every time you change the database.
posted @ Friday, April 18, 2008 12:11 PM