MS SQL Databases

From CSWiki

MS SQL Server 2005 has been installed on all the lab machines in Dundee, along with the four Windows machines in the Doucette lab. Students can use MSSQL to create databases for various classes, projects, or just for fun.

Logging On

Students and faculty should be able to log in to the local SQL database server by opening "SQL Server Management Studio" from the "Microsoft SQL Server 2005" folder within the start menu, under "All Programs." When logging in, the default variables should be acceptable, but if they have changed for some reason, use the settings below to log in:

• Server Type: Database Engine
• Server Name: localhost\SQLEXPRESS
• Authentication: Windows Authentication

Database Ownership

When creating or importing a database, the owner needs to be set to "SA" to enable database diagrams, along with some other features. This can be accomplished using the following steps:

• Right click on the database container and select "Properties."
• On the left side of the window that comes up, select "Files."
• On the right side of this same window, change the name of the owner to "SA."

Once this is complete, you can create database diagrams!

Machine Dependence

Each time you create a database, it is created on that local machine only! Make sure that you create a backup copy of your databases!

This can be easily done in MS SQL Management Studio by right-clicking on the database, and selecting "script database as -> file". This will save an SQL query file which will create the database. This process needs to be repeated for each table, trigger, etc. Fianlly, to save any data which is in a table, take the following steps:

1. Right-click on the table and select "script table as -> select to -> new query window". 2. Right-click anywhere in the query window which is created, and select "Query options".

  a.) Select "Results -> text" and choose "comma delimited" for the output format.  This will enable easy creation of an INSERT  
       query to get the data back in the database upon re-creation.
  b.) Change any other options in this menu as desired.

3. Right-click anywhere in the query window and select "Results to -> File" and save the file. This will produce a comma delimited

   file containing all of the rows in the table. This file can be viewed in any spreadsheet program, as well as imported into most 
   database management suites.  It can also be easily made into an INSERT query.