Unable to restore database with datetime2(0) in Partition Function

I have a SQL-Server database with large table that partition by a datetime2(2) column.
Some (old) filegroups marked as READ_ONLY.

Periodically I make the backup with READ_WRITE_FILEGROUPS option.
I can successfully recover data from a READ WRITE partition.

However, I cannot read the recovered data, I get the following error:

One of the partitions of index 'pk_myorderid' for table 'dbo.myorders'(partition ID 72057594043105280) resides on a filegroup ("YEAR2021") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

If I change the data type to DATETIME or datetime2(7), no error occurs (of course if I request data from an restored range)

Apart from the this issue, everything else is working properly.

I created a test script to illustrate the problem. This script creates a test database, populates the table, backs up and restores the database.

If, in this script, change datetime2(7) with datetime2(2) , the data becomes inaccessible after recovery.

Test script:

```
USE MASTER

-- Reset environment
IF DB_ID('PartialDatabase') IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'PartialDatabase'
ALTER DATABASE PartialDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PartialDatabase
END
GO
IF DB_ID('PartialDatabase_Recovery') IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'PartialDatabase_Recovery'
ALTER DATABASE PartialDatabase_Recovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PartialDatabase_Recovery
END
GO

-- Create database
CREATE DATABASE [PartialDatabase] ON PRIMARY (
NAME = N'PartialDatabase'
, FILENAME = N'C:\SQLData\PartialDatabase_primary.mdf'
, SIZE = 10240KB , FILEGROWTH = 10240KB )

, FILEGROUP [YEAR2021]
( NAME = N'PartialDatabase_YEAR2021'
, FILENAME = N'C:\SQLData\PartialDatabase_YEAR2021.ndf'
, SIZE = 10240KB , FILEGROWTH = 10240KB )

, FILEGROUP [YEAR2022]
(

You're not getting dynamic partition elimination when the data types don't match and there is a risk of truncation, as I explained a decade ago now in my article Why Doesn’t Partition Elimination Work? (see below).

As to the behaviour itself, I wrote at the time:

I’m in two minds whether the SQL Server 2008+ behaviour is a bug, an oversight, or an undesirable consequence of fixing something else…so I opened a Connect item for it. The report was closed as Won’t Fix:

The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

Explanation

Your query:

SELECT
    M.myorder_id, 
    M.myorder_date, 
    M.myorder_details 
FROM dbo.myorders AS M
WHERE 
    M.myorder_date >= '2023-01-01';

As written, the query qualifies for simple parameterization. The string literal is given an inferred type of varchar(8000) (see my article series Simple Parameterization and Trivial Plans for full details) and is then implicitly converted to datetime2(7) at runtime for comparison with the datetime(2) column:

The risk of truncation means no dynamic partition elimination using RangePartitionNew is applied as described in my article.

When the query doesn't qualify for simple parameterization, the string is still implicitly converted to datetime2(7) but this is harder to see because constant folding is applied instead of performing a runtime conversion (notice the seven fractional digits at the end). Still no partition elimination though due to risk of truncation:

SELECT
    M.myorder_id, 
    M.myorder_date, 
    M.myorder_details 
FROM dbo.myorders AS M
WHERE 
    M.myorder_date >= '2023-01-01'
OPTION (KEEP PLAN); -- hint prevents simple param

If we provide a properly typed value using a parameter or explicit conversion, all these issues are avoided and partition elimination occurs, with or without simple parameterization:

-- Simple param allowed
SELECT
    M.myorder_id, 
    M.myorder_date, 
    M.myorder_details 
FROM dbo.myorders AS M
WHERE 
    M.myorder_date >= CONVERT(datetime2(2), '2023-01-01', 120)

Notice the seek predicate applying dynamic elimination there.

The plan is even simpler without simple parameterization because static partition elimination can be used after constant folding:

Notice the two fractional digits in the literal and direct seek to the correct partition id.

Naturally, any plan that avoids accessing the offline partitions avoids the error. To be clear, the problem isn't that the plans without partition elimination aren't optimal, the problem is those plans have to access partitions which are not available.

This is one reason why I encourage people so strongly to be aware of data types when coding. A string is not a date/time and the potential gotchas are numerous.
Workarounds

Many workarounds are possible, but all will require some sort of change. One simple one is to add an OPTION (RECOMPILE) hint to the query either directly or via a plan guide.

This allows the parameter embedding option where the literal is constant folded to the correct type resulting in static partition elimination. The downside is increased compilation and no plan reuse.

Confirmed as effective on SQL Server 2022 with recovery pending filegroups:

SELECT 
    DF.data_space_id,
    DF.[type_desc],
    DF.[name],
    DF.state_desc
FROM sys.database_files AS DF
ORDER BY
    DF.data_space_id;

data_space_id
type_desc
name
state_desc

0
LOG
Test_log
ONLINE

1
ROWS
Test
ONLINE

2
ROWS
Test_YEAR2021
RECOVERY_PENDING

3
ROWS
Test_YEAR2022
RECOVERY_PENDING

4
ROWS
Test_YEAR2023
ONLINE

SELECT
    M.myorder_id, 
    M.myorder_date, 
    M.myorder_details 
FROM dbo.myorders AS M
WHERE 
    M.myorder_date >= '2023-01-01'
OPTION (RECOMPILE);

myorder_id
myorder_date
myorder_details

4
2023-01-01 10:00:00.00
year - 2023

Another option is to be explicit about the partition being accessed using $PARTITION:

SELECT
    M.myorder_id, 
    M.myorder_date, 
    M.myorder_details 
FROM dbo.myorders AS M
WHERE 
    M.myorder_date >= '2023-01-01'
    AND $PARTITION.pf_myorders_date(M.myorder_date) = 
        $PARTITION.pf_myorders_date('2023-01-01');

You could wrap that extra logic in an inline function or view and get the users to access those instead of the base tables, or even create synonyms to the new objects in another database so the queries don't have to be changed. Which creative option works best for you depends on the specifics of your environment and your working constraints.
Summary

Ultimately, accessing a database with some offline filegroups is a bit hit-or-miss. That's pretty much by design. The assumption is that a piecemeal restore will eventually result in a completely restored database. Queries run before the process is complete may fail if they try to access a filegroup that hasn't been recovere

StackExchange Database Administrators Q#325322, answer score: 5