Piecemeal restore refers to restore just page,file or file group. If the SQL Server is Enterprise. SQL Server may do the online restore which means the database will be online and only the restoring file or file group is not accessible.
Lets Create new file group and add 1 file to the file group
Exam the file group status
Create the Table on the new file group
Backup the database
Insert some data
Backup the transaction log
Exam the file group size change
To simulate the file group corruption, we need to shutdown the database and delete the GROUP2file1.ndf then restart the SQL Server.
Upon the SQL Server start up, you will find out the Database is in the RECOVERY PENDING state, the error log will also indicate it can not find the database file.
Lets start the piece meal recovery
First, we need to backup the tail log
Check the Database status again, the status become “RESTORING”
Since the Primary file group is intact, Lets bring it up ASAP
Restore Primary file group , log 1 and the tail log
Check the status again, the database is online now, but if you try to select the TB1, you will get the error message regarding the file group is not online. The table in the primary file group will be accessible for user .
Lets restore the 2nd file group.
Now , you have fully usable database.
Performing Piecemeal Restores http://msdn.microsoft.com/en-us/library/ms177425.aspx
Perform Page restore http://msdn.microsoft.com/en-us/library/ms175168.aspx