Friday, 4 November 2005

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

You should see a prompt (afer entering a password) that looks like

1>

You can type

exit

to exit SQLCommand or you can enter you SQL code.

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

or with a specific log file name

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 :)

Posted by kevin at 9:57 AM in Net

Responses (5)

DRM and Rootkits

As my friends would tell you I really hate DRM. I like to buy CDs and I also think that the CDs I buy belong to me, I'm not licensing the content, I've paid and I own it. I like to rip the CDs to my PC at home and then onto my IPod. I don't give copies of the CDs to my friends, I don't use Kazaa or other file sharing software, and I don't sell bootlegs on street corners. I like to think I am an honest person and from this point of view DRM hurts me. DRM also has the bizarre side effect of making honest people dis-honest.

For example, myelf and my family are fans of Lost, but, being in the UK, we can't follow series 2. I bought a copy of the series 1 DVD in the US when I was there in September, and we watched the DVDs inside a week a so. My kids wanted to watch Series 2, so I thought I'd be go get the episodes on-line. I go to the Apple music store and try and download the episodes, turns out I need a US billing address to get the episodes, as I'm in the UK they're not available to me.

I have lots of friends in the US and I ask one of them to download the first episode for me, which they do, so I grab the episode and of course it's DRM'd and I can't unlock it on my PC. This is an episode that I've paid for and only my family will watch. At the moment nothing is available to remove this DRM so I have to either wait until Lost Series 2 becomes available in the UK, or I have to get an illegal copy of Lost using BitTorrent or something similar!

Which brings me to the real point of the post. Sony have released a DRM'd CD which installs a root kit on your PC when you install the software on the CD. This was brought to the world's attention by Mark Russinovich here, and has been discussed in many places. I just wanted to point at this excellent Inquirer article which starts

SONY SCREWED UP WITH its rights removal to protect its profit margins philosophy and there is no way the use of rootkits can be justified.
Caught with its pants down, what did it do? Make things right? Heck no, it blamed the user, and doesn't do anything more than window dressing to deflect what are valid criticisms.

Being an optimist by nature I hope that the furore surrounding this will bring companies like Sony to their senses, but realistically I doubt it!

Posted by kevin at 7:45 AM in General

Responses (1)