This article explores the piecemeal database restore SQL Server feature and its benefits to minimize the database restoration.

Introduction to RPO and RTO

The database administrator’s primary duty is to ensure the availability of the database as per defined RTO and RPO.

Let’s understand these two terms.

• Recovery time objective (RTO): It defines the time in which database should be brought online in case of any disaster
• Recovery point objective (RPO): It refers to data loss. We define maximum data loss in case of any disaster

In the following screenshot, we can see that RPO refers to acceptable data loss and RTO refers to the time taken for recovery. It is critical to meet these requirements and make the database available to avoid any business loss:

We take regular database backups with a combination of full, differential and log backups. We might think of high availability solutions for database recoveries. In this article, we focus on database backups.

Piecemeal database Restore SQL Server

Let’s consider a scenario in which you have a production standalone SQL Server, and it crashes. We cannot bring this server up. You need to restore databases on a separate server and bring them online ASAP. The database is huge, and the database restoration might take few hours to finish.

SQL Server enterprise edition offers a solution – Piecemeal database. In this technique, we can restore the database on a filegroup level and can reduce the downtime significantly. However, you need to design the database to make use of this piecemeal restoration. We will cover more in the later part of the article.

In the following example, we have a database, and it contains the following filegroups:

• Primary filegroup (default)
• Filegroup A
• Filegroup B
• Filegroup C

DBA usually follow the database backups in the following sequence:

• Full backups weekly
• Daily differential backup
• Log backup every hourly

In the following image, you can see a backup sequence (not mapped with the schedule):

Let’s say the database is down at highlighted point and you want to restore this database using backups on a different server:

You will follow the database restore sequence as follows:

• Restore the latest full backup
• Last differential backup
• Transaction log backup available after the last differential backup

The database size is enormous and due to this, it takes a long time for backup restoration. Let’s say this approach took 4-5 hours for database restoration. Can you afford such downtime? No, we need some mechanism to reduce the downtime and bring the application up ASAP. Piecemeal database restores SQL Server helps to minimize database restoration.

As we have multiple filegroups in the database, each filegroup contains the following data:

• FG- primary – Empty filegroup
• FG- A – data for department A
• FG- B – data for department B
• FG C- Archive data

The application uses data from FG A and FG B filegroup for all operations. Data in these filegroups should always be available.

FG C filegroup contains archive data. The size of this filegroup is approximately 45% of the overall database size. The application uses this data rarely. We can afford comparatively large downtime for this filegroup.

We can utilize a piecemeal database to restore SQL Server in this case. Look at the following image, and you can get the following points:

• We restored database backups and initially restored FG- Primary, FG- A and FG- B filegroups
• The database is available, and application can fetch data from online filegroups
• FC-C is still unavailable. It takes time for this filegroup restoration. Therefore, we start the restoration of this particular filegroup once all other filegroups are available
• FC-C filegroup also becomes online, and now the application can fetch data from this filegroup as well

Using this piecemeal database restoration, we minimized database downtime and application can be online without waiting for archival filegroup:

Demonstration for Piecemeal database Restore SQL Server

It sounds good, right. Let’s explore it practically.

For this demo, create a SQL database with multiple filegroups, as shown in the above image using the following query:

Execute sp_helpfile command to list down all data files, filegroups:

The following query creates a table on each filegroup and insert test data in it:

• A table in filegroup FG-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

• A table in filegroup FG-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

• A table in filegroup FG-C

  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' );

We perform full, differential, Log backups for creating the backup LSN chain.

Full database backup

In the output, we can see it takes a backup of all filegroups and transaction logs in full database backup:

Differential database backup

In the differential backup also, it takes the backup of all filegroups and transaction logs.

Transaction log backup

Insert another record in the archive table stored in FG-C filegroup before taking transaction log backup:

Below is the output of the transaction log backup:

Now, replicate the scenario that the database server is crashed, and you cannot access the server. In this case, you decided to restore this database on a separate server. We will use the same sequence for database restoration – full, differential and log backup.

We can verify the database files and their filegroups in the database backup before restoration. Execute RESTORE FILELISTONLY to view the files information inside the full backup.

We can see backup file information for all data files and their respective filegroups:

For this demonstration, I will restore the database on the source database instance with a different database name. In this restore database command, you can note that we are restoring primary, FG-A, and FG-B filegroups.

We use NORECOVERY clause so that subsequent backups can be restored on this database. We cannot restore further backups without NORECOVRY clause because SQL Server already performed database recovery and the database is online.

Restore full database backup for piecemeal database restore SQL Server (Primary, FG-A and FG-B filegroups)

In the output messages, it shows the data files that belong to filegroups primary, FG-A and FG-B. We do not have an entry for filegroup FG-C because we have not specified filegroup FG-C in the restore database command:

Restore differential database backup for piecemeal database restore SQL Server (Primary, FG-A and FG-B filegroups)

Let’s restore a subsequent differential backup. We do not need to specify the filegroups in the differential backup restoration command. It automatically restores content for primary, FG-A and FG-B filegroups:

Restore transaction log backup for piecemeal database restore SQL Server( Primary, FG-A and FG-B filegroups)

Restore transaction log backup after differential backup restoration with the following query:

Once we have done database restoration using full, differential and transaction log backup, we can perform database recovery using WITH RECOVERY clause. We can access the database after the recovery:

The database is online now, and we can access the data from FG-A and FG-B filegroups:

• FG-A filegroup table

  1 2	SELECT * FROM Customers;

• FG-B filegroup table

  1 2	SELECT * FROM Product;

  

We have not restored the FG-C filegroup, and if we try to access table archive from FG-C, we get the following error message. We cannot retrieve this record because the table resides in a filegroup that is not online:

We have partially recovered the database. In the example above, we saw that FG-C contains archive data and we have longer restore time for it, but the application will be functional with other filegroups. At this point, we can run the application with minimal impact on database availability. It is the benefit of piecemeal database restoration.

We can check the status of filegroups using the following query:

In the screenshot, we can see status RECOVERY_PENDING for the filegroup FG-C:

Let’s restore the remaining filegroup FG-C as well. We require restoration of all backups again but for this filegroup only.

Restore full database backup for piecemeal database restore SQL Server (FG-C filegroup)

In the output, we get an entry for FG-C filegroup only because we have specified FG-C on the restore database command:

Restore differential database backup for piecemeal database restore SQL Server (FG-C filegroup)

Restore transaction log backup for piecemeal database restore SQL Server(FG-C filegroup)

It automatically performs recovery for the FG-C filegroup:

Let’s check the status of all data files using sys.database_files:

In the output, we can verify that the complete database is online and accessible:

Conclusion

In this article, we explored the Piecemeal database Restore SQL Server. It is useful for the database restoration of a large database. We should plan the database structure in a way to enable piecemeal restoration effectively. It can help a DBA to minimize the application downtime.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

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

Latest posts by Rajendra Gupta (see all)

• 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