Basics of Database Administration in SQL Server

Databases in SQL Server

There are basically the following two types of databases in SQL Server:

System Databases.

User Databases.

System Databases in SQL Server

System databases are databases that are created when sql server dba is installed. These databases are used for various operational and management activities for SQL Server.

Types of System Databases

There are basically four system databases in SQL Server,

master

msdb

tempdb

a model that we can see. Apart from that, there is one more system database

System Database

SystemDatabase

Master Database in SQL Server

• All the system-level information for a SQL Server record by the master database.

• The dbid (database id ) of a master is 1.

• The master database has a SIMPLE RECOVERY MODEL.

• It is a very important database and we must have the backup.

• Without the master database, the server can't be started.

Assume the master database files are missing or inaccessible, will SQL Server start or up?

Answer:

No, SQL Server will not start because the master database is the important database and all the configuration and information needed to start the SQL Server is stored in the master database itself hence without the master database SQL server will not start.

• Master database contains information about the server configuration. We can see the server configuration with the following query:

• select * from sys.sysconfigures;

• Master database contains information about all other databases and their location on SQL Server. We can see the information by executing the following query:

• select * from sys.sysdatabases; or sp_helpdb

• Master database contains information about logins in SQL Server. The following is the query by which we can see it:

• select * from sys.syslogins;

• Master database also contains information about users on SQL Server. The following is the query to see user details:

• select * from sys.sysusers;

• Master and mastlog are the logical file names of the master database.

• master.mdf ( data file ) and mastlog.ldf are the physical files of the master database.

1. SELECT name, physical_name FROM sys.database_files;

Query to see the physical file location of the master database:

Master Database

Model Database in SQL Server

• The Model database acts as a template database used in the creation of new databases.
• The dbid of the model database is 3.
• By default, the model database has FULL RECOVERY MODEL.
  
  Figure: Recovery Model
• We can take a backup of the model database.
• Modeldev and modeling are the logical file names of the model database.
• Model.mdf ( data file ) and modeling.ldf are the physical files of the model database.
• The same query can be used to seeing the physical file location of the model database:

1. SELECT name, physical_name FROM sys.database_files;

DataTable

A user has created a new database. What will be the recovery model of that database?

Answer:

Because the model database acts as a template database, when a user creates a new database it will inherit the property of the model database and as we know by default the recovery model of the model database is FULL (until or unless the user changes it), hence the new database is created by the user with FULL RECOVERY MODEL,To get more information about SQL Server DBA please visit:SQl server DBA Ttraining

MSDB Database in SQL Server

• A MSDB database stores information related to backups, SQL Server Agent information, SQL Server Jobs, alerts and so on.
• The Dbid of the msdb database is 4.
• The recovery model of a msdb database is SIMPLE.
• We can take backup of a msdb database.
• MSDBData and MSDBLog are the logical file names of a msdb database.
• MSDBData.mdf (data file) and MSDBLog.ldf are the physical files of a msdb database.
• The same query can be used to see the physical file location of a msdb database:

1. SELECT name, physical_name FROM sys.database_files;

MSDB database Table

TempDB in SQL Server

• It stores temporary objects, like temporary tables, temporary Stored Procedures and temporary tables to store sorting and so on.
• The dbid of a temp database is 2.
• The recovery model of a temp database is SIMPLE.
• We can't take a backup of a tempdb.
• tempdev and templog are the logical file names of tempdb.
• tempdb.mdf (data file) and templog.ldf are the physical files of a tempdb.
• Same query can be use to see the physical file location of tempdb:

1. SELECT name, physical_name FROM sys.database_files;

TempDB Table

Why we can't take a backup of a temp database?

Answer:

Temp databases, as the name says, are used to do temporary operations, such as tables, Stored Procedures and cursors. Once the operation is over it will be cleared and is minimally logged. A TempDB is recreated everytime SQL is started, so it is always has a clean copy of the database hence backup and restore operations are not allowed for a TempDB.

How you will check to determine if the SQL Server is restarted?

Answer:

Check the creation date of the tempdb, if it is new it means SQL Server is started.

Database Properties

We can execute the following query also to check the tempdb creation date:

1. select name, crdate from sys.sysdatabases;

Output

Resource Database in SQL Server

• It is a read-only database hidden from the user. It contains all the system objects that are included with the SQL Server.
• The Dbid of the resource database is 32767.
• The Resource database helps when we do a SQL Server upgrade.
• We can't see the resource database in SQL Server Management Studio but we can see its database file at the OS level by the name mssqlsystemresource.mdf and mssqlsystemresource.ldf in the Binn folder of Program Files.

Microsoft SQL System

We can see the location of the resource database file using the following query also:

1. Use master GO
2. SELECT
3. 'ResourceDB' AS 'Database Name',
4. NAME AS [Database File],
5. FILENAME AS [Database File Location]
6. FROM
7. sys.sysaltfiles
8. WHERE
9. DBID = 32767 GO,If you are interested to learn SQL DBA visit SQl Server DBA online training Hyderabad