Both SQL Server 2000 and SQL Server 2005 allow you to restore whole databases and to selectively restore individual files or filegroups. This is useful if one particular file or filegroup has failed and you want to restore it without going through the hassle of restoring the entire database. It's especially useful if the file/filegroup in question isn't that large to begin with or is only a small part of a large database.
Here's a concrete example: if you have a single bad file that's only 50 MB, and your entire database runs to several dozen gigabytes, it makes more sense to restore the single bad file, if possible. One scenario where this sort of thing happens often is where the file or filegroup is on a separate drive and the drive fails. Usually, you'll incur less total downtime by restoring a single file/filegroup, since it cuts down significantly on the amount of data that has to be restored.
Now, why would you not want to do this? Here are a few reasons:
- You need to have transaction log backups. If you want to restore a file or filegroup from backups, you'll also need to restore the transaction log backups created with them so that the entire database can be brought to a consistent state. In SQL Server 2000 and 2005, you need to use the Full Recovery or Bulk-Logged Recovery modes (i.e., not Simple Recovery) to make this possible. I should point out that SQL Server does make a best effort to determine if a file or filegroup has been modified since the last backup. If it hasn't been, then the transaction logs aren't needed. But, on the whole, count on needing transaction log backups -- and if you don't already have a recovery or backup plan in place that does backs up transaction logs, set one up.
- Inconsistencies of data between the tables in that file or filegroup and the rest of the database may make it a bad idea. If you have tables that depend on each other, which aren't stored in the same physical file or filegroup (and sometimes that's unavoidable), restoring only one file or filegroup may cause it to fall out of sync with the rest of the database. For instance, if you have one table that's referenced to another table with a JOIN using a view or stored procedure, restoring one without also restoring the other could be problematic.
- You only have one filegroup in the database. If all of your data is stored in just one file or filegroup -- which happens if it's not a very large database to begin with -- then it makes no sense to attempt a file/filegroup restore.
The main reason for performing selective restores of files or filegroups is to make it possible to spot-recover damage to a database that is too big to restore wholesale. On smaller or less heavily-trafficked databases and on nonproduction systems or with databases that have only one filegroup, it's scarcely worth the effort to do a selective restore since it's often just as easy to restore the whole database at once.
I've found that most of the time when people want to perform a file/filegroup restore, they are really trying to retrieve a specific table as it was at an earlier point in time. This is not an explicitly supported feature in SQL Server, but there is a way to do it, provided you don't
mind manually managing any of the inconsistencies that might arise because of doing it this way (as cited in #2 above). If you have a full database backup handy, you can simply restore that backup as a differently named instance of the same database. Then, roll that database forward with the transaction logs to the point desired (if that's required), and manually copy the table in question to the target database.
I've done variations of this trick myself a few times, but only on a table I knew wasn't strongly interrelated with other tables in the same database. My example involves a chat site that also featured a message board system. I've often had to restore data accidentally deleted from a message board, which is (thankfully) fairly self-contained: The only JOINs made from the data in the message board table were outward, not inward. Therefore, I was comfortable with updating that table with impunity since I knew I wouldn't be leaving that table out of sync with anything else.
In SQL Server 2000 and higher, you can use the PARTIAL clause when you do a RESTORE so that only the filegroup with the needed data is restored. This is useful as both a time and space-saving measure: You don't have to tediously restore everything just to get at one table; and perhaps there simply is no space available to do a full restore.
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site WindowsInsider.com, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
|
