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:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE DATABASE [PartialFG] ON PRIMARY ( NAME = N'PartialFG', FILENAME = N'C:\Export\PartialFG.mdf' ), FILEGROUP [FG-A] ( NAME = N'PartialFG_A', FILENAME = N'C:\Export\PartialFG_A.ndf' ), FILEGROUP [FG-B] ( NAME = N'PartialFG_B', FILENAME = N'C:\Export\PartialFG_B.ndf' ), FILEGROUP [FG-C] ( NAME = N'PartialFG_C', FILENAME = N'C:\Export\PartialFG_C.ndf' ) log ON ( NAME = N'PartialFG_log', FILENAME = N'C:\Export\PartialFG_log.ldf' ); |
Script to change recovery model of PartialFG database to Simple:
|
1 2 |
ALTER DATABASE PartialFG SET RECOVERY SIMPLE GO |
Create a table in filegroup PartialFG_A:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.Customers (ID INT NOT NULL, CustomerName NVARCHAR(50) NOT NULL, ) ON [FG-A]; INSERT INTO Customers VALUES (1, 'Raj' ); GO |
Create a table in filegroup PartialFG_B:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.Product (ID INT NOT NULL, productname NVARCHAR(50) NOT NULL, ) ON [FG-B]; INSERT INTO product VALUES (1, 'DB' ); GO |
Create a table in filegroup PartialFG_B:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE dbo.archive (ID INT NOT NULL, archivedata NVARCHAR(50) NOT NULL, ) ON [FG-C]; INSERT INTO archive VALUES (1, 'archivedata' ); |
Modify filegroup FG-C from read-write to read-only:
|
1 2 |
ALTER DATABASE PartialFG MODIFY FILEGROUP [FG-C] READONLY GO |
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:
You get the following confirmation message once the specific filegroup is marked as read-only:
You cannot insert, update any data in the read-only filegroup. It gives the following error message for any DML operation:
|
1 2 3 4 5 |
SELECT name, physical_name, state_desc, is_read_only FROM sys.database_files; |
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:
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:
|
1 |
backup database PartialFG to disk='T:\DB\PartialFG.bak' with stats=10 |
In the output, we can verify that it includes read-only filegroup data as well:
Generate some workload before taking the partial database backup:
|
1 2 3 4 5 6 7 8 |
INSERT INTO Customers VALUES (1, 'Raj' ); UPDATE product SET productname = 'database'; |
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:
Generate database activity for taking differential database backup:
|
1 2 3 4 5 6 7 8 |
INSERT INTO Customers VALUES (1, 'Monu' ); UPDATE product SET productname = 'database SQL'; |
To take a differential backup, we need to add WITH DIFFRENTIAL clause in the script:
|
1 2 3 4 |
BACKUP DATABASE PartialFG READ_WRITE_FILEGROUPS TO DISK = N'T:\DB\PartialFG_readwrite_diff.bak' WITH DIFFERENTIAL GO |
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:
|
1 2 3 |
RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG.bak'; RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG_readwrite.bak'; RESTORE HEADERONLY FROM DISK = 'C:\Export\PartialFG_readwrite_diff.bak'; |
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:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [master] GO RESTORE DATABASE [Restore-PartialFG] FROM DISK = N'C:\Export\PartialFG.bak' WITH NORECOVERY GO RESTORE DATABASE [Restore-PartialFG] FROM DISK = N'C:\Export\PartialFG_readwrite.bak' WITH RECOVERY GO |
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:
Restoring a differential Partial backup SQL Database
In this method, we perform the following database restoration:
- Restore full backup in NORECOVERY mode
- Restore partial database backup in NORECOVERY mode
- 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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE [master] GO RESTORE DATABASE [Restore-PartialFG] FROM DISK = N'C:\Export\PartialFG.bak' WITH NORECOVERY GO RESTORE DATABASE [Restore-PartialFG] FROM DISK = N'C:\Export\PartialFG_readwrite.bak' WITH NORECOVERY GO RESTORE DATABASE [Restore-PartialFG] FROM DISK = N'C:\Export\PartialFG_readwrite_diff.bak' WITH RECOVERY GO |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023