Restore the entire SQL Server database or only the error filegroup

Here's a concrete example: if you have a single problem file. This file has a 50MB size, and your entire database runs about billions of bytes, so it makes sense to recover a single failed file. One of the things that happens when a file or filegroup is on a separate drive, and the drive has a problem. Typically, simply restoring a single file or filegroup shortens the total stop time because it significantly reduces the total amount of data that needs to be recovered.

Now, why don't you choose to do this? There are some reasons for this:

You need to have a transaction log backup. If you want to recover a file or filegroup from a backup, you also need to restore the transaction log backups created with them, so that the entire database is in a consistent state. In SQL Server 2000 and 2005, you need to use the full Recovery or bulk-logged Recovery mode (that is, not simple Recovery) to make it possible. I should point out that the implementations of SQL Server did not do their best to determine the function of backing up a file or whether the filegroup has been modified since the last time. If it has not been changed, then the transaction record is not necessary. However, in general, a transaction log backup is required, and if you do not have a recovery or backup schedule for backup transactions, create one now.

Data inconsistencies between the tables in the file or file group that you want to recover, and the other tables in the database, are an issue to consider. If you have tables that depend on each other, and the tables are not stored in the same physical file or filegroup (which is sometimes unavoidable), simply restoring a file or filegroup may cause it to be out of sync with the rest of the database. For example, if you have a table that is associated with a join in another table, and the join uses a view and stored procedure, then just restoring one without restoring another may be problematic.

When you have only one filegroup in the database. What happens if all your data is stored in only one file or filegroup, and it is not a particularly large database? The effort to recover a file or filegroup at that time would be meaningless.

The primary reason for the selective recovery of files or filegroups is that the recovery of the database is so large that the restoration of the entire database is costly, making it possible to restore local corruption to the database. In a very small lightweight database, and in a nonproduction system, or when there is only one filegroup in the database, the selective recovery function does not make much sense, because there is no big difference between restoring the entire database and restoring individual files or filegroups.

I've found that most of the time when people want to use files or file groups to recover, they actually want to restore a particular form to a point in time before the state. This is not a feature of explicit support in SQL Server, but there is a way to do this if you don't mind the need to manually manage the inconsistencies that might arise as a result of this approach (just like the above). If you have a database backup at hand, you can simply restore that backup, just think of it as an instance of a different name for the same database. Next, scroll the database through the transaction log to the specified point, if necessary, and manually copy the current database to the target database.

I have experimented with this method several times myself, but only one table is not significantly associated with other tables in the same database. My example involves a chat site that contains a message board system. I have to frequently recover messages that were accidentally deleted on the message board, which are self-contained: the only joins from the data from the message form are external rather than internal. So I'm free to update the form because I know I'm not going to let that table go out of sync with the other tables.

In SQL Server 2000 and its higher version, when you do a restore operation you can use the partial clause to restore only the filegroup data that is needed. This is useful as a time and space-saving measure: You don't have to do a lot of work to recover all of your data, and you just need to operate on a table. And there is probably not enough room for a full recovery operation.