Microsoft SQL 2005: Database Administrator Notes


  -----------------------------------------------------------------------------
| BUG: It seems if you delete an instance and then try to install you get an error 1603...
| SOL'N:
|
| 1. Run the Client tools setup from SQLrun_tools.msi and when the
| feature selection screen comes, select one Client component at
| a time and EXCLUDE Management Tools to be installed at the end.
|
| 2. Follow any sequence ( like first select Only BIDS under Client
| Tools Selection Screen.. Install it .... after that run the Setup
| again->SQLRun_tools.msi and this time select Legacy components..
| and so on... )
|
| 3. After all the other Client Components were installed, INSTALL THE
| MANAGEMENT TOOLS by running the SQLRun_tools.msi setup.
|
| Client tools setup should succeed now.
|
| Note:
|
| SQLrun_tools.msi can be found in C;\software\MySQL\Disk2\Setup
  -----------------------------------------------------------------------------

Ms SQL Basics

* When first (instance) installed, the server is given the name of the computer's name

* Each instance is considered an installation.

* The default files created, are considered the primary file and used by the system/software

* Each instance can have multiple databases and files

* Can have multiple instances on the same machine/sharing the same CPU

* The files and data can be stored in places anywhere other than where the software was installed

* Uses filegroups to "group" files together

DEFAULT/PRIMARY FILES


ADDING A NEW USER

  1. Connect via Management Studio
  2. Expand Security
  3. Right click Logins and select New Login
From there you can configure that new user. Remember to set the authentication mode for the server. It's set to windows authentication by default.

CONFIGURE USERS

  1. Connect via Management Studio
  2. Expand Security
  3. Expand Logins
  4. Right click on user and select properties
  5. Highliaght item in left pane

CHANGING OWNERSHIP Of database

  1. Connect via Management Studio
  2. Expand database
  3. Right click on database and select properties
  4. Select files

COMMAND LINE:

  1. Connecting to another instance (DB engine):
    At DOS prompt (C:\>) type: sqlcmd -S
    E.G. sqlcmd -S chris\tobago
  2. Removing a database:
    drop database
  3. Attaching a dadtbase:
    USE master;
    GO
    CREATE DATABASE Archive
    ON (FILENAME = 'D:\SalesData\archdat1.mdf')
    FOR ATTACH ;
    GO