How to Manage Database with Creating and Storing information in SQL

February 19, 2014 , , , 0 Comments

As a database developer, programmer might need to create databases to store information. At times, you might also delete a database, if it is not required. Therefore, it is essential to know how to create and delete a database.
The SQL Server contains some standard system databases. Before creating a database, it is important to identify the system databases supported by SQL Server and their importance.

System databases are the standard databases that exist in every instance of SQL Server. These databases contain a specific set of tables that are used to store server-specific configurations, templates for other databases. In addition, these databases contain a temporary storage area required to query the database.

SQL Server contains the following system databases:

master Database

The master database records all the server-specific configuration information, including authoried users, databases, system configuration settings, and remote servers. In addition, it records the instance-wide metadata, such as logon accounts, endpoints, and system configuration settings.

The master database contains critical data that controls the SQL Server operations. It is advisable not to give any permission to users on the master database. It is also important to update the backups of the master database to reflect the changes that take place in the database as the master database records the existence of all other databases and the location of those database files.

The master database also stores the initialization information of the SQL Server. Therefore if the master database is unavailable, the SQL Server database engine will not be started.

tempdb database

The tempdb database is a temporary database that holds all temporary tables and sor procedures. It is automatically used by the server to resolve large or nested queries or to sort data before displaying the results to the user.
All the temporary tables and results generated by the GROOUP BY, ORDER BY, and DISTINCT clauses are stored in the tempdb database. You should not save any database object in the tempdb database because this database is recreated every times the SQL Server starts. This results in loosing data you saved earlier.

model database

The model database acts as a template or a prototype for the new databases. Whenever a database is created, the contents of the model database are copied to the new database.
In this database, you can set the default values for the various arguments to be specified in the Data Definition Language (DDL) statements to create database objects. In addition, if you want every new database to contain a particular database object, you can add the object to the model database. After this, whenever you create a new database the object will also be added to the database.

msdb Database

The msdb database supports the SQL Server Agent. The SQL Server Agent is a tool that schedules periodic activities of the SQL Server, such as backup and database mailing. The msdb database contains task scheduling, exception handling, alert management, and system operator information needed for the SQL Executive Service. The msdb database contains a few system-defined tables that are specific to the database.
As a database developer, you can query this database to retrieve information on alerts, exceptions, and schedules. For example, you can query this database to know the schedule for the next backup and to know the history of previously scheduled backups. You can also query this database to know how many database e-mail messages have been sent to the administrator. However, there are tools available to perform these database administration tasks.

resource Database

The Resource database is a read-only database that contains all the system objects, such as system-defined procedures and view that are included with SQL Server. The Resource database does not contain user data or user metadata.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google