Table of Contents
- SQL Server – An Overview
- SQL Server Database Types
- What is a System Database
- Uses of System Database
- What is a User Database
- Uses of User Database
- System Databases in SQL Server
- master Database
- model Database
- msdb Database
- tempDB Database
- ResourceDB
- SQL Server Backup and Recovery
SQL Server – An Overview
Microsoft SQL Server is a relational database management system (RDBMS). In simpler words, it is a software application designed to manage databases, which stores data and allows other applications to retrieve data from it.
SQL Server is one of the widely used databases in various applications like online transaction processing, business intelligence operations, and analytics operations.
SQL Server was primarily built to manage and store data within a database. However, SQL Server is now more than just a database. Microsoft has bundled a variety of tools with SQL Server such as data management, business intelligence (BI), and analytics tools.
SQL Server Database Types
One or more than one instances of SQL Server can be installed on a computer. Each SQL instance can contain one or many databases.
In general, there are two types of SQL Server Databases:
- System Database
- User Database
What is a System Database
- System databases are the databases that are installed during the SQL Server installation and are used to manage SQL Server
Uses of System Database
- System databases are used to manage SQL Server and contain information about the SQL Server system like logins, databases, linked servers, jobs, schedules, reports, etc
What is a User Database
- User-defined databases are created and managed by the users. These are the databases that users use while creating applications
Uses of User Database
- User databases can store and access the data of an application for which they are being used
In this blog, you’ll learn about all the system databases and their purposes.
System Databases in SQL Server
SQL Server mainly contains the below-listed system databases:
- master
- model
- msdb
- tempDB
- resourceDB
Each of them is used by SQL Server for Separate purposes. Let’s look into them in detail.
master Database
master Database is the core system database that contains all the system-level information of SQL Server. Of all the databases, the master database is the most important as it is necessary for starting the SQL Server.
It stores a wide range of metadata information that includes:
- System configuration settings
- Linked Servers Information
- File locations of user databases
- Information about all other objects created in the SQL Server – Databases, Stored Procedures, Tables, Views, etc
- Logon accounts
- Endpoints
If the master database becomes unusable or corrupted, you need to bring the database back to its usable state as SQL Server cannot start if the master database is unavailable. Therefore, it is always recommended to maintain a current backup of the master database. You need to either rebuild the Master Database or recover from backup. Rebuilding the master database rebuilds all of the system databases.
model Database
The model database is used as a template for other databases created in SQL Server, especially when creating user-defined databases.
The model database information including tables, stored procedures, etc, that are created on it will be copied when a new database is created using the model database template.
If you modify the model database, all the changes made in the model database will be reflected on all the databases created using it.
The tempdb is created from the model database each time SQL Server starts. Therefore, the model database must always exist on the SQL Server.
msdb Database
The msdb database is used mainly by the SQL Server Agent, and also by other features like SQL Server Management Studio, Service Broker, and Database Mail.
msdb supports scheduling jobs & alerts and stores system activities like backup & restore history for the databases, replication information, log shipping, etc.
Backup for msdb database is necessary as msdb is required for the proper function of SQL Server Agent Service.
tempDB Database
We can easily identify the purpose of this database from the name of the database itself.
tempDB is a global resource that can be accessed by all the users in the SQL Server Instance.
tempdb stores temporary user objects that you create like temporary tables (Global and Local), temporary variables, temporary stored procedures that hold intermediate results during the sorting or query processing, and cursors.
The performance of SQL Server will be impacted if more temporary objects are created and the tempDB storage space is not sufficient. Hence it is recommended to use storage with enough space for tempdb.
Every time SQL Server starts, the tempdb database is recreated. This database is created using the model database. You cannot back up or restore the tempdb because it is temporary storage.
ResourceDB
Resource database is a read-only database.
All the system objects that are included with SQL Server are kept in this database. There will be no user data or user metadata in the resource database.
Server system objects will be physically stored in this database, but they logically appear in the sys schema of every database.
SQL Server Backup and Recovery
Backing up and managing SQL servers is one of the most important tasks in their job description for many database administrators.
Vembu BDRSuite for MS SQL Server offers a robust, and cost-effective backup solution to protect your SQL Servers Databases and all of its data.
BDRSuite offers two different options to backup the SQL Server
- Back up the entire machine in which the SQL Server is running and individually restore the application items (or) restore the entire machine within a few seconds based on your requirement
- Back up only the SQL Server Databases and restore from any point in time when needed
Read this blog to learn more about SQL Server Backup and Restore using BDRSuite
Download a full-featured 30-day free trial of BDRSuite and evaluate SQL Server Backup & Recovery in your environment.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
