0

I have created database with this command and add some testing data:

CREATE DATABASE [TestDBRestore]
ON PRIMARY 
 (NAME = N'TestDBRestore', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDBRestore.mdf'), 
 FILEGROUP [HISTORY] 
 (NAME = N'TestDBRestore_History', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDBRestore_History.ndf')
 LOG ON 
 (NAME = N'TestDBRestore_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDBRestore_log.ldf')
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
CREATE PARTITION FUNCTION pf1(INT)
AS RANGE LEFT FOR VALUES (1, 2, 3);
GO
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
TO ([PRIMARY], [PRIMARY], [HISTORY], [HISTORY]);
DROP TABLE IF EXISTS dbo.PartitionedTable
CREATE TABLE dbo.PartitionedTable
(
 PartitionId INT,
 Id INT,
 name VARCHAR(100)
) ON ps1(PartitionId)
INSERT INTO dbo.PartitionedTable VALUES (1, 1, 'PRIMARY')
INSERT INTO dbo.PartitionedTable VALUES (1, 2, 'PRIMARY')
INSERT INTO dbo.PartitionedTable VALUES (1, 3, 'PRIMARY')
INSERT INTO dbo.PartitionedTable VALUES (2, 1, 'PRIMARY')
INSERT INTO dbo.PartitionedTable VALUES (2, 2, 'PRIMARY')
INSERT INTO dbo.PartitionedTable VALUES (3, 1, 'HISTORY')
INSERT INTO dbo.PartitionedTable VALUES (3, 2, 'HISTORY')
INSERT INTO dbo.PartitionedTable VALUES (3, 3, 'HISTORY')
INSERT INTO dbo.PartitionedTable VALUES (4, 1, 'HISTORY')

After this I want to backup database, but only for FILEGROUP PRIMARY:

BACKUP DATABASE TestDBRestore
FILEGROUP = 'PRIMARY'
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDBRestore.bak'
WITH COPY_ONLY;

When I drop the database:

DROP DATABASE TestDBRestore

And the restore the database only with this filegroup in the new database:

RESTORE DATABASE TestDBRestore
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDBRestore.bak'
WITH REPLACE
Processed 552 pages for database 'TestDBRestore', file 'TestDBRestore' on file 1.
Processed 5 pages for database 'TestDBRestore', file 'TestDBRestore_log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 557 pages in 0.011 seconds (395.197 MB/sec).

When I now do a SELECT on the table

SELECT * FROM dbo.PartitionedTable

I get this message

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

So as I don't have the backup for HISTORY filegroup I would like to somehow remove this data and pick up into correct shape

I deleted the filegroup

ALTER DATABASE TestDBRestore
REMOVE FILEGROUP [HISTORY]

When I run this query it was changed from RECOVERY_PENDING into DEFUNCT

SELECT 
 f.name AS FileGroupName, 
 d.name AS FileName,
 d.state_desc AS State
FROM 
 sys.filegroups f
INNER JOIN 
 sys.database_files d ON f.data_space_id = d.data_space_id

FileGroup HISTORY DEFUNCT

But still when I do the SELECT * FROM dbo.PartitionedTable, I get the same error message.

Is it possible somehow correct the database and remove these partitions from the database which are in HISTORY filegroup?

In case it will be possible could be there some negative impact than only I lost my data from those partitions

asked Jun 16 at 13:16
4
  • Well, if you select *, it will try to access all partitions, shouldn't it have been: select * from yourtable where partitionid IN (1,2) or whatnot Commented Jun 16 at 13:25
  • I know about this, but i would like to remove somehow the partitionId IN (3,4) or the best would be to remove full filegroup HISTORY from the restored database Commented Jun 16 at 13:38
  • 1
    Not sure it's possible, i'd probably just restore the whole thing and then just switched out/truncated the history partitions to dev/null Commented Jun 16 at 13:46
  • What happens if you try select * from yourtable where $partition.pf1(PartitionId) = 1 Commented Jun 16 at 15:07

1 Answer 1

1

Is it possible somehow correct the database and remove these partitions from the database which are in HISTORY filegroup?

Your best bet is to create a new table and insert the data that is still available from whatever partitions are accessible into the new table. This would be best if completed in a new database.

In case it will be possible could be there some negative impact than only I lost my data from those partitions

The defunct filegroups will never go away, there may be deferred transactions against them. Your best option is to create a new database and move the data into the new one, then validate your data makes sense.

Given the scenario you're presenting is BCDR + Data Loss, this should be both acceptable and understandable that new items will need to be built and there will be a non-trivial amount of work involved. Layering other technologies can help, but I assume the scenario was specific for a reason.

answered Jun 16 at 21:46

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.