Types of SQL Server Data Backups and Full vs Differential vs File vs Filegroup vs Partial - Transaction with Syntax

Overview

SQL Server offers many options for creating backups.  In a previous topic, Recovery Models, we discussed what types of backups can be performed based on the recovery model of the database.  In this section we will talk about each of these backup options and how to perform these backups using T-SQL.

The different types of backups that you can create are as follows:

1.     Full backups

2.     Differential backups

3.     File backups

4.     Filegroup backups

5.     Partial backups

6.     Transaction log backups

7.     Copy-Only backups

8.     Mirror backups

1. SQL Server Full Backups      

Overview

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'

GO

2. SQL Server Differential Backups      

Overview

Another option to assist with your recovery is to create "Differential" backups.  A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL

GO

3. SQL Server File Backups      

Overview

Another option for backing up your databases is to use "File" backups.  This allows you to backup each file independently instead of having to backup the entire database.  This is only relevant when you have created multiple data files for your database.  One reason for this type of backup is if you have a very large file and need to back them up individually.  For the most part you probably only have one data file, so this is option is not relevant.

Explanation

As mentioned above you can back up each data file individually.  If you have a very large database and have large data files this option may be relevant.

BACKUP DATABASE MyDBBackup FILE = 'MyDBBackup' TO DISK = 'C:\MyDBBackup_MyDBBackup.FIL'

GO

BACKUP DATABASE MyDBBackup FILE = 'MyDBBackup2'TO DISK = 'C:\MyDBBackup_MyDBBackup2.FIL'

GO

4. SQL Server Filegroup Backups      

Overview

In addition to doing "File" backups you can also do "Filegroup" backups which allows you to backup all files that are in a particular filegroup.  By default each database has a PRIMARY filegroup which is tied to the one data file that is created.  You have an option of creating additional filegroups and then placing new data files in any of the filegroups.  In most cases you will probably only have the PRIMARY filegroup, so this is topic is not relevant.

Explanation

As mentioned above you can back up each filegroup individually.  The one advantage of using filegroup backups over file backups is that you can create a Read-Only filegroup which means the data will not change.  So instead of backing up the entire database all of the time you can just backup the Read-Write filegroups.

BACKUP DATABASE MyDBBackup FILEGROUP = 'ReadOnly' TO DISK = 'C:\MyDBBackup_ReadOnly.FLG'

GO

5. SQL Server Partial Backups      

Overview

A new option is “Partial” backups which were introduced with SQL Server 2005.  This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files.  This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.

Explanation

A Partial backup can be issued for either a Full or Differential backup.  This can not be used for Transaction Log backups.  If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.

--Create a full partial backup

BACKUP DATABASE MyDBBackup READ_WRITE_FILEGROUPS TO DISK = 'C:\MyDBBackup_Partial.BAK'

GO

--Create a differential partial backup

BACKUP DATABASE MyDBBackup READ_WRITE_FILEGROUPS TO DISK = 'C:\MyDBBackup_Partial.DIF'

WITH DIFFERENTIAL

GO

6. SQL Server Transaction Log Backups      

Overview

If your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups.  By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred.  The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log.

Explanation

A transaction log backup allows you to backup the active part of the transaction log.  So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'

GO

 7. Copy-Only Backups (SQL Server)

Overview

Copy-only full backups (all recovery models) A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

Explanation

The types of copy-only backups are as follows:

1.     Copy-only full backups (all recovery models)

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Restoring a copy-only full backup is the same as restoring any other full backup.

2.     Copy-only log backups (full recovery model and bulk-logged recovery model only)

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore.

8. Mirror backups

Overview

To distribute copies of a subset of the filegroups in a database, use replication: replicate only those objects in the filegroups you want to copy to other servers.

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

The mirror database is created by restoring, without recovering, backups of the principal database on the mirror server. The restore must keep the same database name.

Referred: Microsoft site & DBA site.