How to Create Database in SQL Server By using TSQL or GUI - SQL Server / TSQL Tutorial Part 24

Scenario:

You are working as database developer and you have to prepare scripts for Create Database with below requirements so it can be run in QA, UAT and Production SQL Server Instances.

Database Name : TechBrothersIT
Primary File Name : TechBrothersIT
Index File Group : TechBrothersIT_Indexe
Log File Name : TechBrothersIT 
and 
Primary file should be on C:\PrimaryDBFiles
Index File should be on C:\IndexDBFiles
and Log file should be on C:\LogDBFiles

The Initial Size for each of the file should be 1GB and Max Size set to Unlimited and file growth should be set to 2 GB.


Solution:

You can create the database by using Graphical User interface in SSMS or you can use TSQL. Let's do this by using GUI first and then we will do the same by using TSQL.

Step 1: 
Right Click on the Database Tab and then hit New Database so open Create database wizard.

How to create new database in SQL Server - SQL Server / TSQL Tutorial

Now make changes according to our requirement. 1 GB=1024 MB. Also we need to add a new File Group for Index File.
How to create Database in SQL Server - SQL Server / TSQL Tutorial


Now once you hit Ok, new database will be created. 

How to Create Database by using TSQL Script:

If you don't like to create database by using GUI, you can hit Script Button and then Generate Script in new Windows.

TSQL script will be generated in new window and you can use this to create database. Also you can make changes as per your environment if paths are different.

CREATE DATABASE [TechBrothersIT] CONTAINMENT = NONE ON PRIMARY (
    NAME = N'TechBrothersIT'
    ,FILENAME = N'C:\PrimaryDBFiles\TechBrothersIT.mdf'
    ,SIZE = 1048576 KB
    ,FILEGROWTH = 2062336 KB
    )
    ,FILEGROUP [IndexFileGroup] (
    NAME = N'TechBrothersIT_Index'
    ,FILENAME = N'C:\IndexDBFiles\TechBrothersIT_Index.ndf'
    ,SIZE = 1048576 KB
    ,FILEGROWTH = 1048576 KB
    ) LOG ON (
    NAME = N'TechBrothersIT_log'
    ,FILENAME = N'C:\LogDBFiles\TechBrothersIT_log.ldf'
    ,SIZE = 1048576 KB
    ,FILEGROWTH = 2062336 KB
    )


If you need to add more file groups or add file names to file groups, you can do that as we have done in above TSQL script.



No comments:

Post a Comment