SQL Server Partial Data backup, a new backup type in SQL Server 2005

One of my training participant was asked discussing about refresh of large databases where I suggested him a partial backup strategy but I was shocked when he misunderstood the things and understood that partial backup is same filegroup backup.

So let's understand,

What is SQL Server Partial Database Backup ?

Partial backups were introduced for SQL Server 2005. Partial backups will backup the primary filegroup, any read/write file groups, and any read-only files that are specified during the backup. The partial backup is designed to not backup any data that is placed in a read-only file group. So, you can use a partial backup to backup your entire database – except for the read only data. Partial backups are meant to be used in a simple recovery model situation, although they can be used in a full recovery model situation as well.

Data Loss:

The partial backup provides bare minimum protection from data loss. The partial backup serves as a base for differential partial backups – which can be applied to the partial backup to bring the database closer to a point in time near the actual database failure. A full recovery model with transaction logs provides the best chance to restore the database up to the point of failure.

Size:

A key advantage of using a partial backup as opposed to a full database backup is the reduced file size. The read only filegroups only have to be saved once, therefore the partial backups will be smaller than what would be required for a full database backup.

Resources Required:

The strain on the database will likely be much less for a partial database backup than it is for a full database backup since specified read only filegroups won’t have to be backed up.

How to take a Partial Database Backup ?

Backup Type Partial and Differential partial are newly introduced in SQL Server 2005 and still not supported by SSMS and Maintenance Plans. To take this sort of backup we need to manually write SQL  statement like below

BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK'  -- Full Partial  backup 
BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK' WITH  DIFFERENTIAL  -- Differential Partial backup 

For More Details check BOL.