Databases in sql server

Working With SQL Database

Databases in SQL Server Three types of databases are available. As a DBA working with only 2 types of databases, 

databases, 

These are the type of database we are using in the SQL server

1- System Define Databases.-   2-  User Define Databases.  3- Sample Databases

1. System Define Data Bases: - System databases store the complete Metadata info of SQL server we cannot deploy and Disaster Recovery Features like (Log Shipping, Database Mirroring, Always on Availability, Replication, etc...) But  Disaster recovery Features details are store in system define databases & directly we cannot do offline or de-attach the system define databases without system databases we cannot imagine running SQL. Here are the Some System Databases are...

   To Check SQL Server Databases with the state using this t-SQL command.

        select * from sys. databases

• Master Databases
• Model Databases
• Msdb  Databases
• Tempdb databases
• Resource Database
• Report Database
• Report Tempdb database
• Distribution Database

Master Databases in SQL Server:-

Master Database is the main database of SQL server it stores the SQL server user informactin , link server information server-level & database level settings details are store in the master database is the entry-level of SQL server when we restart SQL server start with the help of master database & tempdb database. when we install SQL server 2 copy of system databases are created  in SQL server  one copy of system databases are store in the data folder  and another copy of  system database are store in Bin Template folder .whenever you changes in any server or database level setting in SQL server Microsoft suggest to take backup Immediately of system databases 

Sof UserFul Point are Master Databases:-

• It store the Servrer Level Sttings Informaction.
• System Define error messages.
• Link server Details are store in the master Database.
• Backup Device Details are store in the master Database.
• Logins details.
• Endpoints.
• The default recovery Model is Simple.
• We can not de-attach Master Database
• Daily Full Backup is required We we do any changes to server level or database level Immediately Take backup for Sqfty pupruse 

SQL Server startup Parameters are  

Registry startup parameters: 

-d C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf

-e C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG

-l C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Model Database:-

The Model database is the template database that SQL Server uses to create new databases. When we create the new user database in SQL server same setting like database recovery model , setting data file store path, data file and log file by default size database growth  size these types are setting are applied to the newly created database 

MSDB DATABASES

MSDB Database stores the automation level details like  SQL server agents detail , Job details, Job history detail backup and restores history details, database mail details, service broker details are store in the msdb database.

TEMPDB DATABASE

TempDB is a system database in Microsoft SQL Server used as a store of internal objects, row versions, work tables, temporary tables, and indexes. TempDB is available for use to all participants connected to a SQL Server instance (it is a global resource). ... Temporary tables are created with the # naming convention. Every time when we restart the SQL server it's created new with the help of the maser database we can not take backup temp DB.

Resource Database

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys. objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Report Database & Report Tempdb

When we install SSRS Services in SQL by default install report DB & report tempdb database in SQL server 

 temporary database to store session and execution data, cached reports, and work tables that are generated by the report server.

User Databases:-  User database define or create by end-user & we can set applied setting accordingly like (attach,de-attach, online, offline,DB shrink, etc....).

Sample Database:-  Sample Database use for testing or learning purpose we can use it  Sample database are Adventureworks 2012,2014,2016,2017

We hope this article helps you know more about databases in SQL server. you may also want to see .--