The most important task for a DBA to know is to be able to recover a database in the event of a database corruption issue. Corrupted databases can happen for lots of reasons, like hardware failures, power failures, incorrect shutdown of a server, failed upgrades, SQL injection, etc. The most common corruption problem that requires a DBA to recover a database is caused by human error such as forgetting to put a WHERE statement in a DELETE or UPDATE statement or accidentally deleting an object or the entire database.

In order to recover a corrupted database, a DBA will need to restore a database to a point in time before the corruption occurred. That recovery process is done using one or more database backups. There are a number of different types of database backups a DBA can create. In this article, I will cover the different backup types available in SQL Server.

Full Backup

The full backup, as it sounds, is a complete backup of a database. The full backup contains all the data in a database and can be used to do a complete restore of the database to the point-in-time that the full backup completed, less the uncommitted transaction in flight at that time. To remove the uncommitted transactions, the full backup also contains some transaction log information. The transaction log information in the backup is used, during the restore process, to roll back and remove any uncommitted transactions. By rolling backup uncommitted transactions, the database is left in a consistent state once the restore process completes.

When a full backup is created, it is written to an operating system file known as a media set. Discussion of media sets is outside the scope of this article. To create a full backup of a database, a DBA can use SQL Server Management Studio (SSMS) or can write a TSQL script. SSMS is typically used to take ad hoc backups of databases, whereas TSQL scripts are more often used to automate the backup process. Using TSQL scripts provides a repeatable backup process, that doesn’t require any other action besides submitting the script. The code in Listing 1 shows how to back up the sample AdventureWorks2019 database, using TSQL code.

Listing 1: TSQL Script to back up the AdventureWorks2019 database

The code in Listing 1 will create a .bak backup file in the backup folder for the default instance, on the C drive. The WITH FORMAT option tells SQL Server to create a new media header and backup set in this file. If the backup file already exists prior to this command being executed, it will be overwritten.

To back up a database using SSMS, you need to use the SSMS GUI. It takes several clicks and some typing to create a backup. Here are the steps to create a backup using the SSMS GUI:

Step 1: Expand the “Databases” item

Step 2: Right click on the “AdventureWorks2019” database

Step 3: Hover over the “Tasks” item from the dropdown menu

Step 4: Click on “Back Up…” from the context menu

Step 5: Make sure the “Backup Type” on the General page is set to “Full”

Step 6: Make sure the “Backup up to” is set to “Disk”

Step 7: Click on the “Add” button in the “Destination” section

Step 8: Browse or type in a location for the backup file

Step 9: Click on the OK buttons a couple of times to create the backup

The time it takes to perform a full back up a database is directly proportional to how much data is stored in the database. The more data stored in a database, the more time it takes to back up the database. Therefore, if the database is quite large, a DBA might want to consider taking other database backup types in addition to a full backup, like differential, as part of their backup and recovery plan.

Differential Backups

A differential back is a backup that copies only the data that has changed since the last full backup, also known as the delta changes. The amount of time it takes to perform a database backup is directly proportional to the amount of data a backup is required to write to the backup file. On a large database, it might take hours to back it up using a full backup. Whereas the time to take a differential backup is directly proportional to the amount of data that has changed since the last full backup. If your backup window is short, and the amount of data changed in a database since the last full backup is small then taking a differential backup will optimize the runtime and still provide a recovery point for your database.

In order to take a differential backup, you must first take a full backup. The full backup is the base for any follow-on differential backup. Each time a differential backup that is taken the delta changes (data changed since the full last backup) are written to the backup file. The amount of data and the time to take to perform a differential backup is directly proportional to the number of updates that have occurred since the last full backup. Therefore, over time a differential backup will get bigger and bigger the more a database is updated and will take longer and longer to run.

Because the differential backups grow in size over time, it is recommended that periodically a new base full backup be taken. Taking a new full backup will reset the number of delta changes back to zero, and thus help keep subsequent differential backups from being really large. A good timing for full and differential backups might be to take a full back up once a week, like on a Sunday, and take differential backups once a day the rest of a week.

One thing to keep in mind when using differential backups is that the restore process is more complicated. In order to restore to a differential backup recovery point, you first have to restore the base full back up before you restore the differential backup. This means you need to run two different restore processes, one for the full backup, and one for the differential backup.

A differential backup can be taken using a TSQL script or using SSMS. To take a differential backup of the AdventureWorks2019 database, you can run the TSQL code in Listing 2.

Listing 2: TSQL Script to take a differential backup

If you compare the script in Listing 2 with Listing 1, you can see the I added the word Differential to the WITH option. This is all it takes to make a differential backup of a database. To use SSMS, you have to follow the same steps as taking a full backup shown in the Full Backup section but change the Backup Type to Differential in Step 5.

Tail Log Backup

The tail-Log backup is a special type of transaction log backup. As the name implies, it backs up the tail of the log, meaning those log records that have yet to be backed up, as part of the normal transaction log backup process. In reality, it is just another transaction log backup, but it is the last transaction log backup taken to ensure that all transaction log records for a database have been backed up so there is no data loss when restoring or moving a database to another server using a restore operation. The tail-log can be taken even when a database is offline or has a missing or corrupted data file. This type of backup is only supported when a database is in Full or Bulk-Load recovery model. To take a take tail log backup, you can use the same script and steps as described in the Transaction Log backup section.

Transaction Log Backup

If you want to do a restore to a point-in-time other than when full or differential backups are taken, then you will want to perform transaction log backups periodically, in addition to full and/or differential backups. A transaction log backup copies the transaction log records off to a backup file. Transaction log backups are only supported if a database is using the Full or Bulk-Logged recovery model.

