Database Backup and its types

Hey guys, In this blog I am going to explain you about Database backup and its types.

Backup-

A backup of the SQL Server data that can be utilized to repair a failure and recover the data. A database, or a few of its files or file groups, might be the level at which a backup of SQL Server data is produced.

In SQL Server database, mostly we take three kind of database backups.

1. Full Database Backup

2. Differential Database Backup 

3. Transaction Log Database Backup


Full Database Backup

Full database backups represent the whole  database at the time the backup finished. Full DB backup file extension normally we keep as .BAK but it's not mandatory.


 --Script
-------------------------------------------------------------------- 

BACKUP DATABASE DB Name TO DISK = N'Z:\DB_Name.bak'

WITH DIFFERENTIAL ,NAME = N'BKP FileName' GO

--------------------------------------------------------------------

Differential Database Backup

 A data backup that is based on the latest full backup of a complete set of data files or  filegroups (called differential base) and that contains only the data extents that have  changed since the differential base.

Differential DB backup file extension normally we keep as .BAK but it's not mandatory.

 --Script 

--------------------------------------------------------------------  

BACKUP DATABASE [DB1] TO  DISK = N'Z:\Diff_Name.bak' WITH  DIFFERENTIAL , NAME = N'BKP FileName' GO

--------------------------------------------------------------------

Transaction Log Database Backup

 A backup of transaction log backup that includes all log records that were not backed up in a  previous log backup. 

Transaction log DB backup file extension normally we keep as .TRN but it's not mandatory.

Note: Log backup we can take only for the DB if its recovery model is FULL. 

--Script 

-------------------------------------------------------------------- 

BACKUP LOG [DB1] TO  DISK = N'Z:\Log_Name.trn'

WITH NAME = N'Log  Backup' GO

--------------------------------------------------------------------