Backup And Its Type In SQL Server

In our previous article we learned many concepts and component of SQL Server. In our last article, we’ve learned how to setup DB mail on machine.

In this article we’ll learn about backups and its types available in SQL Server. We’ll also perform different types of backups on one of our database as demo to clear the picture.

We’ll learn all these things as listed below:

1. Backup Requirement.
2. Ways to perform Backup.
3. Types of Backup with examples.

Before proceeding, let’s understand what exactly backup means.

Backup

In simple term, “Backup is nothing but help or support.”

In computer terms, “Backup is a copy of a file or other item of data created as an alternate that can be preserve in case of any failure or any disaster.”

Backup Requirement

Let’s begin with backup’s requirement. Below are some of the few factors or questions during backup requirement.

• What type of backup you want?
• How many databases to be added in the backup process?
• How much data loss is acceptable?
• What is the suitable time to perform the backup?
• How backup need to schedule?
• At what time databases are heavily used?

So these are some set of questions we need to ask before scheduling any backup task. Once gathering requirement of above questions, we need to find the way to create back up task. Below are the ways to create backup task.

Ways to perform backup

1. Taking backup via Maintenance Plan.
2. Taking backup via T-SQL Script.
3. Taking backup via Third Party tool.
4. Taking backup via power shell script.

Now, let’s look at types of backups we have in SQL Server.

Types of Backup

In SQL Server we’ve different types of backup, some of them are listed below.

• Full backup
• Differential Backup
• Transaction Log backup.
• Filegroup Backup
• Partial Backup

In this we’ll discuss the top 3 backup types i.e. FULL, DIFFERENTIAL and T-LOG.

Let’s begin with full backup first.

Full Backup

• Full backup is an essential and most commonly used backup in SQL Server. In full backup, all pages within a database are captured that contains your actual data.
  
  
• Full backup is the base for other backups, because without performing full backup, differential and log backup cannot be possible.
  
  
• Full database backup can be performed on any databases having recovery models; SIMPLE, FULL, BULK LOGGED.
  
  
• If your database is in simple recovery model, transaction log gets automatically truncate when checkpoint runs or execute.
  
  
• If your database is in FULL or BULK LOGGED model, then we need to perform Log backup to control the size of the log file. This situation happens when you want to shrink your log file of database which is in FULL recovery model, you need to perform Log backup to free space from the log file.
  
  
• When SQL Server initiate backup, backup engine grabs pages from the data file as quickly as possible. While performing this operation it doesn’t check the order of pages.
  
  
• During backup operation, you cannot perform shrinking activity or any kind of data file addition.
  
  
• When SQL Server performs backup operation, it follows below steps:
  
  
  1. Locks the database.
  2. Block all the transaction.
  3. Place a mark in the transaction log.
  4. Release the database lock.
  5. Extracts all pages in the data files and writes them to the backup device.
     
     
• You can perform backup in 2 ways, i.e. via GUI or T-SQL script.

To perform backup from GUI, right click on your database, then Tasks, Back Up as shown below:



You’ll get the following backup window, where you need to enter below details:



Backup Type: FULL
Name: <Name of your backup>

Click on Add to select your backup location.

Select your Compression as shown below. You’ll find this option under Option section.



After doing all these activities, click OK and will see backup progress.

The following is the T-SQL script for full backup.

• Format: This option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase information in a backup set that already exist during backup.
  
  
• INIT/NOINIT: If you specify INIT, and file already exists, SQL Server overwrites the file with the contents of the new backup. If you specify NOINIT, and file is already exists, SQL Server append the new backup to the end of the file.
  
  
• Name: Used to identify the backup set.
  
  
• Skip: Tells SQL Server to skip the expiration check that it normally does on backup set.
  
  
• NoRewind: Tells SQL Server to keep device open and ready for use when backup gets completed.
  
  
• NoUnload: Tells SQL Server not to unload the device when backup operation is completed.
  
  
• Compression: Tells SQL Server to compress the backup file when written to disk.
  
  
• STATS: This will show you the backup progress.

So, when we execute above backup script, backup will start and you’ll get something like below in output window.



This window tell you that backup completed successfully and you’ll get your backup file saved at specified location shown below.



You can see above backup file is compressed to 299 KB as we used COMPRESSION parameter during backup operation.

If we perform above operation without COMPRESSION, you’ll see a big difference in size as shown below.



As we can see, file size file with Compression is just 299 KB and file without compression took 2133 KB space. Using compression option during backup operation can save your disk space by reducing backup size.

Now let’s move to differential backup.

Differential Backup: