Partial Restore - Does it really need to start with restore of the PRIMARY FG?

Question

Partial Restore - Does it really need to start with restore of the PRIMARY FG?

winston Smith

SSCoach

Points: 19500
More actions
November 12, 2011 at 12:59 pm

#246828

I am playing with peacemeal restore.
I am trying to restore a readonly filegroup. If i restore it and recover immediately, everything works as expected and I can select from tables on that Filegroup.
Restore Database BackupTests Filegroup='FG3'
FROM DISK=N'C:\SQLServerFiles\BackupTests\backups\backuptests_Partial_All'
WITH RECOVERY
If i try to recover later it does not work, giving the following error
Restore Database BackupTests Filegroup='FG3'
FROM DISK=N'C:\SQLServerFiles\BackupTests\backups\backuptests_Partial_All'
WITH NORECOVERY
RESTORE Database BackupTests Filegroup='FG3' with RECOVERY
The file or filegroup "FG3" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.
Ive tried restoring the transaction log before recovery but it still does not work.
Any idea why this is?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Which statement fails? The restore of the filegroup or the restore of the DB with recovery?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

winston Smith SSCoach Points: 19500 More actions · Answer 2

of the second code block, this part fails with the error listed previously:

RESTORE Database BackupTests Filegroup='FG3' with RECOVERY

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What about just RESTORE DATABASE <dbname> WITH RECOVERY ?

I know that's what you run after a page restore if the last log restore isn't run WITH RECOVERY.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

winston Smith SSCoach Points: 19500 More actions · Answer 4

Yep, that worked, thanks Gail! fantastically helpful as always!