Default databases in SQL Server

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:

Mr. Mansoor Ali Mohammed - Software Engineer
I love to write articles. Programming languages fascinate me and I feel at least reasonably familiar with Sql Server database and .Net Technologies. The idea of articles come from my own experiences while working in those areas and i like to share my knowledge on database with all others so that it might be helpful.
Read moreRead more about Contributor