I teach an ASP.NET Programming II course at Univeristy of California - San Diego Extension that, for the final project, involves creating a forum site not unlike the ASP.NET Forums. To make the project easier to move from lab to home to work to me for grading, I encourage my students to use SQL Server 2005 Express Edition databases in the App_Data folder.
Earlier in the class the students created a MessageBoard database created with, at minimum, tables for storing Forums, Threads, and Posts. Later, after learning about Membership, they needed to add the membership and roles-related tables to their existing database. This is a lot easier said than done when using SQL Server 2005 Express Edition.
If you use the SQL Server 2005 Standard Edition you can simply use the aspnet_regsql.exe tool's little wizard, point it to your SQL Server 2005 database, provide the credentials, and you're done. Alternatively you can run this tool from the command-line, using something like (with the -E using a “trusted connection,“ although user ID/password credentials can also be supplied):
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
While the above may make help fulfill any geek quota you're trying to maintain, the easiest approach, by far, is to just download the SQL Server 2005 Management Studio Express product. With that you can work with SQL Server 2005 Express Edition databases much like you would with standard and up versions.
Hope this helps!