Monday, December 1, 2008

SQL Server consists of six databases by default.

Master


It contains system catalogs that keep information about disk space, file allocations,
usage, system wide configuration settings, login accounts, the existence of other
database, and the existence of other SQL Servers (for distributed operations).

Model

It is a simply a template database. Every time you create a new database, SQL Server
makes a copy of model to form the basis of the new database.


Tempdb
Temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique
among all other databases because it is recreated not recovered every time SQL Server
is started

Pubs
This is a sample database used extensively by much of SQL Server documentation.
It’s available to everyone in the SQL Server community


Northwind
This is a sample database that was originally developed for the use of Microsoft
Access

Msdb
This database is used by the SQL Server Agent Service, which performs scheduled
activities such as backups and replication tasks.


Database Files:
A database file is nothing more than an operating system
file. SQL Server 2000 allows the following three types of database files:

·Primary data files (.mdf)
·Secondary data files (.ndf)
·Log files (.ldf)

When we create a new user database, SQL Server copies the model database (includes
19 system tables and 2 system views (for backward compatibility)). A new user database
must be 1MB or greater in size.

We can create a new database using the following command: CREATE DATABASE newdb

Types of Backups:
  • Full Backup

  • Differential Backup

  • Log Backup


    Tables: A database can contain multiple tables with the same name as long
    as the tables have different owners.

    The full name of a table has three parts, in the following form:

    Database.Owner.Tablename

  • No comments: