One of the biggest benefits of using the file and filegroup backup strategy is the ability to recover quickly from isolated disk failures. If only one file or filegroup is damaged, you can easily recover by restoring only the damaged files or filegroups.
Using the case study from the main article, imagine that only file PubsTestRWFile2 has been damaged. In this case, the database is suspect but most of the database files are still accessible. To recover up to the time of the failure, you proceed very much like you do with the whole database recovery, except you need to look at only one file: the damaged PubsTestRWFile2.
However, before you proceed, be aware of a problem that exists with read-only filegroups and isolated file and filegroup failures. During recovery, transaction log restores will fail if a filegroup (even one that isn't being restored) had the READONLY property set when the log being restored was backed up. The failure occurs only when you're attempting to restore a subset of the database (as in the case of isolated failures); it doesn't occur when you're recovering the whole database. I recommend that you avoid this problem by using filegroups for read-only purposes— perhaps even logically setting up a filegroup solely for read-only data.
I don't recommend enforcing read-only activity by setting the READONLY property. As of SQL Server 2000 Service Pack 2 (SP2), you can't restore transaction logs after an isolated failure occurs and only file or filegroup backups have been applied. One workaround (if you're in the middle of recovery and discover the problem) is to restore the entire database as in the main article. This solution can add a tremendous amount of downtime, but you won't have data loss and you'll be able to recover up to the minute of failure. Or, you can avoid this bug entirely by not using the READONLY attribute for filegroups.
If you want to experiment with this scenario, you can use the PubsTest case study to set the READONLY property for the ROFG filegroup. A section in the FileFilegroupStrategiesCaseStudy.sql downloadable script has this code commented out. Search for "MODIFY FILEGROUP ROFG READONLY" and remove the comments on this ALTER DATABASE statement. Next, run the entire script to recreate the PubsTest database. Finally, follow the strategy detailed here to recover from isolated disk failure. The script that Web Listing A shows will successfully restore the NewPubsTest database after an isolated failure only if the READONLY attribute has never been used for any filegroups within the database.
So, to recover up to the minute in a case of isolated failure in the PubsTestRWFile2 file, you need to start by recovering that file. In the example backups, you backed up the entire filegroup of which PubsTestRWFile2 is a member, so you can restore just this file from this filegroup. The last full filegroup backup was number 3. The syntax to restore this full file from the full filegroup backup is
RESTORE DATABASE PubsTest FILE = 'PubsTestRWFile2' FROM DISK = N'C:\Program FilesMicrosoft SQL Server\MSSQL\BACKUP\pubsbackup.dat' WITH FILE = 3, -- This is the backup number by position. NORECOVERY
The next step is to restore the latest differential backup of this file. Again, you backed up this file only as part of the RWFG filegroup. You can use the last differential backup of RWFG (number 11) to get the differential backup of this file:
RESTORE DATABASE PubsTest FILE = 'PubsTestRWFile2' FROM DISK = N'C:\Program FilesMicrosoft SQL Server\MSSQL\BACKUP\pubsbackup.dat' WITH FILE = 11, -- This is the backup number by position. NORECOVERY
Finally, you must apply the correct sequence of transaction log backups to roll forward the database to the time of the failure. Using the msdb query from the main article to determine the minimum effective log sequence number (LSN), you'll find that the first log backup you need to apply is number 12. In the event of an isolated failure in a large database, the file and filegroup backup strategy ensures rapid recovery and minimal downtime.
