In the Data Technology era, keeping data safe is more important than ever before

 

For companies, and for individuals who work with data storage and management, few things can be more important than data security.

Many companies store their core data in databases, where it is vulnerable to threats including hardware failures, malicious attacks, erroneous user operations, database corruption, and data loss caused by natural disasters.

In the new data technology (DT) age, it is vital that measures are taken against these threats to data security. For relational databases such as SQL servers, database backup provides a strong guarantee of disaster recovery (DR) capability. The data backup types outlined in this article can be effective tools for keeping data secure.

This article introduces the working principles and application processes for the following backup types:

1. Full Backup – a full copy of a database at a given point in time
2. Transaction Log Backup – a record of all transaction log changes submitted from the last log backup, or the full backup which the transaction log backup started from, to the current point-in-time
3. Differential Backup – a copy of all data changes from the last full backup to the current point in time

Full Backup

Full backup is the simplest and most fundamental database backup of all the SQL Server backups. Full backup stores a full copy of a specific database at the point in time that the backup completed.

The drawback of full backup is that it can only restore the database to the point in time when the backup completed successfully. This means that the system can only be restored to the point in time when full backup was completed, and not to any point in time within the database history.

How Full Backup Works

The following timeline provides a simplified illustration of how full backup works:

Figure source: SQL Server Backup Academy

The events shown in the timeline are as follows: 

• 7 p.m.: Data #1 is generated.
• 10 p.m.: The database is fully backed up and the backup file contains data #1.
• 2 a.m.: Data #2 is generated and the database contains data #1 and #2.
• 6 a.m.: Data #3 is generated and the database contains data #1, #2, and #3.
• 10 a.m.: The database is fully backed up and the backup file contains data #1, #2, and #3.
• 1 p.m.: Data #4 is generated and the database contains data #1, #2, #3, and #4.
• 5 p.m.: Data #5 is generated and the database contains data #1, #2, #3, #4, and #5.
• 8 p.m.: Data #6 is generated and the database contains data #1, #2, #3, #4, #5, and #6.
• 10 p.m.: The database is fully backed up and the backup file contains data #1, #2, #3, #4, #5, and #6.

In a real production environment, actual operations can be far more complex than the timeline suggests. Two important issues with the full backup process are:

• Backup operations can slow down I/O (input/output)

Database backup requires a large volume of I/O resources. As a result of this increased demand on I/O resources, database backup operations may slow down database queries.

 

• During the full backup process, the database transaction log cannot be truncated

=This may result in the transaction log growing until it takes up all the free space in the disk. For Alibaba Cloud RDS for SQL Server product users, this is a common scenario. Transaction log backup, which is also detailed in this article, is one potential solution.

Creating Full Backup with T-SQL

Full database backup can be completed with T-SQL statements by using BACKUP DATABASE statements. The following example shows how to create a full backup of the AdventureWorks2008R2 database:

 

Creating Full Backup with SSMS IDE 

SSMS integrated development environment (IDE) interface operations can also be used to create a full database backup.

The procedure is as follows:

1. Right click on the database to be backed up.
2. Select Tasks > Back Up.
3. Select Full for Backup Type.
4. Select Disk as backup file storage.
5. Click on Add to add backup file.
6. Select a directory to store the backup file.
7. Enter the backup file name.

The following screenshots are for reference:

Select Back Up from the drop-down menu

Back Up Database window

Transaction Log Backup

During the full backup process, the transaction log can grow significantly. Transaction log backup provides a solution to this issue: transaction log backup creates a record of all transaction log changes submitted from the last log backup, or the full backup which the transaction log started from, to the current point-in-time.

Transaction log backup can work with both full backup and differential backup to restore the database to a specific point in time.

 

How Transaction Log Backup Works

The following timeline shows how transaction log backup works: 

Figure source: SQL Server Backup Academy

The events shown in the timeline are as follows:

• 00:01: Transaction #1, which contains LSN #1, #2, and #3, starts and is submitted.
• 00:02: Transaction #2, which contains LSN #4, #5, and #6, starts but is yet to be submitted. The transaction log backup file contains only LSN #1, #2, and #3.
• 00:04: Transaction #2, which contains LSN #4, #5, and #6, is still running and has yet to be submitted.
• 00:05: Transaction #2 starts LSN #7. Transaction #2, which now contains LSN #4, #5, # 6, and #7, is submitted successfully. Transaction #3, which contains LSN #8, #9, and #10, starts but is yet to be submitted. The transaction log backup file contains LSN #4, #5, #6, and #7.
• 00:07: Transaction #3, which contains LSN #8, #9, and #10, is still running and has yet to be submitted.

How Transaction Log Backup Saves Space

Once transaction log backup has completed, the transaction log file can be truncated, and the transaction log space can be reused. This solves the issue of the transaction log growing constantly during a full backup.

