Creating Databases in SQLExpress

As much for my benefit as I tend to forget these details when I don't use the software frequently.

I'm working with VSTS and in particular the unit testing bits. Last night I went to create a database to store the tables for doing data driven testing. As I was working in Visual Studio I wanted to create the databse there because when I teach this stuff I want to show the students how to use the tools availlable. I also wanted to use SQLExpress as that will be available to most folks when they take away demos or examples whereas SQLServer may not be. Eventually I discovered several ways of creating SQLExpress databases and I wanted to document them here.

In VS2005 you open the server explorer and right click on the 'Data Connections' then choose 'Create New SQL Server Database...'

This gives the following dialog

.

Notice that the name given to the server is ".\SQLExpress" i.e. the version of SQLExpress running on this machine, after entering the filename you can think click on OK.

This will create the database on this machine. This is fine, but not quite what I wanted. The created database files are stored in "C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data" which is a problem for two reasons. First: I run as non-admin on my machine and this directory is not available to me; Second: I would like the database files (the .mdf and .ldf files) to be in my application directory so that I can distribute them easily. To do this I would have to first copy the files to the directory I was using, then drop the database from SQLExpress and re-attach to the files in the new location. I can't find anyway to drop files from VS2005 server explorer, if anybody knows of a way then let me know.

Option two was to use "SQL Server Management Studio" that ships with SQLServer 2005, there will be a SQLExpress "Express Manager" but this is not yet available. Sure enough in the Management Studio I can attach to SQLExpress and create a databse whereever I want

.

Again this is fine, so log as my system has SQL Server 2005 installed, but again I see two problems. The first is I may not have the full SQL Server installed, just SQLExpress, and secondly, I like to see what is going on and so understand how to create these databases myself without help from a graphical tool (I'm a console kind of guy!).

With both SQL Server 2005 and SQLExpress Microsoft ship a new command line tool called sqlcmd. This is installed at "C:\Program Files\Microsoft SQL Server\90\Tools\Binn" on my install. You can start a command prompt and use the fully qualifed path name, if you install SQL Server you should have a entry off the start menu for this. The entry will be under "All Programs..Microsoft SQL Server 2005..Configuration Tools" and is called the "Notification Services Command Prompt". This is a command prompt with the path set correctly so that the SQL tools can be used.

To use sqlcmd to access SQLExpress you run it like this sqlcmd -S .\SQLExpress note that the switches are case sensitive.
To run as a specific user you use

sqlcmd -U sa -S .\SQLExpress

1>

exit

To create a databse at a specific location the SQL looks something like

USE [master] 
GO
CREATE DATABASE Test ON PRIMARY (NAME=N'Test', FILENAME = 'c:\temp\data\Test.mdf') 
GO

USE [master]
GO
CREATE DATABASE TEST ON PRIMARY (NAME=N'TEST', FILENAME = 'c:\temp\data\Test.mdf') 
 LOG ON ( NAME = N'Test_log', FILENAME = N'C:\temp\DATA\Test.ldf' )
GO

Any SQL guys reading this is either going "d'uh! obvious", or "d'uh" moron, didn't you know you could do XXX". In the first case, this entry is for users new to SQLExpress that may only be using it for simple work, in the second case please correct me :)