Partial restore question

Question

Post reply

Partial restore question

JohnMarks

Ten Centuries

Points: 1253
More actions
May 22, 2014 at 7:01 pm

#289149

We have a VLDB that needs to be restored from a production to stage and dev servers on a monthly basis. It contains many years of data that after the current year does not change. Our goal is to reduce the back up and restore time. We decided to use file groups and partition the largest tables. The file groups are:
primary - system tables
rw - current data and default file group
archive - data older than a year - read only
The large tables are partitioned so that current data sits on the rw file group and anything older than 1 year sits on the archive file group. The indexes are partitioned aligned.
It was our understanding & hope hat we could do a partial backup of the read/write file groups and on a monthly basis do a partial restore of those file groups to the stage/dev environments. This does work however the archive filegroup is left in a recovering state. So any general select queries that hit tables that are partitioned over the two file groups fail with the following error
Msg 679, Level 16, State 1, Line 3
One of the partitions of index 'PK_X' for table 'dbo.X'(partition ID 72057594038779904) resides on a filegroup ("FG_X_Archive") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.
Is the problem we are running into a limitation of the filegroups and partial restore/backup methodology or are we missing something?
If we add a SARG to the queries that limit the results to the current year they work however we do want to allow read access to the data on the archive filegroup - just not have to restore it every month.
TIA

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

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

Have you tried RESTORE DATABASE { database_name } <filegroup> [ ,...n ] WITH RECOVERY ?

Recover a Database Without Restoring Data (Transact-SQL)

JohnMarks Ten Centuries Points: 1253 More actions · Answer 2

After the restore of the read write filegroups the read only file groups have a state of RECOVERY_PENDING. If I run

RESTORE DATABASE X FILEGROUP='FG_readonly'

WITH RECOVERY;

The read only files have a state of RESTORING and remain inaccessible.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

Looks to me like the order goes Piecemeal Restore of RW then online restore of read only. You also need to be running Enterprise:

https://www.simple-talk.com/sql/backup-and-recovery/partial-backup-and-restore/

http://msdn.microsoft.com/en-us/library/ms177425.aspx

http://msdn.microsoft.com/en-us/library/ms175541.aspx

JohnMarks Ten Centuries Points: 1253 More actions · Answer 4

I think we ended up causing our own problem. We were attempting to restore to a new database that we had set up off a restore of the primary VLDB using a backup that was taken prior to the read only file groups being set to read only. Once we had done the restore using a backup that had the file groups set to read only subsequent partial restores using r/w filegroup backups worked and we were able to bring the read only file groups online using the command Jon suggested.

If anyone runs into this problem in the future we found that the easiest way restore a database using a mixture of read/write and read only file groups is to either take a full or partial backup of the r/w filegroups and restore them to a new system before creating a database. This of course assumes that that backups are done after the read only property is set on the appropriate file groups. Once the primary file group is restored then subsequent partial restores of r/w file groups or ro filegroups can be done.