Backup and Restore FILESTREAM-Enabled Database

Backup and Restore FILESTREAM-Enabled Database
4.1 (82.86%) 7 votes

In my previous articles, I explained how to create and configure the FILESTREAM feature in SQL server instance. Moreover, I demonstrated how to create a table that has a FILESTREAM column and hot to insert and delete the data from it.

In this article, I am going to explain how to backup and restore the FILESTREAM-enabled dataase. Moreover, I am going to demonstrate how to restore FILESTREAM filegroup without making database offline.

As I explained in my previous articles, when we enable FILESTREAM on SQL Server instance, we need to create a FILESTREAM container that has the FILESTREAM filegroup. When we backup the FILESTREAM-enabled database, backup of the FILESTREAM filegroup will be included into the backup set. When we restore the database, SQL Server will restore the database and the FILESTREAM container and files within it.

When we back up a FILESTREAM-enabled database, it will:

  • Backup all available data files of the database.
  • Backup the FILESTREAM filegroup and files within it.
  • Backup T-Log.

SQL Server gives the flexibility to take backup of the FILESTREAM container only. If files within the FILESTREAM container get corrupt, we do not need to recover the entire database. We can restore just the FILESTREAM filegroup.

In this demo, I am going to:

  • Explain how to take a full backup of the FS database and backup only the FILESTREAM container.
  • Explain how to restore the FILESTREAM-enabled database.
  • How to restore the FILESTREAM container online and offline. Note: SQL Server Enterprise edition and developer edition supports ONLINE restore.

Demo Setup:

In this demo, I am going to use:

  1. Database: SQL Server 2017
  2. Software: SQL Server management studio.

Backing Up FILESTREAM-Enabled Database

To demonstrate the backup process, I have created a FILESTREAM-enabled database named FileStream_Demo. It has a FILESTREAM table named Document_Content.

Full database backup

Backing up a FILESTREAM-enabled database is a straightforward process. To generate a full backup of it, execute the following T-SQL script.

The following is the backup log generated by execution of the above backup command:

As I mentioned at the beginning of the article, first, SQL server takes backup of the Primary datafile then secondary data files and lastly, Transaction logs. As you can see in the backup log, Firstly, SQL server backup Primary datafile then FILESTREAM filegroup and data associated with it and finally transaction logs.

Backup of FILESTREAM container

As I mentioned at the beginning of the article, we can also generate a backup of the FILESTREAM container. To create a backup of the FILESTREAM container, execute the following T-SQL script.

Restore FILESTREAM-Enabled Database

When we restore the FILESTREAM database, SQL restores FileStream container along with all files within the FILESTREAM container.

To restore the database, perform the following tasks:

  1. In SSMS, right-click the database and select Restore Database.
  2. In the Restore dialog box, select Device and click Browse. Another dialog box will open, In the dialog box, click  Add.
  3. In the Locate Backup File dialog box, navigate through the directory structure, click a proper backup and click OK.
  4. Once the backup information loads in the Backup sets to restore grid view, click OK to start to restore the process.

Alternatively, you can restore a database by executing the following command:

FILESTREAM-Enabled database recovery scenario

The FILESTREAM filegroup restores process like the process of restore filegroups.

To generate the restore scenario, create a FILESTREAM-enabled database named FileStream-Demo. The database has a FILESTREAM table named Document_Content. Insert some random data and files in the Document_Content table.

Execute the following query to populate details of files inserted in the table.

The output is as follows:

The following is a screenshot of the FILESTREAM container:

Firstly, generate a full backup of the database. For this, execute the following command.

Secondly, generate a FILEGROUP backup of the FILESTREAM filegroup named Dummy-Document by executing the following command:

To generate the FILESTREAM corruption, delete some files from the FILESTREAM container. Once those files get deleted, try to retrieve data from “Document_Content” by executing the following command:

You will get the following error:

See the following screenshot:

Now, we need to restore the FILESTREAM container to fix this error. We generated a full backup and backup of the Dummy-document filegroup.

We can restore the entire FILESTREAM container by restoring the FILESTREAM filegroup. I am going to show:

  1. Offline restore of FILESTREAM filegroup.
  2. Online restore of FILESTREAM filegroup.

Offline restore of FILESTREAM container Filegroup

As I had deleted files from FILESTREAM container, we do not need to restore the entire database. Hence instead of restoring the entire database, we will restore the only filegroup. To do that, first, generate a Tail-Log backup to capture the data changes which were not backed up. The Tail-log backup must be taken using the NORECOVERY option to bring database in restoring state, and that gives an ability to apply backups on the database. To do that, execute the following query:

Once the Tail-log backup is generated, the database will be in restoring mode. Now, we can apply the FILEGROUP backup on a database with the NORECOVERY option. For this, execute the following command:

Now apply the Tail-log backup with the RECOVERY option. For this, execute the following command:

Once the backup restores, the database will be online, and all files will be restored in the FILESTREAM container. To verify it, execute the following command:

The output of the above query is as follows:

Online restore of FILESTREAM filegroup

Using SQL server enterprise edition, we can restore the backup when the database is online. For example, if a file F1 of secondary filegroup FG-1 is corrupted, then we can restore file F1 while the database remains online. Restore sequence of offline restore, and online restore are same.

As mentioned above, to perform an online restore of FILESTREAM filegroup, make the Dummy-Document datafile offline. For this, execute the following command.

To verify the status of the file, execute the following query:

The output is as follows:

We have already backed up the Dummy-document filegroup. Hence, once the data file is offline, restore the FILEGROUP backup on a database with the NORECOVERY option. For this, execute the following command:

Now, take a Log backup of the database to make sure the point at which datafile went offline is captured. For this, execute the following command:

Execute the following command to restore the last T-Log backup.

Once the Log backup restores, all the files in the FILESTREAM container will be restored, and filegroup will be online. To verify that, execute the following query:

The output is as follows:

Once the backup is restored, the database will be online and all files will be restored in the FILESTREAM container. To verify it, execute the following command:

The output is as follows:

Summary

In this article, I  explained:

  1. How to take backup and restore the FILESTREAM-enabled database and FILESTREAM filegroup.
  2. How to restore FILESTREAM filegroup online and offline.
Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay