Backup and Restore FILESTREAM-Enabled Database

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 database. 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.

BACKUP DATABASE [FileStream_Demo] TO  DISK = N'E:\Backups\FileStream_Demo.bak'
 WITH NOFORMAT, NOINIT,  NAME = N'FileStream_Demo-Full Database Backup', 
 SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

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

/*Begin Backup DataFile*/

Processed 568 pages for database 'FileStream_Demo', file 'FileStream_Demo' on file 1.

/*Begin backup of FILESTREAM container*/

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 111106 pages for database 'FileStream_Demo', file 'Dummy-Documents' on file 1.

/*Begin backup of FILESTREAM container*/

Processed 4 pages for database 'FileStream_Demo', file 'FileStream_Demo_log' on file 1.
100 percent processed.

BACKUP DATABASE successfully processed 111677 pages in 18.410 seconds (47.391 MB/sec).

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.

BACKUP DATABASE [FileStream_Demo] FILEGROUP = N'Dummy-Documents' TO  DISK = N'E:\Backups\FS_Container.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'FileStream_Demo-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

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:

USE [master]
RESTORE DATABASE [FileStream_Demo] FROM  DISK = N'E:\Backups\FileStream_Demo.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

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.

SELECT  
RootDirectory,  
FileName,  
FileAttribute,   
FileCreateDate,   
FileSize,  
FileStreamCol.PathName() AS FilePath FROM Document_Content  order by filesize desc

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.

BACKUP DATABASE [FileStream_Demo] TO DISK = N'E:\Backups\Full_FileStream_Demo_20180810.bak' WITH NOFORMAT, NOINIT,NAME = N'FileStream_Demo-Full Database Backup'

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

BACKUP DATABASE [FileStream_Demo] FILEGROUP = N'Dummy-Documents' TO  DISK = N'E:\Backups\FileStream_Filegroup_Demo.bak' 
WITH NOFORMAT, NOINIT, NAME = N'FileStream_Demo-Full FILEGROUP Backup'

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:

Use FileStream_Demo
Go
select * from Document_Content

You will get the following error:

Msg 233, Level 20, State 0, Line 122
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, 
error: 0 - No process is on the other end of the pipe.)

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:

backup log [FileStream_Demo] to disk ='E:\Backups\FileStream_Filegroup_Demo_Log_1.trn' With NORECOVERY

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:

use master
go
RESTORE DATABASE [FileStream_Demo] FILE='Dummy-Documents' FROM DISK = N'E:\Backups\FileStream_Filegroup_Demo.bak' WITH NORECOVERY,REPLACE;

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

RESTORE LOG [FileStream_Demo] FROM  DISK = N'E:\Backups\FileStream_Filegroup_Demo_Log_1.trn'

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:

SELECT  
RootDirectory,  
FileName,  
FileAttribute,   
FileCreateDate,   
FileSize,  
FileStreamCol.PathName() AS FilePath FROM Document_Content  order by filesize desc

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.

use master
go
Alter database [FileStream_Demo] MODIFY FILE (NAME='Dummy-Documents',OFFLINE)

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

Use [FileStream_Demo]
Go
select File_id, type_desc,name, physical_name,state_desc,size from FileStream_Demo.sys.database_files

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:

use master
go
RESTORE DATABASE [FileStream_Demo] FILE='Dummy-Documents' FROM DISK = N'E:\Backups\FileStream_Filegroup_Demo.bak' WITH NORECOVERY, REPLACE;

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:

backup log [FileStream_Demo] to disk ='E:\Backups\FileStream_Filegroup_Demo_Log1.trn'

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

use master
go
RESTORE LOG [FileStream_Demo] FROM  DISK = N'E:\Backups\FileStream_Filegroup_Demo_Log1.trn'

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:

Use [FileStream_Demo]
Go
select File_id, type_desc,name, physical_name,state_desc,size from FileStream_Demo.sys.database_files

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:

SELECT  
RootDirectory,  
FileName,  
FileAttribute,   
FileCreateDate,   
FileSize,  
FileStreamCol.PathName() AS FilePath FROM Document_Content  order by filesize desc

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.