SQL Server – How to backup a database?

Earlier post we learnt what the different types of database backups in SQL Server. Today we are look into how to take a backup in SQL Server?

There are mainly  two ways to backup the database, i.e.

  • Using management studio
  • Using  T-SQL
  • Or using any third party tools.

Using management studio:


Right click on the database -> choose tasks option -> choose Backup option.

Look the figure given below.


In backup option window, there are some parameters which we need to choose,

Database Name: name of the database to be backed up.

Recovery model of the database: Full/Simple/Bulk

Backup Type: Full/Differential/Transaction log

Backup Component: Which database component we need to backup? I.e. database/file/file groups

Backup set: A backup set will contain backup from single successful operation.

Backup Destination: where we need to take backup? To Disk or Tape


Backup using T-SQL:

--Full Backup
Backup database Mydatabase to disk = 'C:\testbakup.bak'
--Differential Backup
Backup database Mydatabase to disk = 'C:\testbakup_differntial.bak' with differential 
--Transactional Log Backup
Backup log Mydatabase to disk = 'c:\test_log.trn'

Backup using Third party tool:

We can use some third party tools for backing up of  sql server database