How to Specify the Location of Data Files and Log Files when Creating a Database in SQL Server

Normally we can the simplest way to create a database in SQL Server is to as below:

If you ever need to know where your database files are located, run the following T-SQL code:

use master
CREATE DATABASE my_database 

If we create without specifying anything else, data files and log files are created in the default location (see how to find the default location).

However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.

CREATE DATABASE KargoFirmasiSistemi
ON PRIMARY (
NAME = 'KargoFirmasiSistemidb',
FILENAME = 'c:\database2\KargoFirmasiSistemi_db.mdf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 2MB
)
LOG ON (
NAME = 'KargoFirmasiSistemidb_log',
FILENAME = 'c:\database2\KargoFirmasi_log.ldf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
)
GO

The .mdf file is the data file and the .ldf file is the transaction log file.

We specify the size of each file, as well as its maximum size and its filegrowth.

FILEGROWTH specifies the automatic growth increment of the file (the amount of space added to the file every time new space is required).

• FILESIZE can be specified in KB, MB, GB, or TB.
• MAXSIZE can be specified in KB, MB, GB, TB, or UNLIMITED.
• FILEGROWTH can be specified in KB, MB, GB, TB, or %.

The default is KB (for kilobytes).