Query a SQL Server Database while it is still restoring

PARTITION is an SQL Server Enterprise Edition feature that can be used to spread data over different partitions, which can be put into different filegroups.
When you think about Backup and Restore strategies, a neat thing is that secondary filegroups can be set read-only and then don't have to be backuped permanently (simply because the data keeps unchanged). Thus, your backup runs faster, because you only backup readwrite filegroups, and thus the backup doesn't effect your database so much. Furthermore, you can do piecemeal restore after a disaster, which means that you can make the database available already after restoring the primary filegroup. So the users can query the restored part of the database until you restored all the filegroups of the database.

The following example shows how queries to the database work, if one filegroup isn't restored.

In the example, there are two tables with ID and CreateDate. The second table has a foreign key to the ID of the first table. The tables are partitioned with column ID at value 3, so that ID 1 and 2 of each table reside in partition 1, and ID 3 and 4 in partition 2. The first partition is on filegroup 1, the other on filegroup 2. A join query an the two tables returns this result:









In case of a crash, we would restore first the primary filegroup. If we run the same query against all the data in the tables, we get an error because not all data is available:
Msg 679, Level 16, State 1, Line 8
One of the partitions of index 'PK_Transfile' for table 'dbo.Transfile'(partition ID 72057594039238656) resides on a filegroup ("SECONDARY") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

In order to make your query run, you have to restrict it to the restored partitions. In this case, you have to restrict the query so that the ID columns of the tables are smaller or equal to 2. Since the partition column is known, the SQL processor knows that it only has to query the first partition and, thus, only enters the first, restored filegroup. The result then is not complete, but it doesn't return an error:







This way, you can use PARTITION to get your system run much faster, you can shorten your backup time and you can ease restore.