In this article, I am going to discuss about the basics of SQL server which is helpful for newbies.
Most of the people have to know SQL server query has designed for set based (declarative) .
The SQL commands are
DML (Insert, Update, Delete)
DDL (Create, Alter, Drop, Truncate)
DCL (Grant, Revoke, Deny)
TCL (Commit, Rollback)
A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one ndf). The log file is called ldf (More than one ldf)
1. System databases
A system database will create by default when the SQL server installed.
2. User databases
A user database will create by the user.
SQL Server System Databases
2. Resource (2005 onwards)
- The Master database manages the SQL Server instance. It stores system configuration, logins details etc..
- In SQL Server 2005 onwards Master database stores only the logical information in the system objects residing in the sys schema.
- In the previous editions of SQL Server, the Master database physically stores all the system objects, (tables, Sprocs etc.) information.
- One of the main things is master and resource DB must be located in the same directory. Otherwise the server goes down 🙂
- Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).
- Prior to SQL Server 2005 the system related data were stored in the master database.
- It’s a hidden and read-only database that is not accessible via SQL server management studio.
- TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc…
- When the SQL Server instance is rebooted, the TempDB database is recreated every time.
- Model is a template database for all user defined databases.
- So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs etc.…) i.e. Whenever you create a new database the template will taken from the model database.
- MSDB is the main database to manage the SQL Server Agent configurations.
- It provides some of the configurations for the SQL Server Agent service (such as jobs alerts etc..) and it stores database backup details.
Important: Keep taking the system database backup regularly except tepmDB.