Piecemeal restore of a partitioned table

A few weeks ago I presented my Advanced Backup and Restore session on SQL Rally in Denmark.
One of the topics that I showed was the piecemeal restore functionality. This allows you to restore your database in “pieces”. The files or filegroups that have been restored are already accessible while the others are still restoring. Jes Borland (blog | twitter) has made a good blog post on this topic.
During the session, I got a question if it was possible to restore filegroups that were used by a partioned table, which should result that the table would be partially available.
My answer to that question was that you had to restore all the files before the table becomes available, but I figured out that my answer was not correct.

With this blog post I want to correct my answer.

Setup the test scenario

For testing purposes, I created a database, MyDatabase, with 5 filegroups: PRIMARY, Partition1, Partition2, Partition3 and Partition4.

Once the database was created I added my partition function and partition scheme.
As you can see, the partition function has 3 boundaries (1, 100, 1000) and the partition scheme is created that specifies the filegroups to hold each one of the four partitions.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (Partition1, Partition2, Partition3, Partition4);

Next step was creating a partitioned tabled based on the partition scheme

--Create Partitioned table
create TABLE PartitionedTable 
	(PartitionColumnID int not null,
	Name varchar(100) not null,
CONSTRAINT [PK_PartitionColumnID] PRIMARY KEY CLUSTERED 
(
	PartitionColumnID ASC	
) ON [myRangePS1]([PartitionColumnID])	
)ON myRangePS1(PartitionColumnID);

And fill the table with 1000 records

declare @i as int
set @i=1
while @i<=1000
	begin
		insert into PartitionedTable
		select @i,'Name' + CAST(@i as varchar)
		set @i=@i+1
	end

The query below give me the number of records per partition

SELECT $PARTITION.myRangePF1(PartitionColumnID) AS Partition, 
COUNT(*) AS [COUNT] FROM PartitionedTable
GROUP BY $PARTITION.myRangePF1(PartitionColumnID)
ORDER BY Partition ;
GO

Restore the partitioned table

Before I can start with a piecemeal restore, I need to have a backup of course of my database. Don’t forget to change the <BackupPath> in the script.

--Take a new FULL BACKUP
BACKUP DATABASE [MyDatabase]
TO DISK = N'<BackupPath>MyDatabase.bak' WITH COMPRESSION,INIT
GO
BACKUP LOG [MyDatabase] TO DISK=<BackupPath>MyDatabase_Log.trn' WITH FORMAT,INIT,COMPRESSION
GO

Ok, let’s drop the database and begin with the piecemeal restore

USE [master]
GO
ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatabase]
GO

First of all, I’m going to restore the PRIMARY filegroup so that my database is already available and “ONLINE” again.

RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'PRIMARY'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH PARTIAL, NORECOVERY 
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

When I check the status of my filegroups, you can see that my PRIMARY file is already ONLINE

SELECT [name], [state_desc] 
FROM [MyDatabase].sys.database_files;
GO

Secondly, I restore the Partition1 filegroup. Don’t forget to always apply the transaction log backup.

USE master;
GO
RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'Partition1'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH NORECOVERY
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

When I checked the status of my filegroups again, the Partition1 filegroup is now ONLINE

I know that in this filegroup, there is 1 record stored (see “the number of records per partition”), so let’s see if I can query the table

use [MyDatabase]
GO
select PartitionColumnID,Name from PartitionedTable
GO

Well, I get an error that the second partition cannot be accessed, but if you look at the Results, the query returned 1 record!

I restored the Partition2 filegroup

USE master;
GO
RESTORE DATABASE [MyDatabase] 
FILEGROUP = 'Partition2'
FROM DISK = N'<BackupPath>MyDatabase.bak'
WITH NORECOVERY
GO
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY
GO

And executed the same query. I got again an error that Partiton3 filegroup was not accessible but the query returned 100 rows. This is correct because I know that there were 100 records in my first 2 partitions of the table.

Of course, if you’re using the correct where-clause and only query the ONLINE filegroups, you will not get any errors at all.

--Try to select the tables
use [MyDatabase]
GO
select PartitionColumnID,Name from PartitionedTable where PartitionColumnID<=100
GO

Finally, I also restored Partition3 and 4 and my table was completely available again.

Conclusion

My answer during my session was not correct. It is possible to restore a partitioned table in pieces and make part of the table available while the other filegroups are still in recovering state.
This can be really helpful to improve your RTO. Instead of restoring the complete table in 1 time, you can restore only the partitions that you really need to get your application back up and running.
The less important partitions, that for example only contain history data, can be restored in a later stage.

Well, as always, you’re never too old to learn something!

3 comments on “Piecemeal restore of a partitioned table
  1. Thanks, Pieter.
    This article really useful and interesting. I didn’t know before that it is possible to restore only the partitions that I really need instead of restoring the complete table in 1 time.
    Backup and restore process are one of the most important part in the life of every DBA, nowadays there are at least three way who to make database backups – T-SQL commands, SSMS and third party tools. What are the best way to make backups?

    • Hi Olek,

      It depends 😉
      Manual backups can be done with T-SQL, SSMS and a third party tool. Depends on your own preference I guess.
      Scheduling backups can be done by T-SQL Scripts, Maintenance plans or third party tools.
      A very common maintenance solution is Ola Hallengren’s SQL Server Maintenanc Solution
      So again, the best way of taking backups really depends on your environment (sometimes you *have* to use a third party tool) or on your personal preference.

      Regards
      Pieter

Leave a Reply

Your email address will not be published. Required fields are marked *