Regular database transaction log backups help control transaction log file size. Regularly performing database transaction log backups is recommended. The database must be in the full recovery model before transaction log backup can be performed.

The following shows how to set the database to the full recovery model. The AdventureWorks2008R2 database is used for this example:

Creating Transaction Log Backup with T-SQL

The process for creating transaction log backup using T-SQL statements is as follows:

 

Creating Transaction Log Backup with SSMS IDE

The procedure for creating transaction log backup with SSMS IDE is as follows:

1. Right click on the database where the transaction log backup will be created.
2. Select Tasks > Back Up.
3. Select Transaction Log for Backup type.
4. Select Disk as backup file storage.
5. Click on Add to add backup file.
6. Select a directory to store the backup file.
7. Enter the backup file name.

The following screenshot is for reference:

Back Up Database window

Transaction Log Backup Chain

Transaction log backups are connected through transaction log chains. The transaction log chain always starts from a full database backup.  

Each transaction log backup file has its own FirstLSN and LastLSN.

• The FirstLSN equals the LastLSN of the previous transaction log backup file.
• The LastLSN equals the FirstLSN of the next transaction log backup file.

This is how connections are established between transaction log backup files.

This structure does mean that if any of the transaction log backup files are lost or damaged, the whole transaction log may be unable to recover. The following figure shows the interactive relationship between different files in the transaction log backup chain:

Figure source: SQL Server Backup Academy

The following example shows the relationship between transaction log chain files, as shown with search results from the RESTORE HEADERONLY method:

 

As shown:

• The LastLSN of AdventureWorks2008R2_log_201711122201 equals the FirstLSN of AdventureWorks2008R2_log_201711122202.
• The LastLSN of AdventureWorks2008R2_log_201711122202 equals the FirstLSN of AdventureWorks2008R2_log_

 

In this way links are established along the transaction log backup chain.

If any loss or damage happens to AdventureWorks2008R2_log_201711122202, the database can only restore the transactional behavior that is contained in AdventureWorks2008R2_log_201711122201.

Another issue is that transaction log backup must be performed at least every five minutes. This protects against the risk of losing data, prevents the database transaction log from constantly growing, and removes the need to constantly perform full backups.

If transaction log backup is performed every five minutes, this means 12 backups per hour, or 288 backups per day. This can cause the transaction log recovery chain to become too long, which in turn can extend recovery time and make it harder to achieve the recovery time objective (RTO). Differential backup technology can help address this issue.

Differential Backup

Differential backup backs up all data changes from the last full backup to the current point in time. It usually takes up much less data space than a full backup. It ensures greater backup efficiency, higher restoration speed, and better disaster recovery capability.

How Differential Backup Works

The following timeline shows how differential backup works:

   

Figure source: SQL Server Backup Academy

• 7 a.m.: Database contains data #1.
• 10 a.m.: Database is fully backed up and the full backup file contains data #1.
• 1 p.m.: Database contains data #1, #2, #3, and #4.
• 2 p.m.: Database is differentially backed up, and the differential backup file contains data #2, #3, and #4. This represents all changed data between the last full backup and the differential backup at 2 p.m.
• 4 p.m.: Database contains data #1, #2, #3, #4, #5, #6.
• 6 p.m.: Database is differentially backed up, and the differential backup file contains data #2, #3, #4, #5, and #6.
• 8 p.m.: Database contains data #1, #2, #3, #4, #5, #6, #7, and #8.
• 10 p.m.: Database is fully backed up and the full backup file contains data #1, #2, #3, #4, #5, #6, #7, and #8.
• 11 p.m.: New data #9 and #10 are generated and the database contains data #1, #2, #3, #4, #5, #6, #7, #8, #9, and #10.
• 2 a.m.: Database is differentially backed up, and the differential backup file contains data #9 and #10.

Creating Differential Backup with T-SQL

The following example shows the process for creating differential backup using T-SQL statements:

 

 

 

Creating Differential Backup with SSMS

The procedure for creating differential backup using SSMS IDE is as follows:

1. Right click on the database where the transaction log backup will be created.
2. Select Tasks > Back Up.
3. Select Differential for Backup type.
4. Select Disk as backup file storage.
5. Click on Add to add backup file.
6. Select a directory to store the backup file.
7. Enter the backup file name.

The following screenshot is for reference:

Back Up Database window

Looking Towards a More Secure Future

Each of the backup types covered in this article uses a different approach, and each has its benefits and drawbacks. These are summarized in the following table:

 

Given the tradeoffs involved in selecting one backup method over another, it can sometimes be difficult to know which backup types to use and when. An improved understanding of the different backup types available can help determine which backup solutions are most appropriate to which data storage situations

 

Alibaba Tech
First hand, detailed, and in-depth information about Alibaba’s latest technology → Search “Alibaba Tech” on Facebook