SQL DBA Tutorial

Types of SQL Server Backups

Types of SQL Server Backups

Types of SQL Server Backups

SQL Server offers many options for creating backups. There are different types of backup available in SQL Server.Flowing are the most common type of backups are used day to day work in SQL Server.

  1. Full Backup.
  2. Differential Backup.
  3. Transaction Log Backup.
  4. Tail Log Backup.

There are other Backup types available as well.

  1. File group Backup.
  2.  Mirrored Backup.
  3. Copy-only Backup.
  4. Striped Backup.

Full Backup: 

Syntax:  Backup database <dbname>to disk=’…….bak’.

Backup database [master] to disk=’D:\backup\master.bak’ with compression, stats=1

Differential Backup: 

Differential Backup capture all the data that has changed since the last full backup.It reduces time of recovery in case of database failure i.e no need to restore all log backups made after full backup.

Syntax: Backup database <dbname>to disk=’……….’with differential.

   Backup database [master] to disk=’D:\backup\master.bak’ with compression, differential, stats=1

Transaction Log Backup: 

A Transaction log backup contains all the transaction log records generated since the log backup. Transaction log backup are only possible in the Full and Bulk-logged recovery models.

Syntax:   Backup log <dbname> to disk=’…….trn’

Backup log [master] to disk=’D:\backup\master.trn’ with compression, stats=1


Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

Thank you for subscribing.
Something went wrong.
I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Exit mobile version