In the instructions below, substitute your UW Net ID wherever you see "uwnetid".

Last updated: 9 Oct 2012

• Overview
• Starting SQL Server Express
• Backing Up your SQL Server Express Database
• Restoring your SQL Server Express Database
• Stopping SQL Server Express
• Workflow

SQL Server

Microsoft SQL Server is a complex piece of software. In our lab environment, it can be installed two ways: as part of the Visual Studio Professional installation, or as a separate product. When it is installed via Visual Studio Professional, it is SQL Server Express and is configured to work in that environment, with minimal administration. When it is installed as a separate product, it can be any edition of SQL Server; if it is installed prior to Visual Studio Professional's installation, Visual Studio will use that edition.

This document addresses SQL Server Express installed as a part of Visual Studio, but SQL Server Express is not limited to just working in concert with Visual Studio. You can use it as a general database system, most likely by using the "SQL Server Management Studio" application or via the Java Database Connectivity (JDBC) driver.

Starting SQL Server Express

The SQL Server Express service is not started by default. The rationale for this is that we don't want a database server always running on a workstation, consuming resources and being available for network users. Consequently, it must be started manually via a command shell:

g:\start_mssql

Then you can start to use SQL Server Express. If you are using SQL Server Management Studio, you may have to use .\SQLEXPRESS in the connection string instead of the default IT30xxxxx\SQLEXPRESS IT30xxxxxx is the name of the lab computer you are logged into -- the purple tag number on the front of the computer is the numeric part.

Be careful how you use SQL Server. SQL Server stores its databases on the computer you are currently using (i.e., locally), and you may not want to or be able to use the same computer to continue working later on. Stated another way, your database won't roam with you.

There is a supported way to make your database roam with you, but it takes more effort on your part. You need to backup the databases you create. If you are going to use only one database, the simplest thing to do is to name it the same as your UW Net ID. For example, if your UW Net ID is srondeau, you could create a database called srondeau. If you do so, it is simple to backup your database before stopping SQL Server Express, and then later restoring a backed up database after starting SQL Server Express, because the scripts that you use will presume the database name is your UW Net ID.

Backing Up your SQL Server Express Database

To backup a database called "uwnetid":

g:\mssql_backup

If you chose another name or have more than one database, backup them up by providing an argument to the script -- the name of the database. For example, let's say the second database is named survey:

g:\mssql_backup survey

Note that your home directory is where the script ultimately places your backups (in H:\mssql.backups). There must be enough space there to hold the backup, and home directory space is limited, so your databases can't be very big. If the script can't copy the file from the temporary file (C:\temp\dbname.bak, where "dbname" is your database name) to your home directory, it won't provide a confirmation that it did.

When a database is backed up to a file using this script, the database is then deleted or dropped from SQL Server to allow room for another user to run.

Restoring your SQL Server Express Database

To restore a database called "uwnetid", SQL Server Express must be started:

g:\mssql_restore

If you chose another name or have more than one database, restore them by providing an argument to the script -- the name of the database. For example, let's say the second database is named survey:

g:\mssql_restore survey

Please note that you will need to backup every restored database if there were any changes made to it.

Stopping SQL Server Express

It is a good practice to stop the database after you are finished using it for this login session. You can stop it manually via a command shell:

g:\stop_mssql

Workflow

• First time you create a database (i.e., your first database "session"):
  1. Start SQL Server
  2. Create the database
  3. Work with the database
  4. Backup the database
  5. Stop SQL Server
• Subsequent sessions
  1. Start SQL Server
  2. Restore the database
  3. Work with the database
  4. Backup the database
  5. Stop SQL Server

Change Log