Creating Database in SQL Server in any Location /Directory(.mdf and .ldf files)

To work with SQL Server Database we need to create a database first. We can do it in many ways. Let's discuss.

Using Graphical User Interface (GUI)

After login to SSMS(SQL Server Management Studio)

• Open Object Explorer
• Right Click on Database
• Click on New Database

• A new window will open;  Give the Database name
• Click OK

✅ Your Database is created successfully.

After Creating a database, SQL Server creates 2 database files internally in C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA location with .mdf and .ldf extensions.

Those files are PRIMARY DATA FILE/MASTER DATA FILE (.mdf) and LOG DATA FILE (.ldf). Primary Data File contains all database information and the Log Data File contains all the transactions/log activities.

Using Character User Interface (CUI)

We can write queries to create a database.

Open Query Editor

Syntax to create a Database

create database <Database Name>

Ex:-create database MyFirstDB 

then Execute

✅ Your Database is created successfully.

Note:- It will also store the database files on the above default path i.e. C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA. We can also change the location of these file paths and their size.

Giving customized Path, Size, Max size, and File growth of the database file

Using GUI

While creating a new database we have seen this table also.

we can clearly see the columns, Logical Name, File Type, Filegroup, etc. The Red dotted column values we can customize.

Click on the Autogrowth/Maxsize columns box we have another window to set those properties; the image reference is given below

Note:-Filegrowth data can be set in percentage value/ Megabyte value, and Max size data can be set as Megabyte value/Unlimited.

Let's understand these columns clearly. These fields are helpful while writing the query for customized database creation.

Logical Name/ Name:- It is the logical name of the file; we have to set both .mdf and .ldf file names. In GUI while giving the database name the application automatically sets file names as follows. .mdf/data file name-> databasename and .ldf/log file name->databasename_log.

Initial Size(MB)/ Size:- It is the size of the file initially while creating the DB.

Autogrowth/ Filegrowth:- Sometimes SQL server needs to expand its size. This property helps to grow the file size automatically when it needs some extra space in the disk. the value can be set in Megabyte/Percentage.

Maxsize:- This property sets the maximum size of the file.

Path/ Filename:- It is the physical path/location of data and log file.

Using CUI

Syntax:-

create database <database name>

on primary

(.mdf/data file Properties)

 log on

(.ldf/log file  Properties)

Ex:-

create database MyFirstDB

on primary

(name = 'MyFirstDB ', filename = 'D:\MyDatabase\MyFirstDB .mdf' , size = 2MB , maxsize = 6MB, filegrowth = 1MB)

log on

(name = 'MyFirstDB _log', filename = 'D:\MyDatabase\MyFirstDB _log.ldf' , size = 2MB , maxsize = UNLIMITED , filegrowth = 10%);

then Execute

✅ Your Database is created successfully.

We can only set Customized Path like the below query

create database MyFirstDB 

on primary

(name = 'MyFirstDB ', filename = 'D:\MyDatabase\MyFirstDB .mdf')

 log on

(name = 'MyFirstDB _log', filename = 'D:\MyDatabase\MyFirstDB _log.ldf');

then Execute

✅ Your Database is created successfully.

Thank You