Avoiding Query Errors with Partial Database Availability

Q: You previously blogged about using partial database availability for faster restores. I’m trying to use this method with our partitioned database, but I keep getting errors when running queries against the partitioned table. How can I work around them?

A: When using any kind of partitioning, you need to ensure that the queries you’re running attempt to access only the online portions of the database, otherwise they’ll fail. As an example in “Using Partial Database Availability for Targeted Restores,” I created a sales database with the main sales table partitioned into four file groups by SalesDate. I dropped the database and performed a partial restore of the primary file group and this year’s sales data using the code in Listing 1.

RESTORE DATABASE SalesDB    FILEGROUP = 'primary'FROM DISK = 'D:SQLskillsSalesDBBackup.bak'WITH PARTIAL, NORECOVERY;GORESTORE DATABASE SalesDB    FILEGROUP = 'SalesDBSalesDataPartition2010'FROM DISK = 'D:SQLskillsSalesDBBackup.bak'WITH NORECOVERY;GO-- Restore log backups-- Bring the database partially onlineRESTORE DATABASE SalesDB WITH RECOVERY;GO

Suppose I want to find the number of sales for a particular customer this year. If I assume that the query processor will automatically limit the query to the online portions of the table, I could use the following code:

SELECT COUNT (*) FROM SalesDB.dbo.SalesWHERE CustomerID = 1440;GO

However, the query processor determines that it would need to scan the entire table to satisfy the query, realizes that part of the table is offline, and throws the error shown in Web Figure 1.

Msg 679, Level 16, State 1, Line 1 One of the partitions of index 'SalesPK' for table 'dbo.Sales'(partition ID 72057594039631872) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

To avoid this error, you need to specifically limit the query to look only in the partitions that are online. This means you need to provide another search argument, and there needs to be an index on the table that lets the query processor match the search argument against one or more partitions. This process is called partition elimination.

In this case, I’ll add a date search argument, which matches against the cluster key I chose (and is also the partition key), using the following code:

SELECT COUNT (*) FROM SalesDB.dbo.SalesWHERE CustomerID = 1440AND SalesDate > '2010-01-01';GO

And that query works perfectly.