Ready to start programming SQL Server 2005? Here is a concise walk-through of the steps for obtaining and installing all the necessary software.

This walkthrough will explain how to find, download and install SQL Server 2005 Express Edition, Microsoft SQL Server Management Studio Express and the AdventureWorks sample database. These free Microsoft products are very popular as they provide a complete SQL Server development environment for Windows XP and Windows Vista. At the heart of today’s data-driven, interactive “Web 2.0” applications is a database that not only houses data, but seamlessly interacts with an application’s requests to manipulate and view that data. To do that, SQL database programming is required. Many would-be programmers use SQL Server 2005 Express Edition and Microsoft SQL Server Management Studio Express as a platform for developing their SQL skills.

Download the Software

To get started, download all the software installers needed. Just save them for now, as the actual installations will happen later. There are two prerequisite software packages needed in order to install SQL. Links are included here for convenience sake. Some are direct links to the software packages, others are links to the web page on which the software can be downloaded. Download and save .NET Framework 2.0, MSXML 6.0, SQL Server 2005 Express Edition, SQL Server Management Studio Express and the AdventureWorks sample database.

Install SQL Server 2005 Express: Learn how to install Microsoft's Free SQL 2005 Development Platform
Install SQL Server 2005 Express: Learn how to install Microsoft’s Free SQL 2005 Development Platform

Install the Software

Start the installation process with .NET Framework 2.0. Double-click on the installer file, dotnetfx.exe, and follow the prompts. There are no special settings or decisions needed for this installation. Next, install MSXML 6.0, again following the default prompts. Once those two installations are complete, install SQL Server 2005 Express Edition. This is a longer install process and there are a few prompts that ask for decisions. Stick to the defaults once again and this will serve well for the majority of installations. Finally, install Microsoft SQL Server Management Studio Express using all default settings.

Install AdventureWorks Sample Database

The AdventureWorks database is nice to have for the purpose of practicing SQL statements. Many SQL tutorials and examples use the AdventureWorks database so it helps to have one installed in the development environment. AdventureWorks also has an installer file that was downloaded. Run it and it will copy the database files to the appropriate locations. One common point of confusion at this point is that AdventureWorks does not yet appear as a database in Management Studio.

Attach AdventureWorks Sample Database to the SQL Server

On SQL 2005, this database must be manually attached to the SQL server. To do this, click on the “New Query” button on the Studio toolbar to open a Transact-SQL console window. This is where SQL commands can be entered directly to interact with the SQL server and databases. Type the following command:

exec sp_attach_db @dbname = N’AdventureWorks’,
@filename1 = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf’,
@filename2 = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf’

Keep in mind that the “filename1” and “filename2” file paths listed above will work if the default file locations were unchanged during the installation of SQL Server. If they were changed to a different file path, change the statement accordingly. To execute the SQL statement, click on the “Execute” button in the toolbar above the console window or type Ctrl-e. A message window will appear below the command and should report “Command(s) completed successfully.” Lastly, right-click on the “Databases” folder in the “Object Explorer” pane on the left and select “Refresh.” The AdventureWorks database will now appear in the list of databases. The process is now complete and a fresh SQL development environment is waiting to be explored.