How to Backup and Restore Filestream Data

Question

I am trying to backup and restore my database which includes a filestream filegroup.

I backed up the database without the filestream filegroup and was interested to know what happens when you restore it. Inside the restore dialog nothing appeared in the 'Select backup sets to restore' list. This is not exactly surprising.

Then, I backed up the filestream data into a separate .bak file.

When I tried to restore them both together I got this message,

The media loaded on 'backup file' is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.

• What does that mean?
• How can I backup filestream and database separately?

Answer 1

You have to restore the database first with the norecovery switch, then restore the other file group. Then roll the transaction log forward then bring the database online.

This requires a minimum of three RESTORE DATABASE commands to do.