Partial Backups

partial backups Partial backups are a type of backup which was first introduced with SQL Server 2005. 
These types of backups were designed for use with the simple recovery model in order to improve flexibility in terms of backing up very very large databases that can contain at least one read-only filegroup.

Partial backups will work with all types of databases, with no dependency regarding the recovery model used.

What are Partial Backups?

Partial backups are similar to a FULL database backup, except that a partial backup will not contain all of the filegroups as a FULL backup would.

A partial backup will have inside it all of the data in the primary filegroup, every read/write filegroup, and possibly read-only files which can be specified optionally.

As said in the beginning, these types of backups are useful for situations with large databases and where you want to exclude read-only filegroups.

A partial backup of a database that is read-only will be composed out of only the primary filegroup.

How to create Partial Backups?

In order to create partial backups, you need to use the BACKUP statement along with the READ_WRITE_FILEGROUPS option. Also, you can specify any read-only file or any filegroup so it is included in this partial backup.

The general syntax for this type of backup is:

BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS
    FILEGROUP = 'TestDatabase_FileGroup1',
    FILEGROUP = 'TestDatabase_FileGroup2',
TO DISK = 'C:\Backup.bak'

Unfortunately, these types of backups cannot be created through a visual interface or a wizard in either SQL Management Studio or in a Maintenance Plan, thus writing a query is the only solution.

Partial backups can be used as a “differential base” for the actual differential partial backups.

By doing this you can add a short series of differential partial backups to a partial backup.

Leave a Comment