Using ASPNET_RegSQL.exe with SQL Express databases in APP_DATA

I was playing with some of the PDC05 session samples (session PRS404) again this weekend and the sample had a script to extend the default ASP.NET membership and role tables.  I could have just used my ASPNETDB instance installed on SQL Server 2005, but I didnt want to impact anything I was working on with my "real projects".  Therefore, I needed yet another ASPNETDB instance to apply their sample SQL Scripts on.   I toyed with temporarily detaching the current ASPNETDB instance from SQL2005 and creating a new DB as a one-off, but that left me feeling a bit dirty and it wouldnt help me when I needed a 3rd instance to play with.  Then, I recalled the power of using file-system attached SQL Express Databases offered in the new version.  I had played with this earlier in the beta's when using the SQL Express provider to attach a DB located within the APP_DATA folder of my website, and it seemed to be a perfect solution. 

Creating a new DB in VisualStudio.NET 2005 is as simple as "Select APP_DATA node -> Add New Item -> Sql Database" and wah-lah you have a new aspnet.mdf file located in your APP_DATA folder.  However, when you run the tool ASPNET_REGSQL.exe in Wizard Mode (E.g. using the "-W" switch) there is no way to specify a SQLEXPRESS attached database - it only seems to support SQL Server 2005 (and earlier) database servers.    So, my next thought was to just export the SQL that is generated by the tool and run it manually.    This option works fine:

     aspnet_regsql.exe -E -A all -sqlexportonly AspNetPandMscript.sql

It exports a SQL Script as expected.  However, when you try to run this script, it fails because it tries to be "smart" and check for the existance of the appropriate DB name and invokes other built-in objects in the MASTER db that are typically available in SQL 2005, yet don't exist by default in SQL Express.  I started to try and hack the script, but quickly decided it was too much trouble

So, after several attempts, I finally figured-out the "right" way to do this:

     aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\MyProject\APP_DATA\aspnetdb.mdf"

This will connect to the local SQLEXPRESS engine and attach the MDF file passed in the "-d" switch then create the appropriate objects in the DB.   I'm sure this is documented somewhere, but hopefuly this will help me, or someone else who needs a quick answer the next time you try this.

Enjoy...