What Is Piecemeal Restore?

Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?

Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.