Wednesday, March 7, 2012

Restoring filegroups in 2005.

Howdy all. I want to be able to restore just 1 filegroup from a filegroup backup, without having to restore the log afterwords even if the data has changed post filegroup backup. While this would leave the data inconsistant, it may be desired at times. From BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm

************************************************** ******
If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:

The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).
************************************************** ******

But thats just it, I may not want to take this action. Maybe some data got deleted from a set of tables in a filegroup right after I took my last filegroup backup, so I just want to restore that filegroup back to that backup, but leave my other filegroups alone. Restoring the log brings the data back to a consistant state when the log was backed up, which may not be what I want.

Can this be accomplished?

TIA, cfrA very knowledgeable guru clarified that this would not be possible.|||Only way to do this would be to restore the entire database to the desired opint in time, then transfer the data from the one filegroup to the existing database. NOTE: This will likely fail any foreign key relationships among these tables, and could result in a pile of orphaned data.

No comments:

Post a Comment