Post
Ask Question

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:

go to BACKUP

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

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.

Select your Compression

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

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

  1. =======  
  2. BACKUP DATABASE [CSharpCornerDB] TO DISK = N'A:\SQL Server Backups\CSharpCornerDB\CSharpCornerDB_backup_2015_11_26_233017_3650802.bak'   
  3. WITH NOFORMAT, NOINIT, NAME = N'CSharpCornerDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10  
  4. GO  
  5. =======  
In above script, you’ll find various parameters but the only 2 parameters required for a backup are the Name of the database and the backup device.

Other parameters can also be used like INIT/NOINIT, FORMAT/NOFORMAT, COMPRESSION, STATS, etc.

Lets’ go through some of the parameters.
  • 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.

output window

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

backup completed successfully

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.

COMPRESSION

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:

  • A differential captures all the pages that has changed since last FULL backup.

  • Without performing full backup, we cannot perform differential backup.

  • SQL Server tracks each changes made after full backup using special page in header of a database called Differential Change Map (DCM). As changes are made to an extent within a database, SQL Server sets the bit to 1. When differential backup is executed, SQL Server reads the content of the DCM to find the extents that have changed since last full backup.

  • If we lose full backup, any differential backup that rely on that base backup will be unusable.

  • The primary purpose of creating differential backup is to reduce the number of log backups.

  • Differential database backup can be performed on any database having recovery models; SIMPLE, FULL, BULK LOGGED.

  • You cannot take differential backup of master database.

To perform differential backup, follow below steps.

Right click on database -> Tasks -> Back Up and you’ll get the following backup window.

backup Type

Everything will be same. Just change Backup type, name of your backup and select your backup location. You can use compression in Option section and click OK to start the differential backup.

Below is the T-SQL script for Differential backup.

  1. =======  
  2. BACKUP DATABASE [CSharpCornerDB] TO DISK = N'A:\SQL Server Backups\CSharpCornerDB\CSharpCornerDB__DIFF_backup_2015_11_26_233017_3650802.dif' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'CSharpCornerDB-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM  
  3. GO  
  4. =======  
If you see above query, it’s same as full backup only difference is the ‘DIFFERENTIAL’ option added in it which tells SQL Server it’s a differential backup.

We’re also using CHECKSUM option in this. This option is nothing but to verify the page checksum if it exists before writing the page to the backup.

Checksum is calculated for the entire backup that can be used to check whether backup is corrupted or not.

It’s always recommend to specify CHECKSUM option during backup operation to catch bad pages.

When you execute above query, SQL Server will begin differential backup and you’ll get the following output.

output

SQL Server will store the differential backup file with .dif extension as shown below.

differential backup file

Now let’s move to Transaction Log backup.

Transaction Log Backup
  • With help of transaction log backup, point-in-time recovery is possible.

  • With a T-Log backup, we capture the details of all the transactions that have been recorder for that database.

  • You can’t perform log backup on database having SIMPLE recovery model. T-Log backup only work with FULL and Bulk Logged recovery model.

  • A transaction log backup allows you to backup the active part of the transaction log.

  • Any changes we made in our database, SQL Server logs an entry of that transaction to the transaction log. Each entry has a unique number called LSN (Log Sequence Number).

  • This LSN is nothing but an integer value which starts from 1 and keeps increasing.

  • Based on this sequence number, we can restore transaction log one after another to perform point-in-time recovery of any database. With help of this LSN we can recover our database at any point. We just need to make sure that the LSN doesn’t break.

  • If LSN breaks, we need to take full backup before start backing up the transaction log.

  • During restore, before executing any transaction log, we must execute full backup, after executing full backup we can apply all log backup one-by-one.

To perform log backup via GUI, first make sure your database should not be in simple recovery model. As we discussed earlier, in SIMPLE recovery model, you won’t find T-Log backup. Let’s see it by changing our database recovery model from FULL to SIMPLE.

  1. USE [master]  
  2. GO  
  3. ALTER DATABASE [CSharpCornerDB] SET RECOVERY SIMPLE WITH NO_WAIT  
  4. GO  
Now if you open backup window, you’ll not see “Transaction Log” in Backup type option as shown below:

Transaction Log

To proceed further, change it back to FULL.
  1. USE [master]  
  2. GO  
  3. ALTER DATABASE [CSharpCornerDB] SET RECOVERY FULL WITH NO_WAIT  
  4. GO  
Now we can easily perform log backup as shown below.

easily perform log backup

Click OK to begin the log backup. But as you click OK, you’ll get the folllowing error window.

click OK

If you read the error, it says, “Backup Log cannot be performed because there is no current database backup”. This is because we broke the log backup chain by changing recovery model from FULL to SIMPLE.

As per MSDN,

“If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.”

This means we need to perform full backup before performing Log backup in this scenario.
  1. =======  
  2. BACKUP DATABASE [CSharpCornerDB] TO DISK = N'A:\SQL Server Backups\CSharpCornerDB\CSharpCornerDB__FULL_backup_2015_11_26_233017_3650802.difBak' WITH NOFORMAT, NOINIT, NAME = N'CSharpCornerDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
  3. GO  
  4. =======  
So, we are done with Full backup. Now we can perform LOG backup without any issue as shown below.

LOG backup

Below is the T-SQL for the same.
  1. =======  
  2. BACKUP LOG [CSharpCornerDB] TO DISK = N'A:\SQL Server Backups\CSharpCornerDB\CSharpCornerDB_Log_Backup.trn' WITH NOFORMAT, NOINIT, NAME = N'CSharpCornerDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
  3. GO  
  4. =======  
If you see the physical location of the transaction log backup file, you’ll find a file with extension ” SQL Server Transaction Log Backup (.trn)” as shown below.

SQL Server Transaction Log Backup

So this was the article based on backup and its types available in SQL Server.

Conclusion

In this article we learned about backups available in SQL Server and their types. We also perform different types of backups via GUI and T-SQL. In our upcoming articles we’ll see the ways to restore our database in SQL Server.

Please provide your valuable feedback and comments that enable me to provide a better article the next time. Until then keep learning and sharing.


Similar Articles