Restore only one datafile in SQL Server 2008 from partitioned database

Question

We have a partitioned database that I'd like to get copied over on a semi-regular basis to another server for testing updates. Since the partitioning is based on a date column, the most current data exists in one file.

Is it possible to restore just one datafile from the backup to the other server?

Complicating issue: we use Symantec's BackupExec. If this is possible using plain ol' backup from SQL Server, I can probably get that done, though.

Answer 1

You can do a piecemeal restore of this data file if:

• you are in full recovery mode (this is not clear in the question - I think there are many configuration options for BackupExec)
• you have more than one filegroup (this is not clear in the question)
• this data file is read/write and in its own filegroup (otherwise you will also need all of the other data files in this filegroup). I assume this is the case because, how else could you know that this partition exists only in this one file?
• you can restore the primary filegroup and then this filegroup (in other words, the rest of the data is NOT in the primary filegroup - also not clear in the question)
• you are on Enterprise Edition (again, unknown)

More details in the following articles on MSDN:

• Performing Piecemeal Restores
• Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)