aspnet_regsql with SQLExpress database

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, 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.
================================================================

aspnet_regsql.exe -S server -d database -E -A all

While this concept still applies for SQL Server 2005 Express Edition, it can be a little harder to get the server and database names right. What database server is SQL Server Express installed on? And what's the database name for a .MDF file in the App_Data folder?

Assuming you are working on an ASP.NET application locally, the server name will be: localhost\SQLExpress

The database name is (and here's it can get a bit tricky), is the path to the MDF file when it was created. So, say that you have an ASP.NET application created in the classroom lab at C:\Labs\Website\App_Data\MessageBoard.mdf. The name of the database is C:\Labs\Website\App_Data\MessageBoard.mdf, meaning you could install the membership services from the command-line using:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Labs\Website\App_Data\MessageBoard.mdf” -E -A all

Now, imagine that you zip up your files onto a USB keychain drive, go home, and copy your project files to C:\Home\Website. Now, if you wanted to create the services, you'd think you'd just type in:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Home\Website\App_Data\MessageBoard.mdf” -E -A all

Ah, but the database name is C:\Labs\Website\App_Data\MessageBoard.mdf. Eep. So when you run the above command the database can't be found and cryptic error messages abound. Essentially, it can't find the database C:\Home\Website\App_Data\MessageBoard.mdf so it tries to create a database file in the default directory (%PROGRAM FILES%\Microsoft SQL Server\MSSQL.1\DATA) with the filename C:\Home\Website\App_Data\MessageBoard.mdf. This, of course, causes problems since that's not a valid filename. Ick.

So how do we fix this? There are a couple optios. The easiest is probably to download the (free) SQL Server 2005 Management Studio Express program and attach the database file. Then, from the Properties pane you can see the database name. You can then use this with aspnet_regsql.exe. (You could also rename the database at this point...)

If you want to be 3l33t you can use sqlcmd, attach the database (sp_attach_db) giving it a friendly name, which you can then use to run the aspnet_regsql.exe command line program against. Something like:

sqlcmd -S localhost\SQLExpress -Q “EXEC sp_attach_db 'Foobar', N'pathToDBfile'”

And then:

aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all