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.
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!