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:
Create database [MultipleFG]
(name = N'MultipleFG', filename = N'T:\DB\MultipleFG.mdf'),
(name = N'MultipleFG_A', filename = N'T:\DB\MultipleFG_A.ndf'),
(name = N'MultipleFG_B', filename = N'T:\DB\MultipleFG_B.ndf'),
(name = N'MultipleFG_C', filename = N'T:\DB\MultipleFG_C.ndf')
(name = N'MultipleFG_log', filename = N'T:\DB\MultipleFG_log.ldf')
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-A1234567891011CREATE TABLE dbo.Customers(ID INT NOT NULL,CustomerName NVARCHAR(50) NOT NULL,)ON [FG-A];INSERT INTO CustomersVALUES(1,'Raj');GO
A table in filegroup FG-B1234567891011CREATE TABLE dbo.Product(ID INT NOT NULL,productname NVARCHAR(50) NOT NULL,)ON [FG-B];INSERT INTO productVALUES(1,'DB');GO
A table in filegroup FG-C12345678910CREATE TABLE dbo.archive(ID INT NOT NULL,archivedata NVARCHAR(50) NOT NULL,)ON [FG-C];INSERT INTO archiveVALUES(1,'archivedata');
We perform full, differential, Log backups for creating the backup LSN chain.
Full database backup
BACKUP DATABASE [MultipleFG] TO DISK = N'C:\sqlshack\Demo\MultipleFG.bak' WITH NOFORMAT, INIT, COMPRESSION, STATS = 10;
In the output, we can see it takes a backup of all filegroups and transaction logs in full database backup:
Differential database backup
BACKUP DATABASE [MultipleFG] TO DISK = N'C:\sqlshack\Demo\MultipleFG_diff.bak' WITH DIFFERENTIAL, NOFORMAT, INIT, COMPRESSION, STATS = 10;
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:
INSERT INTO archive
BACKUP log [MultipleFG] TO DISK = N'C:\sqlshack\Demo\MultipleFG_log.trn' WITH NOFORMAT, INIT, COMPRESSION, STATS = 10;
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.
RESTORE FILELISTONLY from disk='T:\DB\MultipleFG.bak'
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)
RESTORE DATABASE [MultipleFG_clone] FILEGROUP = 'Primary', FILEGROUP = 'FG-A', FILEGROUP = 'FG-B' FROM DISK = N'T:\DB\MultipleFG.bak' WITH FILE = 1, MOVE N'MultipleFG' TO N'T:\database\MultipleFG.mdf', MOVE N'MultipleFG_A' TO N'T:\database\MultipleFG_A.ndf', MOVE N'MultipleFG_B' TO N'T:\database\MultipleFG_B.ndf', MOVE N'MultipleFG_log' TO N'T:\database\MultipleFG_log.ldf', NORECOVERY, PARTIAL, STATS = 10;
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 DATABASE [MultipleFG_clone] FROM DISK = N'T:\DB\MultipleFG_diff.bak' WITH FILE = 1, NORECOVERY, STATS = 10;
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 DATABASE [MultipleFG_clone] FROM DISK = N'T:\DB\MultipleFG_log.trn' WITH FILE = 1, NORECOVERY, STATS = 10;
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:
RESTORE DATABASE [MultipleFG_clone] WITH RECOVERY;
The database is online now, and we can access the data from FG-A and FG-B filegroups:
FG-A filegroup table12SELECT *FROM Customers;
FG-B filegroup table12SELECT *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:
Select * from archive
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:
select file_id, name, state_desc, physical_name
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)
RESTORE DATABASE [MultipleFG_clone] FILEGROUP = 'FG-C' FROM DISK = N'T:\DB\MultipleFG.bak' WITH FILE = 1, MOVE N'MultipleFG_C' TO N'T:\database\MultipleFG_C.ndf', STATS = 10;
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 DATABASE [MultipleFG_clone] FILEGROUP = 'FG-C' FROM DISK = N'T:\DB\MultipleFG_diff.bak' STATS = 10;
Restore transaction log backup for piecemeal database restore SQL Server(FG-C filegroup)
It automatically performs recovery for the FG-C filegroup:
RESTORE DATABASE [MultipleFG_clone] FROM DISK = N'T:\DB\MultipleFG_log.trn';
Let’s check the status of all data files using sys.database_files:
select file_id, name, state_desc, physical_name
In the output, we can verify that the complete database is online and accessible:
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.
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 firstname.lastname@example.org
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Import data from multiple Excel files in SQL Server tables using an SSIS package - December 13, 2019
- An overview of native differential backups in AWS RDS SQL Server - December 9, 2019
- An overview of SQL Server data types - December 9, 2019