free DBAKings dotnet online tutorials

MS SQL Server database file extensions

Create a database named Company with a table named Employee and insert some data into that table using below commands.

Example commands to create database named Company, table named Employee and insert data

use Company
create table Employee( Eid int, Ename varchar(20))
insert into Employee values(1,'sudhir')
insert into Employee values(2,'ram')
insert into Employee values(3,'rehman')
insert into Employee values(4,'rahim')
insert into Employee values(5,'krish')
select * from Employee

After executing the above sql statements the result set of Employee table is as follows

If you want to give this Company database to someone or if you want to copy this Company database file to some other location first you have to find the location where the database is located in your system.

The default location of database files is

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

Data is the folder in which databases are stored..
It is located in the drive in which Operating system is installed.
In MSSQL folder along with DATA folder Backup folder is also available.
In Backup folder backup files of databases are stored.

Database files and their extensions

In MS SQL Server three database files are present. They are
.mdf file
.ldf filee
.bak file

.mdf file

mdf stands for master database file or Microsoft database file.
It is a file used to store database objects.

.ldf file

It is used to store transaction details of a database file.

.bak file

Bak stands for backup.
It is used to store the backup information of a database.
It is used to restore database files from that backup file ie., you can restore .mdf and log files for .bak file.
If you want to copy a database file from one location to other location first you have to detach the database from MS SQL Server Management Studio.