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
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
1 Answer 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.
select * from yourtable where partitionid IN (1,2)
or whatnotselect * from yourtable where $partition.pf1(PartitionId) = 1