Running transaction log backups keeps the transaction log file from growing out of control when your database is using Full or Bulk-Logged recovery model. When a transaction log backup is taken, the backup process deletes the unused portions of the log files, known as Virtual Log Files (VLF’s), once that information has been copied to the backup file. Deleting these unused/inactive VLF’s frees up space in the log file for reuse. Therefore, if you want to keep your keep the transaction log cleaned up and keep it from growing, you should take transaction log backups periodically.

There is no hard and fast rule to how often you should run a transaction log backup. The frequency depends on how many update transactions are being written to the transaction log, and how much data loss a database can tolerate. Therefore, it is recommended that you take transaction log backups frequently to minimize data loss and to keep the transaction log cleaned up, so it doesn’t run out of space or need to be expanded.

The only requirement to take a transaction log, besides the database being in Full or Bulk-Logged recovery model, is that a full backup has been taken. After a full backup has been taken, you can run as many transaction log backups you want and as often as you like. Typically, full backups are taken daily or weekly depending on the size of the backup window and the time it takes to perform a full backup. Transaction log backups are then taken throughout the day/week at a frequency short enough to keep the transaction log cleaned up and meet the data loss requirements of the database. Keep in mind that if you have a lot of transaction log backups between each full backup, then a lot of transaction log backups may be needed to recover to a failure point. If you find you have an unmanageable number of transaction logs backups between each full back up, then you might want to consider taking differential backups periodically between each full backup. By doing this, you can keep the number of transaction log backups needed for recovery down to a reasonable number.

To take a transaction log backup, you can run a script similar to the one shown in Listing 3. The script in Listing 3 takes a transaction log backup of the AdventureWorks2019 database.

Listing 3: Performing a transaction log backup with a script

If you want to use SSMS to take a transaction log backup, you can follow the steps in the Full Backup section, but change the Backup Type to Transaction Log in Step 5.

Copy-Only Backups

Copy-Only backups are special backups used to take out-of-band backups for special situations. Copy-Only backups do not affect the existing sequence of the normal routine backups. Meaning it will not be considered part of the normal set of backups used to recover a database. This is why these Copy-Only backups are ideal for creating backups copies that can be used for other purposes beside recovering the database, like copying a production database to a development environment.

Copy-Only backups can be taken regardless of the recovery model of the database. Only full and transaction log backups can be created using the Copy-Only option. Differential Copy-Only backups are not supported. If you are using Availability Groups, then Copy-Only backups can be taken on secondary replicates. To take a Copy-Only backup using TSQL, all that is needed is to add the Copy-Only option to the WITH parameter as shown in Listing 4.

Listing 4: Copy-Only Backup

To use SSMS to create a Copy-Only back, all that is needed is to follow these steps: Note Step 5.1 was added to support the Copy-Only backup option.

Step 1: Expand the “Databases” item

Step 2: Right-click on the “AdventureWorks2019” database

Step 3: Hover of the “Tasks” item from the dropdown menu

Step 4: Click on “Back Up…” from the context menu

Step 5: Make sure the “Backup Type” on the General page is set to “Full”

Step 5.1: Check the “Copy-only Backup: checkbox

Step 6: Make sure the “Backup up to” is set to “Disk”

Step 7: Click on the “Add” button in the Destination

Step 8: Browse or type in a location and type in the file name for the backup file

Step 9: Click on the OK buttons a couple of times to create the backup

Additional Backups Types

The full, differential and transaction log backups are the normal backups used in most database corruption situations. With the Copy-Only backup being used to support out-of-band backups for special processing, and the tail-log backups to support backing up the last few transactions in the log that have not been backed up. The Additional backup types listed below are for completeness. These backup types are used for databases that have additional special backup requirements that make the backup types listed above impractical like, performance or size.

File and File Group Backups

By default, the data in a database is contained in a single Operating System (OS) file. But there are times when a database may be contained in multiple OS files. These files are then grouped together into a single file group, or multiple file groups to make up the complete database. When a database is divided into multiple files, and/or file groups you can back up the database using piece meal approach, by using file and/or file group backups instead of back up the entire database. All recovery models support file/filegroup backups. By performing file or filegroup backups, a DBA can schedule a database backup in pieces. Backing up a database in pieces helps manage and schedule backing up large databases. By using a file and/or filegroup backup, a DBA can speed up recovery from a database corruption problem by only restoring the files in the database that are corrupted. This flexibility does come at a cost. The backup and recovery process is more complicated and requires more backup files to create and manage.

Partial Backups

A partial backup, as it sounds, is a backup that only backs up part of the database. Partial backups are used to back up the data in the primary filegroup and all of the read-write filegroups. Optionally it can also be used to back up the read-only filegroups. All recovery models support partial backups. Partial backups are a great way to reduce the run time and the size of the database backup when large amounts of the data in the read-only filegroups doesn’t change.

Summary

Being able to backup and recovery a database when a database corruption event occurs should be the number one priority for a DBA. A DBA has many different options for backing up a database, from a single full back up to a set of piecemeal backups using, differential, transaction log, file/file groups, or partial backups. When minimal data loss is important, transaction log backups need to be used in conjunction with full and differentials backups. A DBA needs to understand all the different backup/recovery options available, as well as the data loss requirements for a database when developing a backup strategy. Knowing how much data can be lost allows a DBA to develop a backup/recovery strategy to meet the performance and data loss requirements for their databases.