Sample Header Ad - 728x90

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

4 votes
1 answer
273 views
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).
Asked by Oleg (43 rep)
Mar 28, 2023, 12:10 PM
Last activity: Mar 30, 2023, 07:37 AM