Understanding SQL Server Backup Types: What Are They and How to Choose?

The topic of SQL Server backup types is so broad that books have been written about it. But in this post, we’ll concentrate on the common backup options, learn what are they, how to choose them, and discuss factors that influence our decisions. This knowledge would then enable us to choose our database backup and restore approach.

SQL backup types:

Full backup:

Contains all the data in a database, a set of filegroups or files, and enough logs to enable data recovery. A full backup is the basis of any kind of backup type.

Full database backup takes longer and needs more storage space than other types, so you can supplement with a series of differential backups for large databases.

Before you start, you should know:

• Backup subjects should be online (except that tail log backup can back up the database offline).

• It is not possible to restore backups from more recent SQL Server versions in older SQL Server versions.

• No transaction, whether explicit or implicit, may contain the BACKUP statement.

• The device must grant read and write access to SQL Server, and the database account needs write access.

• Log in with the roles of sysadmin, db owner, and db backupoperator with permissions of BACKUP DATABASE and BACKUP LOG.

• These prerequisites apply to all SQL Server backup types.

You can create a full backup in SQL Server using SQL Server Management Studio, Transact-SQL, or PowerShell. Here, I’ll pick one way to demonstrate.

Using T-SQL to create a full backup:

BACKUP DATABASE databasename

To DISK='filepath.bak'

WITH options

Generally, WITH options include COMPRESSION | NO_COMPRESSION, NAME, FORMAT, DESCRIPTION, etc.

You can refer to Backup SQL Server Automatically for more backup details or the first method of Backup SQL Server into a .Bak File using SSMS.

Differential backup:

Contains only the data extents changed since the last full backup, which is much faster than a full backup.

Differential backup is especially useful when you often modify a database subset than the rest, and this facilitates frequent data backups, thus reducing the data loss risk, and it also saves backup time and storage space considering its smaller size than the base. Plus, this backup reduces the number of log backups to restore under the full recovery model.

Since differential backup increases in size over time, it is recommended that you take a new full backup at the set interval as the differential base.

You can use SQL Server Management Studio or Transact-SQL to create a differential backup or go to Create a SQL Server differential backup in 3 ways to find out more.

Using T-SQL to create a differential backup:

-- Create a full database backup first. 

BACKUP DATABASE databasename  

   TO filepath.bak  

   WITH INIT; 

GO 

BACKUP DATABASE databasename 

   TO filepath.bak 

   WITH DIFFERENTIAL; 

GO 

Transaction log backup:

Gathers all the transaction log records from the last full backup or transaction log backup to restore SQL Server to a particular point in time. It is used with full or bulk-logged recovery models.

A damaged transaction log loses the records since the most recent valid backup, so we suggest you put the log files on fault-tolerant storage and take frequent log backups based on your business requirements for RTO and RPO.

Using T-SQL to create a transaction log backup:

BACKUP LOG databasename

   To DISK='filepath'

   WITH options

GO

Tail Log backup:

Collects any log records that haven’t yet been backed up (the log tail) to avoid work loss and maintain the log chain integrity.

Tail log backup is used with a database operating in the FULL or BULK_LOGGED recovery model and gets a database back up and running even if it is offline or has a missing or damaged data file. However, it is unnecessary when you have the earlier log backup that contains the recovery point or you are moving or overwriting a database but you do now need to restore it.

Using T-SQL to create a tail log backup:

USE master;

GO

-- create a tail-log backup

BACKUP LOG databasename]

TO DISK = 'filepath'

WITH CONTINUE_AFTER_ERROR;

GO

CONTINUE_AFTER_ERROR: use it only if you back up the damaged database.

NORECOVERY: takes the database into a restoration state and the database won’t change after the backup. It also avoids online database errors.

Copy-only backup:

Special-use backup that is independent of the sequence of SQL Server backups without affecting the database backup and restore procedures.

Copy-only backups incorporate copy-only full backups (all recovery models) and copy-only log backups (full and bulk-logged recovery models only). The former cannot serve as a differential base and its restoration is the same as any other full backup; the latter keeps existing archive points and is sometimes used to perform an online restore.

Using SQL Server Management Studio to create a copy-only backup:

1.     Connect to SQL Server instance in Object Explorer.

2.     Expand Databases, right-click database to backup> Tasks > Back Up….

3.     On the General page Source section, check Copy-only backup.

4.     Click OK.

File backup:

Backs up all the data in single or more files or filegroups and log records to roll forward the files to the backup operation end. It can serve as the base for differential file backups.

Read-only filegroups file backup could be used with partial backups. File backup boosts recovery speed by only restoring the damaged files and increases the scheduling flexibility of full backups. Although it complicates administration at the same time.

Optionally, you can create a file backup in SQL Server using SQL Server Management Studio, Transact-SQL, or PowerShell. Note that it is not supported by  Maintenance Plan Wizard.

Using PowerShell to create a file/filegroup backup:

Use the Backup-SqlDatabase cmdlet and configure the Files for the BackupAction parameter.

Backup-SqlDatabase -ServerInstance ComputerInstance -Database <Databasename> -BackupAction Files -DatabaseFile "Filename"

Backup-SqlDatabase -ServerInstance ComputerInstance -Database <Databasename> -BackupAction Files -DatabaseFileGroup "Group1","Group2"

Use -DatabaseFileString (String refers to one or more files) to back up a specific file.

Use -DatabaseFileGroupString (String refers to one or more filegroups) parameter to back up all files in a filegroup.

Partial backup:

Contains the data in the primary filegroup, read-write filegroup, and one or more read-only files for a read-write database; while containing only the primary filegroup for a read-only database.

Partial backup is widely used in the simple recovery model to improve the backup flexibility of large database read-only filegroups.

Using T-SQL to create a partial backup:

BACKUP DATABASE Databasename READ_WRITE_FILEGROUPS

TO DISK = filepath.bak'

GO

Compare

Backing up SQL Server involves various backup tools and command lines, which makes the database backup a bit complicated. You can simplify SQL Server backup and recovery with Vinchin Backup & Recovery, in which the users are allowed to automate the database backup process with all kinds of backup options and smart strategies. And restore any backup points in 4 simple steps.

Centralized management of multiple database backups: Centrally manage Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, MariaDB, 10+ virtualizations, Linux & Windows Servers, and NAS backups in one web console to cut down management workload and costs.

Multiple backup options available: Choose from the full backup, differential backup, incremental backup, and transaction log backup (vary with databases) options.

Automatic backup: Set up backup schedules to start backup automatically.

Smart strategies: Save storage space with data compression, deduplication, and database compression. Meanwhile, transfer data fast and safely using encrypted and multithreaded transmission.

There are other features for you to explore, you can get a 60-day free trial below:

Sum Up

We've discussed different SQL Server backup types in this article about their basic concepts and how to choose them, which hopefully gives you a general idea of the backup types and how to use them when you are implementing your backup and recovery strategies.

And if you are looking for a simple SQL Server backup tool over complex methods, you can try the 60-day free trial of Vinchin Backup & Recovery now to streamline your database backup.