The peculiarity of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though all recovery models in SQL Server support partial backup, it was primarily designed for use under the simple recovery model as it makes backup of huge databases, containing one or more read-only filegroups, more flexible.
A partial backup can be applied both to full or differential backups, though it is not applicable to transaction log backup. As far as differential partial backup is concerned, only the data extents that have changed in the filegroups since the previous partial backup are recorded into it. Please note that differential partial backups can be applied only with partial backups.
Let’s consider the picture below to understand how partial backup and differential partial backup work:
Below I’ll explain what is shown in the picture. Take a huge database that uses a simple recovery model, where the full database backup was made at 12:00:
BACKUP DATABASE your_database TO DISK = 'full.bak'
Because the size of the database is large, and it takes more time to make backup, a decision was made to create a partial database backup, which includes the primary filegroup and read-write secondary filegroup:
BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_full.bak'
This backup takes less time than a full database backup. Following that some changes were made in the primary filegroup and the read-write secondary filegroup. These changes were stored in the following differential partial backup that was created at 14:00:
BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_diff.bak' WITH DIFFERENTIAL
Note that SQL Server Management Studio does not support partial backups.
Here is another, more interesting case. Assume that the database was changed from read-only to read-write after a partial backup was made. In this case, perhaps, some secondary read-write filegroups will not be included in the partial backup, therefore, when the time comes to make a differential partial backup the following error occurs:
Msg 233, Level 20, State 0, Line 2 A transport-level error has occurred when sending the request to the server.
To avoid this error, make another partial backup before a differential partial backup. Following this operation, the new partial backup will contain every read-write secondary filegroup.
Restore Process
The commands for the database restore process from the example above should be executed in the following order:
RESTORE DATABASE your_database FROM DISK = 'full.bak', WITH NORECOVERYGORESTORE DATABASE your_dataabse FROM DISK ='partial_backup_full.bak', WITH NORECOVERY GO RESTORE DATABASE your_database FROM DISK = 'partial_backup_diff.bak', WITH RECOVERY GO
