How to take Partial Backup Of Database in SQL Server

Hello Friend!!! Hope You are doing great. In this Small blog post I'm Going to explain how to take Partial Backup Of Database in SQL Server..

What Is Partial Backup?

As Per MSDN
"Partial backups 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."

This means that partial backups are only relevant for databases that contain read-only 
filegroups, If not then a partial backup will capture exactly the same data and objects as an equivalent full database backup.

Partial Backups are available to SQL Server 2005 and Later version. It was basically designed for large databases for which it will reduce backup and restore time.

How TO Take Partial Backup:

So Let see How to take Partial backup of SQL Server Database. Please use below script to create environment for same. You can not take partial Backup Using GUI so you have to use TSQL_script for same.

USE [master]
GO

CREATE DATABASE [PartialBackup] ON PRIMARY 
(   NAME = N'[PartialBackup]'
  , FILENAME = N'C:\Data\PartialBackups.mdf' 
  , SIZE = 10240KB  ), FILEGROUP [Read_only_Data] 
(   NAME = N'PartialBackup_ReadOnly'
  , FILENAME = N'C:\Data\PartialBackup_Read_Only.ndf' 
  , SIZE = 10240KB  ) LOG ON 
(   NAME = N'PartialBackup_log'
  , FILENAME = N'C:\Data\PartialBackups_log.ldf' 
  , SIZE = 10240KB )
GO

ALTER DATABASE [PartialBackup] SET RECOVERY SIMPLE
GO

Lets insert some data then we will change the filegroup "Read_only_Data" as read only.

USE [PartialBackup]
GO

CREATE TABLE dbo.RegistrationTable
    (
      ID INT IDENTITY  ,
      Name varchar(20)NOT NULL
    )
ON  [PRIMARY]
GO

CREATE TABLE dbo.Read_only_table
    (
      ID INT IDENTITY  ,
     Name varchar(20) NOT NULL
    )
ON  [Read_only_Data]
GO
INSERT  INTO dbo.RegistrationTable
VALUES  ('ABC'),('DEF'),('ETC')
INSERT  INTO dbo.Read_only_table
VALUES   ('XXX'),('YYY'),('ZZZ')

GO

Now Lets modify the FileGroup named as "Read_only_Data" to read only:

ALTER DATABASE [PartialBackup] MODIFY FILEGROUP [Read_only_Data] READONLY
GO

Now, before we take our first partial backup, we will take one backup copy of the whole database, including the read-only data, as the basis for any subsequent restore operations. 
By the way We can take a partial before taking a full database backup. But if you don't have a single full backup then from where you will restore read_only files in case of failure. So, its a good habit to take a full backup before partial backups.

USE [master]
GO
BACKUP DATABASE PartialBackup
TO DISK = N'C:\Data\PartialBackup_FULL.bak'
GO

We can see from Output that it processes both of our data files (Primary and Read_only_Data), plus the log file.

INSERT  INTO  PartialBackup.dbo.RegistrationTable
VALUES  ('WWW'),('RRRR'),('TTTTTT')
GO

Now take partial backup of database as:

Use PartialBackup

Go
BACKUP DATABASE PartialBackup READ_WRITE_FILEGROUPS
TO DISK = N'C:\Data\PartialBackup_PARTIAL_KA_Full.bak'
GO

We just added  READ_WRITE_FILEGROUPS in script to take partial backup as we want to exclude read_only filegroups from our backup.

We can see from the output that only the primary data file and the log file are processed. So we are good till now as we only wanted to exclude read_only FileGroup.

Now Lets see how to take Differential Partial Backup:

As we can Take differential database backups, which is based on a full database backup that is known as Base for differential backups. We can also take differential partial database backups that refer to a base partial database backup, and will capture only the data that changed in the read-write data files, since the base partial backup was taken.

Before taking partial differential backup insert few rows so that they can be included in your backup:

USE [PartialBackup]
GO
INSERT  INTO RegistrationTable
VALUES  ('FFFF'),(RTYU')
GO

We can use below script to take partial differential backup of database.

USE [master]
GO
BACKUP DATABASE [PartialBackup] READ_WRITE_FILEGROUPS
TO DISK = N'C:\Data\PartialBackup_PARTIAL_KA_Diff.bak'
WITH DIFFERENTIAL
GO

So this is how we can take partial backup of our database in SQL Server. In Next Blog I'm going to explain how to restore partial backups. It is almost same as normal backup restore process. Till then Enjoy. Wish you A Very Happy Weekends!!!

Thanks For Reading This Blog!!!