Adding sample data to SQL Server 2005 Express


Yes, finally SQL Server 2005 Express Beta 1 has been released !
SQL Express is the new name for Microsoft’s MSDE database, the (free) light-weight database version of SQL Server.  SQL Express uses the same high-performance database engine as the other versions of SQL Server 2005. In fact, it is differentiated from the rest of the SQL Server 2005 editions only by the following:
   –
Lack of enterprise features support
   –
Limited to one CPU
   –
One GB memory limit for the buffer pool
   –
Databases have a 4GB maximum size


Download SQL Express Beta 1 here: http://lab.msdn.microsoft.com/express/sql/


 


Ok, so I installed SQL Express Beta 1 on my machine and started playing with it. One of the first things you’ll notice is… there’s no UI (yet).


The UI options for SQL Express include Express Manager (XM) and Computer Manager. SQL Express Manager (XM) will be available with Beta 3 of SQL Server later this year.  However, we’ll try to get a preview release out sometime this fall. Computer Manager is available with the current Beta 1 build however.


 


So onto the Command Prompt ! (wooohooo)


SQLCMD.exe will be your friend to explore the Beta 1 build of SQL Express. Here’s how:


– Open a Command Prompt


– SQLCMD -E -S servername\instancename  (e.g.  SQLCMD -E -S guntherb01\sqlexpress)


– select name from sys.databases


– go


 


You’ll find a list of the default databases in your SQL Express installation (master, tempdb, model, msdb). However, no sample databases – nothing !


 


So here’s an (unofficial) tip to get some sample data in SQL Express (finally, this post is way to long) 🙂
   – Download and install Pubs & NWind T-SQL scripts:
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en


   – SQLCMD -S servername\instance -E (or -U sa -P password)


   – :r “C:\Program Files\Microsoft SQL Server 2000 Sample Database Scripts\instpubs.sql”


   – :r “C:\Program Files\Microsoft SQL Server 2000 Sample Database Scripts\instnwnd.sql”


To verify you have successfully loaded the db’s :
– select name from sys.databases
– go
– Use pubs
– select * from authors
– go


Note that these T-SQL scripts were designed for SQL2000 (hence unsupported), but hey, they seemed to run perfectly on my SQL Express machine.


Now start enjoying SQL Express 2005 beta 1 !!!


Comments (7)

  1. Samuel Jack says:

    Will SQL Server Express support Full-Text indexing and searching? or is that counted an enterprise feature?

  2. GuntherB says:

    Hi Samuel,

    Thanks for your post!

    We don’t plan making Full-Text Indexing part of SQL Server Express. The primary reason that full text search was not included in SQL Server Express was to keep the size of the SQL Express download as small as possible. Right now we are at 37MB download size for SQL Server Express Beta 1 and our goal is to get down to 25MB.  Including Full-text search would nearly double the size.

    Hope this makes sense. However, if you have scenarios that would justify the inclusion of FTI in SQL Express, please let the SQL product team know, by submitting a bug here: http://lab.msdn.microsoft.com/express/sql/bugs/

    Thanks!

    GuntherB

  3. qijie says:

    it is nice.

  4. veer says:

    I was also trying to attach the Sample Dbs in Express from last few days..But now able to do…

    Also Is there way to get teh SQl Script of any DB once done in Express????

    Also wanted to know about :r that U used in the syntax…

    I would be publishing part of your Blog at my blog as well…

    Hop[e you dont mind…

    Regards

    veeru

  5. GuntherB says:

    Hi Veeru,

    Thanks for your post!

    Currently there’s no GUI for SQL Express yet, but stay tuned as we’re working on getting sth out. I don’t know if it will include a generate T-SQL scripts of your Express DB, but I think you can away with VS 2005 or the full SQL Management Studio (the new Enterprise Manager).

    Regarding the ":r" switch: It parses T-SQL statements from the file specified by the filename into the statement cache. The file will be read and executed after a batch terminator (go) is issued.

    I recommend downloading the SQL Express BOL (Books-On-Line), they do not get distributed with the SQL Express Beta 1 installer. Feel free to download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en

    In there, you’ll find the nitty gritty details of SQLCMD.exe which will be your friend until the GUI ships.

    Thanks!

    GuntherB