Sql-server – SQL server restore readonly file group without backup from .ndf file

recoveryrestoresql serversql-server-2012

We have SQl Server database with ReadOnly file group. Common file groups except readonly are backuped regulary.

We restore the database from backup but readonly file group has not restored, its *.ndf file is not corrupted. Is it posiible to attach this file and reset file status RECOVERY_PENDING?

The example script:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1', FILENAME='D:\data\TestDB_Part1.mdf',
SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2', FILENAME ='D:\data\TestDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part3
(NAME = 'TestDB_Part3', FILENAME ='D:\data\TestDB_Part3.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part4
(NAME = 'TestDB_Part4', FILENAME ='D:\data\TestDB_Part4.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part5
(NAME = 'TestDB_Part5', FILENAME ='D:\data\TestDB_Part5.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );
GO
USE TestDB;
GO

CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10,20,30,40);
GO



USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);
GO

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (32,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (42,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (52,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3
VALUES (22,GETDATE());
GO

--- Step 7 : Test Data from TestTable
SELECT * FROM TestTable;
GO

--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY
GO


-- Backup rw and ro separately
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW.bak'
WITH NOFORMAT, INIT, 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- list backup files
RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RW.bak'


RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW.bak'  WITH  FILE = 1, 
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.ndf', 
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.ndf', 
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.ndf', 
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.ndf', 
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.ndf', 
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO

Best Answer

note that when you ran the RESTORE FILELISTONLY look at the column IsPresent and for the readonly file it shows 0 this mean that this file does not exists in the backupset.

anyway this file cannot be restored from that backup set because it does not exists there, so you must take it from a full backup (or just a file backup)

the only option to reset the "Archive_Pending" is by restore that element (either file group, file or page) and make it consistent with the database LSN (log sequence number)

the term attache is only for new attached databases and cannot be used with files.

so you can take it from full backup or from file backup (any backup that this file is "IsPresent" = 1 )

to continue your DEMO SCRIPT

-- take backup of that RO file group
BACKUP DATABASE [TestDB] 
FILEGROUP = N'TestDB_Part2' 
TO  DISK = N'D:\data\File2Backup.bak' ;
GO


-- restore the RO file / FG
RESTORE DATABASE [TestDB2] FILE = N'TestDB_Part2' FROM  DISK =     N'D:\Data\File2Backup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

and thats it.... your DB is up and running

I hope this has been informative for you, please let me know if you have any more questions.

Related Question