Performing a Partial Database Restore

The PARTIAL option is new to SQL Server 2000. The partial clause is specified in conjunction with a restore from a full backup, a file or filegroup clause, and usually a move option. You can also choose to restore additional differential or log backups . A partial restore differs from file or filegroup restore in that it always restores only the primary filegroup and the filegroup specified in the filegroup clause (if a file is specified all files in its filegroup are restored). All other filegroups are marked unavailable. It is for this reason that this restore method is used in conjunction with the MOVE option. As only a subset of the database is restored, you generally are not going to want to restore it on top of your existing database.

Why would you want to restore only part of your database? Partial restore is primarily used for object recovery, not media recovery. Picture this scenario: Your database is ticking along just fine, when a developer, testing a new application, drops the customer table. Rather than shut everything down and do a point-in-time recovery to just before the table was dropped, you could do a partial recovery. In the RESTORE DATABASE statement, specify a different database name, the name of the filegroup the customer table was on, the PARTIAL option, and use the MOVE option to restore the files to a new location. Now restore the logs to a point in time when the customer table still existed. Transfer the customer table from this temporary database back to the production database and you are back in business. Now you have only one thing left to do. Go have a word with that developer!