Rajendra Gupta
database backup sequence

The Partial SQL Sever Database Backup

October 30, 2019 by

This article explores the partial backup SQL Database strategy in SQL Server and its usages for read-only filegroups.

Introduction

In the article, Piecemeal Database Restores in SQL Server, we explored the benefit of database filegroups and their usages to minimize application downtime. We can restore the critical data filegroups for application availability and later restore the filegroup with less critical data. It saves overall database recovery time and increases database availability.

In the following figure, we can reference a database with multiple filegroups and backup sequence:

database backup sequence

Suppose we have a database with four filegroups FG-Primary, FG-A, FG-B, and FG-C. FG-C filegroup is marked as read-only. It contains archived data and it cannot be modified. Users access data from this filegroup tables for read-only or reporting purposes:

Database with multiple filegroup

In full database backup, we take complete backup including all filegroups. We might not require backup for read-only filegroup because its data is static. If we find a way to exclude this filegroup in backup, it can save space as well as time. We can perform database recovery as well in less time. SQL Server provides partial backups to accomplish this task. Let’s explore the partial backup in the next section.

Overview of Partial backup SQL Database

A partial backup allows taking backup for read-write filegroups data for the SQL Server database. It does not include read-only filegroup data in the partial backups. It takes backup for primary and read-write filegroups and excludes read-only filegroup data:

  • Partial backup is available from SQL Server 2005 onwards
  • It is supported in all database recovery models, i.e. simple, full and bulk-logged

Suppose we have an online shopping database. This database holds customer purchase records.

This database contains the following filegroups:

  • FG-Primary: It holds master tables
  • FG-A: It holds customer records and service request information
  • FG-B: It holds customer orders for the current financial year
  • FG-C: It holds customer previous years’ data. This data is static and does not change. We require this data to generate a report for internal usage

Use the following scripts for creating the database to meet the above request.

Script to create partialFG database with multiple filegroups:

Script to change recovery model of PartialFG database to Simple:

Create a table in filegroup PartialFG_A:

Create a table in filegroup PartialFG_B:

Create a table in filegroup PartialFG_B:

Modify filegroup FG-C from read-write to read-only:

We might get the following error message if the database is in use by other sessions. You should close the sessions and execute an alter database command again:

Database error message

You get the following confirmation message once the specific filegroup is marked as read-only:

confirmation message

You cannot insert, update any data in the read-only filegroup. It gives the following error message for any DML operation:

Error while inserting data in read-only filegroup

In the screenshot, we can see that is_read_only flag is set to 1 for data file PartialFG_C that belongs to FG_C filegroup:

Database files states

Let’s take a full database backup SQL Database. It includes backup for filegroups including read-only filegroup as well. It acts as a restoration point for further database backups. Since this backup will include read-only filegroup data as well, we can restore this backup file on a secured location to take reference once required:

In the output, we can verify that it includes read-only filegroup data as well:

backup progress

Generate some workload before taking the partial database backup:

Taking a partial database backup SQL Database is straightforward. We require to add READ_WRITE_FILEGROUPS argument in the backup database command. This option instructs SQL Server to initiate a partial filegroup backup for read-write filegroups.

In the output, we can see it does not include backup for read-only filegroup FG-C. It meets our requirement to exclude read-only filegroup in the database backup:

Partial database backup

Generate database activity for taking differential database backup:

To take a differential backup, we need to add WITH DIFFRENTIAL clause in the script:

Partial backup WITH DIFFRENTIAL

You might think of a question here. Differential backup SQL Database always takes full backup as a reference point. It includes data changes from the last full backup. In this case, we took partial differential backup after a partial database backup.

What will be the reference point of this partial differential backup?

We use the RESTORE HEADERONLY command to check the backup LSN from existing DB backup. The following queries check the LSN information for full, partial and partial differential database backup:

Look at the highlighted LSN information of all database backups:

  • Full database backup Last LSN = Partial database backup (DatabaseBackups LSN)
  • Full database backup Last LSN Partial database differential backup (DatabaseBackups LSN)

It shows differential database backup points to partial database backup LSN. We require restoring partial backup before restoration of partial differential backup:

backup LSN for backup sql database

Performing Partial Database restores

In this section, we look at the database restoration process for the partial database backup SQL Database. We will explore different restore process for the partial database backups.

Restoring a full Partial backup

Here, we aim to perform the following backup’s restoration:

  • Restore Full database backup in NORECOVERY mode: It restores a database from both read-write and read-only filegroups. Norecovery mode allows restoring a further database backup
  • Restore partial database backup in RECOVERY mode: In this step, SQL Server restores partial database backups and perform database recovery

Restore progress

After the restoration of full and partial database backup, all the filegroups are online. We can access the database from the read-only filegroup as well:

Read-only filegroup status

Restoring a differential Partial backup SQL Database

In this method, we perform the following database restoration:

  1. Restore full backup in NORECOVERY mode
  2. Restore partial database backup in NORECOVERY mode
  3. Restore partial differential backup in RECOVERY mode

As highlighted earlier, we cannot apply partial differential backup directly on the partial differential backup. A partial differential backup contains data after the partial database backup. If we try to skip step 2 (Restore partial database backup), we get the following error message:

Database recovery error message

The message clearly states that the current state of the filegroup should have differential backup applied. We skipped the partial database backup; therefore, further partial differential backup gives the error

Let’s follow the restore sequence and it works fine:

Restore database sequence

Useful points about partial backup SQL Database

  • Partial database backup is available in all recovery models
  • It is mostly useful for simple recovery model with read-only filegroups
  • We can exclude read-only filegroups in the partial database backups
  • You cannot take partial database backup using SSMS graphical tool. We require t-SQL for taking partial database backup

Conclusion

In this article, we explored partial backup SQL Databases for excluding read-only filegroups. It gives flexibility for the large database backup. Database backups are vital for recovery in any disaster scenario. You should plan, validate the restoration plan as per the criticality, data requirements RPO and RTO.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
217 Views