PARTIAL Backup in SQL Server Step by Step Tutorial with Examples

As per MSDN “PARTIAL Backups are designed for use under SIMPLE recovery model; thereby to improve flexibility to backup very large database which contain one or more read-only Filegroups. They are useful whenever you want to exclude read-only Filegroups. A partial backup resembles a full database backup, but a PARTIAL backup does not contain all the Filegroups. Instead, for a read-write database, a partial backup contains the data in the primary FileGroup, every read-write FileGroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary FileGroup.” For more information, see Partial Backup (SQL Server).

In the previous example we created a database named MyTechMantra which had three data files and one transactional log file. Two of the data files are residing in PRIMARY FileGroup and one data file was residing in a READ-ONLY FileGroup. Now to create a FULL PARTIAL Backup use the below TSQL script.

Create a FULL PARTIAL backup Using TSQL command

BACKUP DATABASE [MyTechMantra] READ_WRITE_FILEGROUPS
TO DISK = 'C:\DBBackups\MyTechMantra_Partial.BAK'
WITH NOFORMAT, NOINIT, COMPRESSION, STATS = 25
GO

Create a DIFFERENTIAL PARTIAL backup Using TSQL command

BACKUP DATABASE [MyTechMantra] READ_WRITE_FILEGROUPS
TO DISK = 'C:\DBBackups\MyTechMantra_Partial.DIF'
WITH DIFFERENTIAL, COMPRESSION, STATS = 25
GO