Creation of SQL Server Database ( For Beginners )

                                                Creation of SQL Server Database

We can create Database in 2 ways  :

1.       Using GUI

2.       Using T-SQL Script

Using GUI:

Step 1: From the Windows Start Menu, select “Microsoft SQL Server”, and then “SQL Server Management Studio”. Once the Management Studio starts, connect to the SQL instance then right click the Databases folder and select new Database. Enter a name in the “Database name” text box. 

Step 2:  Click on new database and provide the database name

        For this example, well use the name “MyNewDB”.

a. Logical Name:

MyNewDB         - Data file is where the actual data is saved on the hard drive.

                  MyNewDB_log - Log file   keeps track of any changes to that data.

b.  File Type:

Data files will be defined as ROWS and Log file will be defined as LOG

             C: Filegroup:

                     Primary: The Primary data file contains the start-up information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file and. The recommended file name extension for primary data file is .mdf

      Transaction Log: The Transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf  , we can add multiple Log files to database.

            Secondary: Secondary data files are optional, user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow.

        D. Initial Size(MB): 

      It always recommended to give some initial size for a user defined database based on Database average DB growth. SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not auto grow until all the files are full. Growth then occurs using round-robin algorithm. The default value can be set 100MB for .MDF and 1024MB for .LDF.  Later will analyse the DB average growth by monthly and can set Initial size for MDF file.

. Autogrowth:  

     It always recommended to set auto growth for both .mdf and, ldf files Initially we can set auto growth 512MB later can change the value based on the growth of DB. 

For .mdf-  should be given in MB’s (Recommended)

For  .ldf should be given in MB's or Percent (ex10%) depends on the expected size of your log file.

    F. Path:

       You have select the path based on your requirement to be placed .MDF and .LDF files in drive.

  G.  File Name:

      We do not need to provide any file name since files will get generated automatically based on Logical name once click OK. If you want to give any separate name, then you can provide.

Using T-SQL Script:

Use the bellow T-SQL script and execute in master

USE master

GO

CREATE DATABASE Sales

ON

( NAME = MyNewDB,

   FILENAME = 'c:\program files\microsoft sql server\mssql\data\MyNewDB.mdf',

   SIZE = 102400kb,

   MAXSIZE = 10MB,

   FILEGROWTH = 10MB )

LOG ON

( NAME = 'Sales_log',

   FILENAME = 'c:\program files\microsoft sql server\mssql\data\ MyNewDB_log.ldf',

   SIZE = 102400KB,

   MAXSIZE = 10MB,

   FILEGROWTH = 10% )

GO