I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables under this filegroup. Before these various bad changes were made I took a backup of the xyz.ndf file using SSMS –> Backup Component –> Files and Filegroups.
I have an urgent need to restore only xyz.ndf file using the backup without impacting the operation of the remaining datafiles
For this I ran the query below
RESTORE DATABASE AAAA FILEGROUP='XYZ' FROM dISK='C:\MyLoc\XYZ_Backup.bak'
WITH RECOVERY
I get the message below.. and there were no log backups after my original backup. What should i do to fix this?
Processed 48 pages for database 'AAAA', file 'xyz' on file 1. The roll
forward start point is now at log sequence number (LSN)
40000000032600001. Additional roll forward past LSN 40000000034300001 is required to complete the restore sequence. RESTORE DATABASE …
FILE= successfully processed 48 pages in 0.054 seconds (6.944
MB/sec).
Best Answer
The only way to restore a single file group is to run in all the logs so it's up to date with the rest of the files. This would of course run in the bad data modification you're trying to prevent but it's necessary to ensure consistency throughout the database. You'll need to restore it to another location and move the data over.
http://msdn.microsoft.com/en-us/library/ms178099%28v=SQL.105%29.aspx