The SQL Server FILESTREAM feature is available from SQL Server 2008 onwards. This feature allows the large BLOB objects to store into the file system and keeps metadata in the database tables. Before you go further in this article, let us have a quick overview of the FILESTREAM series articles.
Retrospective
- FILESTREAM in SQL Server: This article gives you an overview of the FILESTREAM feature. You can also learn a different way to enable this feature at the instance level with access configurations
- Managing data with SQL Server FILESTREAM tables: Once you have enabled the FILESTREAM, you need to create the database for it with a dedicated filegroup. This article also shows the DML (update, delete) command for the FILESTREAM database
- SQL Server FILESTREAM Database backup overview: It is an essential aspect for the DBA to understand the backup before enabling any new feature in SQL Server. This article gives you insights of the FILESTREAM of backups for the FILESTREAM database using the extended event session
- Restore SQL Server FILESTREAM enabled database: This article walks you through the internals of database restore for the FILESTREAM enabled database. We cover both the full backup restore and the filegroup level restore also
It is the responsibility of DBA to recover the database in case of any unexpected issues such as data deletion, database corruption etc. In this article, we will cover various database recovery scenarios for the SQL Server FILESTREAM database.
Worked examples
For this demonstration, we’ll use the ‘FileStreamDemoDB_test’ database in our instance.
Currently, we have only one document in our FILESTREAM table.
Let us insert one more record into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @File varbinary (MAX); SELECT @File = CAST( bulkcolumn as varbinary(max) ) FROM OPENROWSET(BULK 'C:\sqlshack\Stonehenge.jpg', SINGLE_BLOB) as MyData; INSERT INTO DemoFileStreamTable_1 VALUES ( NEWID(), 'Stonehenge Picture', @File ) |
Now we have two records in the test table.
Below is the screenshot of the SQL Server FILESTREAM container.
We can put the database in Full recovery model using the below command.
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [FileStreamDemoDB_test] SET RECOVERY FULL WITH NO_WAIT GO |
Now take a filegroup level backup for the FILESTREAM database.
|
1 2 3 4 |
BACKUP DATABASE [FileStreamDemoDB_test] FILEGROUP = N'DemoFileStream' TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileGroupBackup.bkp' WITH NOFORMAT, NOINIT, NAME = N'FileStreamDemoDB_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Now, let us generate the recovery scenario. We are going to delete the below-highlighted file from the FILESTREAM container. We do not get any prompt that the file is in use while deleting it from the filesystem.
Remove this file from the FILESTEAM container manually.
Once we have deleted the file, let us rerun the query to check the available records in the SQL Server FILESTREAM container. We do not get any prompt that the file is in use while deleting it from the filesystem.
Once we have deleted the record, We get the below error message.
We cannot access the FILESTREAM database objects now. In this case, we need to restore the Full database backup to fix this error. Launch the database restore wizard and provide the database backup file path.
You get the warning message that it is going to perform a tail log backup before the restoration. The Tail log backup takes backup of the changes that are not backed up yet.
We do not want to take the tail-log backup at this point. We can uncheck the tick on ‘Take a tail-log backup before restore’.
You might get below error message if the database is in use because it cannot get the exclusive access on that database.
Therefore, put a check on the ‘Close existing connections to destination database’.
Once the database restoration is complete, rerun the query to check the records. We have access the database now, and table record is present as shown below.
After the database restoration, you can see that in the filesystem containing the file is present which we deleted in previous steps.
Recovery using the FILESTREAM FILEGROUP level backup
In SQL Server FILESTREAM Database backup overview, we provided an overview of filegroup level backup for the FILESTREAM database. We can restore the deleted files using the filegroup level backup but we need to have below backups for the restoration
- FILESTREAM Filegroup level backup
- Tail-log backup
Let us initiate the filegroup level backup now.
Once the filegroup backup is complete, delete the record again from the filesystem container. We again received the below error message.
Now, take the tail-log backup with SSMS.
Select the option ‘Backup the tail of the log and leave the database in the restoring state’.
After the tail-log backup, the database is in ‘Restoring’ state.
Previously, we used the full backup to recover the file and resolve this error message.
Now, let us restore the filegroup level backup and put the database in ‘NoRecovery’ mode. We can restore further transactions log when the database is in ‘NoRecovery’ mode. Click on ‘Options’ and put a check on ‘Leave the database non-operational and don’t roll back the uncommitted transactions (Restore with NoRecovery).’
Once the filegroup level restore is completed, we need to restore the tail-log backup. We cannot restore the tail-log backup from the SSMS graphical wizard. We need to run the t-SQL command to do it.
Run the below command in the master database to restore the tail-log backup.
|
1 |
RESTORE LOG FileStreamDemoDB_test FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\backup3.trn' |
Below is the output of the tail-log backup restore. The database is in Online status after this restore.
We have recovered the deleted file that we deleted earlier for this demo.
In the previous approach, we needed to take the complete database in the restoring mode to restore the tail-log backup. We might be using the database for the FILESTREAM as well as standard database activities. In this case, there will be downtime for the complete database. We might not want this situation especially if database is in the production. We can restore the filegroup level backup also in the enterprise edition of SQL Server.
Recovering using a transaction log backup
Suppose the filegroup is corrupt and we cannot access it. For demo purpose, let us put filegroup in offline status with below command.
|
1 |
ALTER DATABASE FileStreamDemoDB_Test MODIFY FILE(NAME = DemoFiles, OFFLINE) |
Check the status of the database files using the below command.
In the below image, check the status for the FILESTREAM database file, it is showing the status as ‘Offline’ for the FILESTREAM.
At this moment, we cannot access the FILESTREAM data. If we try to select the record from the table in the FILESTREAM filegroup, we get the below error message.
In the tail-log backup, the database becomes unavailable, therefore; we are taking a regular transactional log backup so that database remains online only.
Now let us start the database recovery process by restoring the filegroup backup and then the transactional log backup. In the Restore wizard, select the option ‘Restore Files and FIlegroups’.
We get the below error while restoring the FILESTREAM filegroup backup. We need to use Replace option while restoring the filegroup backup.
To resolve this error, put a checkbox on the ‘overwrite the existing database (WITH REPLACE)’ and restore the database with ‘NORECOVERY’ clause.
Click on ‘OK’ and database restores successfully now with the filegroup level restore.
We can do the restore of the log backup now. Run the below command in SSMS; we cannot restore this t-log backup with GUI.
|
1 2 3 |
use master go RESTORE LOG FileStreamDemoDB_test FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\backup6.trn' |
Once the log backup restoration completes, you can verify the status of the files. It is showing ONLINE now.
Again, perform the database validation stuff. You can find the file now in the SQL Server FILESTREAM container. In the database also, you can see the record.
Conclusion
This article gives you an overall idea to recover the deleted objects using the full database restore and filegroup level restoration. It will help to recover the database in case of any unexpected issues. We will cover more on SQL Server FILESTREAM in my next article. Stay tuned!
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023