While setting up a Windows Workflow project, I recently spent some time trying to apply the stock SqlPersistenceService_Schema.sql and SqlPersistenceService_Logic.sql scripts to my standalone SQLEXPRESS .mdf database.  What was not obvious to me at the time was how to execute .sql scripts against SQL Express in Visual Studio 2008.  The way I’m assuming most people feel their way through database management in Visual Studio is through the Server Explorer window and by adding database items to a project directly.  Unfortunately, neither of these provide an interface for running a Transact-SQL script to populate schema, data, logic, etc.

One of the first answers I found works on the default database format and uses the sqlcmd utility as follows:

sqlcmd -S localhost\SQLEXPRESS -E -Q "create database WorkflowPersistenceDB"
sqlcmd -S localhost\SQLEXPRESS -E -d WorkflowPersistenceDB -i SqlPersistenceService_Schema.sql
sqlcmd -S localhost\SQLEXPRESS -E -d WorkflowPersistenceDB -i SqlPersistenceService_Logic.sql

(For those interested in creating WorkflowPersistenceService databases, the above scripts are located in C:\Windows\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN.  Make sure to run the schema script first.)

I ran into trouble while trying to derive the sqlcmd arguments to target C:\myDatabase.mdf.  Finally, I came across this article, which pointed out the Database Project template under Other Project Types in Visual Studio 2008.  Here’s how it goes:

• Create a new database by adding a Service-based Database item to a project.
• From the Server Explorer window, right-click on Data Connections and select “Add Connection”.
• Set “Server name” to ComputerName\SQLEXPRESS.
• Under “Connect to a database”, select “Attach a database file” and browse to the .mdf file on disk.  Click OK.

• Add a new project to the solution.  Select “Database Project” under “Other Project Types – Database”.
• When prompted to Add Database Reference, select the connection that was just added.
• Add the .sql scripts to the Create Scripts folder, or in general, whichever folder is appropriate.
• Right-click on the .sql item and select “Run”.

I’m guessing that there are other ways to do this.  Based on the wizard, it would seem that there’s a related interface in sqlcmd, something like this or this.  I believe that there’s an easy way to execute .sql scripts from the SQL Server Management Studio as well, if you have a full version of SQL Server.  For those just using Visual Studio, use the Database Project template.