Skip to content
Related Articles

Related Articles

Create Database in MS SQL Server

View Discussion
Improve Article
Save Article
  • Last Updated : 28 Aug, 2020
View Discussion
Improve Article
Save Article

Prerequisite – Introduction of MS SQL Server

Databases are a collection of objects like tables, views, stored procedures, functions, etc. In MS SQL Server, two sorts of databases are available.

  • System databases
  • User Databases

System Databases :
System databases are created automatically once we install the MS SQL Server. Below is a list of system databases –

  • Master
  • Model
  • MSDB
  • Tempdb

User Databases :
User databases are created by users (DBAs, and testers who have access to create database). To create a database, the below methods could be used –

  • SQL Server Management Studio.
  • Transact-SQL.
  1. Using SQL Server Management Studio :
    • Connect to an SQL instance of the SQL Server Database Engine then expand that instance.
    • Right-click Databases, and then click New Database.
    • Enter a database name.
    • To create the database by with default values, click OK.

    Create New Database

    • Otherwise, continue with the following optional steps.
    • To change the owner name, click (…) to pick another owner.
    • To change the default values of the first data and transaction log files, within the Database files grid, click the editable cell and enter the new value.
    • To change the collation of the database, select the Options page, then select a collation from the list.

    Database Options

    • To change the recovery model, select the Options page, and choose a recovery model from the list.
    • To add more filegroup, click the Filegroups option. Click Add, then enter the values for the filegroup.

    Database Filegroup

    • To create the database, click OK.

  2. Using Transact-SQL :
    • Connect to the Database Engine.
    • Open New Query.

    Syntax –

    CREATE DATABASE databasename
    [ ON
         [ PRIMARY ] <filespec> [...n ]
         [, <filegroup> [...n ] ]
         [ LOG ON <filespec> [...n ] ]
    [ COLLATE collation_name ]
    [ WITH <option> [...n ] ]

    Example –

    Create database with default settings –


    Create database with options –

    ON (NAME = test_dat, --logical datafile name
        FILENAME = 'D:\DATA\testdat.mdf',  --physical datafile name
        SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5)  
    LOG ON  (NAME = test_log, --logical logfile name
             FILENAME = 'L:\DATA\testlog.ldf',  --physical logfile name
             SIZE = 5MB,  MAXSIZE = 25MB,  FILEGROWTH = 5MB ) ;  
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!