Why restore a full SQL database vs. a failed filegroup?

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:

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery The keys to database backup protection for SQL Server Choosing a SQL Server disaster recovery solution Licensing a standby server for SQL Server replication Can I encrypt and restore a database backup in SQL Server 2005? SQL Server errors, failures and other problems fixed from the trenches Get SQL Server log shipping functionality without Enterprise Edition SQL Server 2008 backup compression pros and cons SQL Server backups using SAN database snapshots Tips for scheduling and testing SQL Server backups Code to restore SQL Server databases in VB.NET SQL Server Backup and Recovery Research SQL Server Management A first look at Microsoft SQL Server 2008 R2 Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Using Microsoft Hyper-V for SQL Server consolidation Scaling up vs. scaling out with SQL Server 2008 Migrating to SQL Server 2008 and leveraging new features Testing a SQL Server environment before an upgrade Does upgrading to SQL Server 2008 fit your business? Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary rollback  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.

[TABLE]

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.