Rajendra Gupta

Piecemeal Database Restores in SQL Server

October 3, 2019 by

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: Recovery time objective and recovery point objective

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):

database backup sequence

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

database unavailability

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

database restore sequence

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:

piecemeal database restore SQL Server

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:

Execute sp_helpfile command

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

  • A table in filegroup FG-A

  • A table in filegroup FG-B

  • A table in filegroup FG-C

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:

Full database backup

Differential database backup

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

Differential database backup

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:

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:

RESTORE FILELISTONLY command

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 full database backup for piecemeal database restore SQL Server( Primary, FG-A and FG-B filegroups)

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 diffrential database backup for piecemeal database restore

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:

Restore transaction log backup

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:

database recovery

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

  • FG-A filegroup table

  • FG-B filegroup table

    Verify records

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:

error while accessing table records

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:

RECOVERY_PENDING status

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:

FG-C filegroup

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

Restore differential database

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

It automatically performs recovery for the FG-C filegroup:

Restore transaction log

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:

status of database

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views