Database: Database is a container which stores data and data objects. It manages data and allows fast storage and retrieval of that data.
There are 2 types of databases,
System databases will be created as a part of SQL Server installation. SQL server-internal operations will be performed using system databases.
User databases are created based on organization requirements we are creating externally and stores data.
System databases available in SQL server from 2000
From SQL server 2005 along with the master, model, msdb, and tempdb a new database introduced called resource DB.
Model database will act as a template in creating new user databases. How many files we want to create and how many MDF and LDF, what its initial size, the path of binary files all this information we call as a template.
Every new database acquires properties from the model database. New DB goes to model database and gets those properties. If we create any table all the new databases which we create from now on contain the same table.
Msdb stores all scheduling and automation information. In SQL server we are doing automation in the form of job, sql server agent service will be responsible for this.
All sql jobs information will be stored in sysjobs, sysschedules, and sys steps tables inside msdb.
SQL Server agent service will read msdb schedule tables, based on this it will start and stop the tasks automatically.
History-related information like backup, restoration log shipping history will be stored.
Temporary database where all temporary objects will be created for performing sql server internal operations.
Temporary objects that are created by sql server internal operations such as temporary tables, temporary stored procedures etc.
Rows versions that are generated by data modifications transaction in database that uses read-committed (or) snapshot isolation stores here.
All the transformation, internal calculations, conditions sorting operations will be performed in Tem db.
Whenever sql server started Tempdb will be deleted and new copy will be created.
Resource db is hidden database we can see only mdf and ldf physically. It stores where we keep sql binaries files.
It provides high security to metadata, it contains all system objects such as system objects physically stores.
Resource db makes upgradation to new sql server veron easier and faster. In earlier versions upgrading will delete and create system objects. Now resource database will contain all system objects we take copy of resource database mdf and ldf and completes upgrading easily.
Open sql server 2012 → connect to management studio →Select sql server instance → go to database right click → new database
Database Name : SQL test 2012
Owner : SA
Logical Name: Sql test 2012 – data, Sql test – Log
Initial Size : 1 GB
Autogrowth : 500 MB
Path : E:\Database\sql test.mdf .Ldf
Database name: it provide name to database.
Owner shows default, if we want we can change.
Logical Name is used to refer the physical file in all T-sql statements.
Initial Size will acquire from model db properties we can change as per our requirement. What is the size of database while creating.
Autogrowth means once it reaches the initial how much my database size can increase it will add size.
Path, actually storage location of database, where my database is going to store(location).
We require minimum 2 files to create a database 1 mdf and 1 Ldf.
There are 5 properties required to create a database
Name, what is the database name that we are going to provide for new user database. Logical Mdf and logical Ldf will use for Administrative purpose.
File name, complete path where database is going to store.
Initial Size, we have estimate how much the database size initially we need. We have to specify the max limit and autogrowth means once it reaches the initial size automatically how much size it has to increase we can provide it either in MB (OR) Percentage.
Create database sql db 2012
It will create new database with default model properties.
We can specify the properties through T-Sql
Create database sql db 2012
Name = sqldb2012_data
Filename = ‘E:\data\sqldb2012_data.mdf’
Size = 10 GB
Max Size = 500 GB
File Growth = 5GB
Name = ‘sqldb2012_log’
Filename = ‘E:\data\sqldb2012_data.ldf’
Size = 1 GB
Max Size = 500 GB
File Growth = 500 MB
Rename a file name:
Alter database sql db 2012 modify file name = sql db 2012 – filename =’D:\sqldb_data.mdf’
When database is online we cannot more mdf and ldf. It may lead to database crash.
After creating database, verify properties
Files tab → shows how many Mdf and Ldf are there in database. We have add option in GUI we can add more files.
File group → By default we have only 1 file group as primary. We can group multiple files under one file group
Collation setting → Latin 1 – General
Compatibility Level → On which version sql server developed show here
For sql server 2012 it is 110
#Ref. Ram Mohan & Rama Krishna notes