MS SQL Databases

From CSWiki
Jump to: navigation, search

MS SQL Server 2008 R2 is installed on various servers for use with CS classes. Students can use MSSQL to create databases for various classes, projects, or just for fun.

[edit] Logging On

You must have an MS SQL account on the necessary server in order to log on. Contact Jason for the necessary info. 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 2008 R2" folder within the start menu, under "All Programs." When logging in, you will have to use the login information provided to you in the manner below:

  • Server Type: Database Engine
  • Server Name: servername
  • Authentication: SQL Server Authentication
    • Login: MSSQL Login
    • Password: MSSQL Password

[edit] 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!

[edit] Exporting Databases

Follow these steps if you wish to copy your database to another machine, or just make a backup.

In the MS SQL Management Studio, right-click on the database, and select "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".
    1. 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.
    2. 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.

Personal tools