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

Question

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]
(   NAME = N'PartialDatabase_YEAR2022'
  , FILENAME = N'C:\SQLData\PartialDatabase_YEAR2022.ndf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )

  , FILEGROUP [YEAR2023]
(   NAME = N'PartialDatabase_YEAR2023'
  , FILENAME = N'C:\SQLData\PartialDatabase_YEAR2023.ndf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB ) 
 
  LOG ON
(   NAME = N'PartialDatabase_log'
  , FILENAME = N'C:\SQLData\PartialDatabase_log.ldf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )
GO

 
ALTER DATABASE [PartialDatabase] SET RECOVERY SIMPLE
GO
 
-- create partition FUNCTION & SCHEME
USE [PartialDatabase]
GO
CREATE PARTITION FUNCTION pf_myorders_date ([datetime2](7)) /*([datetime2](2))*/
     AS RANGE RIGHT FOR VALUES
    ('2022-01-01 00:00:00', 
    '2023-01-01 00:00:00')

CREATE PARTITION SCHEME ps_myorders_date AS PARTITION pf_myorders_date 
    TO ([YEAR2021], [YEAR2022],[YEAR2023])
GO
 
-- Create table
CREATE TABLE dbo.myorders
(
       myorder_id      INT                
     , myorder_date    [datetime2](7)                       /*([datetime2](2))*/
     , myorder_details NVARCHAR(4000)     
     , CONSTRAINT pk_myorderid PRIMARY KEY CLUSTERED (myorder_id, myorder_date)
)
ON ps_myorders_date(myorder_date)
GO

/*
Insert rows to all partitions
*/
INSERT INTO [PartialDatabase].dbo.myorders SELECT 1, '2020-01-01 10:00:00', 'year - 2020'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 2, '2021-01-01 10:00:00', 'year - 2021'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 3, '2022-01-01 10:00:00', 'year - 2022'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 4, '2023-01-01 10:00:00', 'year - 2023'



GO


-- Mark old partitions as readonly
alter database [PartialDatabase] set SINGLE_USER with rollback immediate
GO

ALTER DATABASE [PartialDatabase] MODIFY FILEGROUP [YEAR2021] READONLY
ALTER DATABASE [PartialDatabase] MODIFY FILEGROUP [YEAR2022] READONLY

alter database [PartialDatabase] set MULTI_USER with rollback immediate
GO


-- Backup READ_WRITE filegroups
BACKUP DATABASE PartialDatabase 
READ_WRITE_FILEGROUPS
TO DISK = N'C:\SQLData\PartialDatabase_2023.bak'
WITH INIT, STATS = 10;
GO


--Restore READ_WRITE filegroups
RESTORE DATABASE [PartialDatabase_Recovery] 
READ_WRITE_FILEGROUPS
FROM DISK = N'C:\SQLData\PartialDatabase_2023.bak'
WITH PARTIAL, RECOVERY, 
MOVE 'PartialDatabase' TO 'C:\SQLData\PartialDatabase_Recovery_Primary.mdf',
MOVE 'PartialDatabase_YEAR2021' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2021.ndf',
MOVE 'PartialDatabase_YEAR2022' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2022.ndf',
MOVE 'PartialDatabase_YEAR2023' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2023.ndf',
MOVE 'PartialDatabase_log' TO 'C:\SQLData\PartialDatabase_Recovery_log.ldf'
GO

-- Request data located in the READ_WRITE filegroup 
SELECT [myorder_id]
      ,[myorder_date]
      ,[myorder_details]
  FROM [PartialDatabase_Recovery].[dbo].[myorders]
WHERE [myorder_date] >= '2023-01-01'  

Of course, using the DATETIME type is a working solution, but what is the problem with datetime2(2)?

I would prefer to solve the problem only by SQL database modifications, without affecting the software code base. Some applications are using constants in actual queries (not parameters).

I am using SQL Server 2017 14.0.1000.169 (X64).

Answer 1

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 recovered yet.

If you never want the remaining filegroups restored, you'll need to do some work to convert the partially restored database into a fully functional one.

For example, you might create an empty partitioned copy of the target table, SWITCH the desired filegroups into it, rename the old table, then finally rename the new objects to match the original. It can be quite a bit of work, especially if foreign key relationships need to be recreated, but it's the most comprehensive solution. Exactly how you do this depends on how you want the new table to look (e.g. partitioned or not), but this is an example script that results in a new partitioned table:

USE Test_Recovery;

CREATE PARTITION FUNCTION pf_myorders_date2 ([datetime2](2))
    AS RANGE RIGHT 
    FOR VALUES
    (
        '2023-01-01 00:00:00'
    );

CREATE PARTITION SCHEME ps_myorders_date2 
    AS PARTITION pf_myorders_date2 
    TO ([PRIMARY], [YEAR2023]);

IF OBJECT_ID(N'dbo.myorders_defunct', 'U') IS NULL
BEGIN TRY
    BEGIN TRANSACTION;

    -- Will become the new table
    CREATE TABLE dbo.myorders_switch
    (
        myorder_id integer NOT NULL,
        myorder_date datetime2(2) NOT NULL,
        myorder_details nvarchar(4000) NULL, 
    
        CONSTRAINT pk_myorderid_switch 
            PRIMARY KEY CLUSTERED (myorder_id, myorder_date)
    )
    ON ps_myorders_date2(myorder_date);

    -- Move the data
    ALTER TABLE dbo.myorders
        SWITCH PARTITION 3
        TO dbo.myorders_switch
            PARTITION 2;

    -- Rename the old table as defunct (cannot drop)
    EXECUTE sys.sp_rename
        @objname = N'dbo.myorders',
        @newname = N'myorders_defunct',
        @objtype = 'OBJECT';

    -- Rename the old primary key
    EXECUTE sys.sp_rename
        @objname = N'pk_myorderid',
        @newname = N'pk_myorderid_defunct',
        @objtype = 'OBJECT';

    -- Rename the switch table
    EXECUTE sys.sp_rename
        @objname = N'dbo.myorders_switch',
        @newname = N'myorders',
        @objtype = 'OBJECT';

    -- Rename the switch primary key
    EXECUTE sys.sp_rename
        @objname = N'pk_myorderid_switch',
        @newname = N'pk_myorderid',
        @objtype = 'OBJECT';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

It's not perfect, because you still end up with RECOVERY_PENDING filegroups and empty tables named *_defunct, but the new tables won't have any of the original problems. A perfect result would require creating a new database and bulk copying the rows.

By the way, you're using the 2017 RTM release. There have been 31 Cumulative Updates since then. You might like to patch it up to date.