In the series on the SQL Server FILESTREAM feature, we have explored the various aspects of FILESTREAM including its overview, internal architecture, database creation etc. In my last article [put the name of the article and a link], we explored the backup for the FILESTREAM enabled database. We can take Full backup as well as filegroup level backup in the FILESTREAM database.
In this article, we are going to look at full database restores and filegroup level restores as well.
Before we start with this article, you should follow the previous articles and perform the following steps.
- Enable the FILESTREAM feature in SQL Server and configure access
- Create a FILESTREAM database and insert sample records into it
- Backup the FILESTREAM backup (Full backup and the FILESTREAM filegroup level backup)
Suppose we want to restore the FULL backup of the SQL Server FILESTREAM database in the same instance. There, we will use the different name for this database in the same instance.
Connect to the SQL Server instance and Right-click on the instance and ‘Restore Database’
Specify the backup file location and the new database name in the restore database wizard.
Now click on the files tab from the left side menu. We need to change the data file and log file location because our source database exists on the same server path. You cannot put the database into a similar location with the same file names.
Let us again generate the script to ‘New Query Editor Window.’ Alternatively, you can save the script in a file as well. Later, we can open the file to view the script.
You can see the restore database command for SQL Server FILESTREAM enabled database. It is also similar to a standard database restore command. The only difference is that you can see a FILESTREAM file move command also in the script.
|
1 2 3 4 5 6 7 8 |
USE [master] RESTORE DATABASE [FileStreamDemoDB_Restore] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileStreamDemoDB.bak' WITH FILE = 9, MOVE N'FileStreamDemoDB' TO N'C:\sqlshack\Demo\FileStreamDemoDB_Restore.mdf', MOVE N'FileStreamDemoDB_log' TO N'C:\sqlshack\Demo\FileStreamDemoDB_Restore_log.ldf', MOVE N'DemoFiles' TO N'C:\sqlshack\Demo\DemoFiles', NOUNLOAD, STATS = 5 GO |
Execute this restore database command. We can see the output of the restore command also processed FILESTREAM file ‘DemoFiles’ similar to the backup command output.
9 percent processed.
14 percent processed.
18 percent processed.
23 percent processed.
28 percent processed.
33 percent processed.
…
100 percent processed.
Processed 432 pages for database ‘FileStreamDemoDB_Restore’, file ‘FileStreamDemoDB’ on file 9.
Processed 2 pages for database ‘FileStreamDemoDB_Restore’, file ‘FileStreamDemoDB_log’ on file 9.
Processed 2041 pages for database ‘FileStreamDemoDB_Restore’, file ‘DemoFiles’ on file 9.
RESTORE DATABASE successfully processed 2475 pages in 1.859 seconds (10.399 MB/sec).
Now, let us go to the FILESTREAM container for the newly restored database. Below we can see both the databases contain the exact similar files and file size is similar to each other.
We cannot restore the SQL Server FILESTREAM container in the same location as of source database. If we try to do so, we get the below error message.
Msg 3156, Level 16, State 6, Line 2
File ‘DemoFiles’ cannot be restored to ‘C:\sqlshack\FileStream’. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
In the last article, we took the database backup while the file in the FILESTREAM container opened in another program. Below was the error in the backup of the database.
Msg 3056, Level 16, State 1, Line 1
The backup operation has detected an unexpected file in a FILESTREAM container.
The backup operation will continue and include file ‘C:\sqlshack\FileStream\DemoFiles\97f720ed-afbf-413d-8f5c-5b56d4736984\8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e\00000024-00000588-000c\
C:\sqlshack\FileStream\DemoFiles\97f720ed-afbf-413d-8f5c-5b56d4736984\8f62d1b7-7f8b-4f98-abe9-a06b4faa1d2e\~$000024-00000588-000c’.
Now let us restore the database backup that completed with the above warning message. If we restore the database, it works fine. Now, let us go to the container of the newly restored database, and we can see the temporary file there as well. However, there is no issue with the database availability and work due to this hidden file.
Now let us look at the internal of the FILESTREAM database restore using the extended event session. This event session we configured in my last article. You can follow the steps there and monitor it during the database restore.
First, it takes the locks on the database (if we are doing database overwrite) and opens the backup set. Once it reads the backup set, it plans for the database restore.
In below screenshot, you can notice that it begins the OFFLINE database restore. We are restoring the SQL Server FILESTREAM enabled database backup. Therefore it prepares the FILESTREAM containers into the path we specified in database restore command.
Now, it starts transferring the data. In the backup stream, you can see that it starts processing the FILESTREAM data.
Once it completes the data copy for the FILESTREAM, it starts log zeroing. Log zeroing is the process of file Initialization during the database restore.
Once the data transfer is complete, it starts the database recovery process. In below image, you can notice it starts the SQL Server FILESTREAM recovery as well along with the data recovery. Once the file recovery is completed, it starts the offline roll forward for the transactions.
In the last step, it starts the process to bring this database to ONLINE state. It runs the post-restore container fix and starts the database in ONLINE status.
FILEGROUP level restore for the FILESTREAM database
In the previous article, we performed the FILESTREAM filegroup backup as well. We can check the FILESTREAM database files and its status.
|
1 |
select File_id, type_desc,name, physical_name,state_desc,size from sys.database_files |
We can mark SQL Server FILESTREAM file status to OFFLINE. When the FILESTREAM file status is OFFLINE, its filegroup status is also OFFLINE.
Run the below command in the FILESTREAM database to make it offline.
The output of the command is as below. It rolls back the transactions in the particular database.
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Now, we can see the status for this FILESTREAM file as offline.
Note: You should have taken the filegroup level back up before it is in OFFLINE status. If we run the backup while the filegroup is OFFLINE, we get the below error message. The error message says that we can take the backup filegroup backup only it is in ONLINE status.
Msg 3007, Level 16, State 2, Line 3
The backup of the file or filegroup “DemoFileStream” is not permitted because it is not online. Container state: “Offline” (2). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Let us consider that we want to restore this FILEGROUP backup only for this database. We can do the filegroup level backup from the Tasks -> Restore -> Files and Filegroups.
In the database restore wizard, specify the filegroup backup file path. Let us generate the script for the filegroup restore.
Below is the script for filegroup restore.
|
1 2 3 4 5 |
RESTORE DATABASE [FileStreamDemoDB] FILE = N'FileStreamDemoDB', FILE = N'DemoFiles' FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\FileGroupDB.bak' WITH FILE = 2, NOUNLOAD, STATS = 10 GO |
In the output, we can see that it set the roll forward start point LSN. It requires additional roll forward LSN to complete the restore sequence. The status of the SQL Server FILESTREAM data file is ‘Restoring’ at this point.
We need to take transaction log backup as well for the FILESTREAM DB. Run the below script to take the t-log backup.
|
1 2 3 |
BACKUP LOG [FileStreamDemoDB_test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\log.trn' WITH NOFORMAT, NOINIT, NAME = N'FileStreamDemoDB_test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Now we need to restore the t-log backup on SQL Server FILESTREAM database. In the below image, we can see that transaction log restore option is disabled in the SSMS.
Therefore, we need to restore the t-log backup using the t-sql only.
|
1 2 3 |
use master go RESTORE LOG [FileStreamDemoDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\log.trn' |
We can see below that, t-log backup is now restored successfully. Status of the FILESTREAM filegroup is also online after the t-log restore.
Conclusion
In this series on the SQL Server FILESTREAM, we learned to restore the backup for the SQL Server FILESTREAM database. We can restore the full backup as well as filegroup level backup. In the next article, we will explore a few recovery scenarios of the FILESTREAM database.
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
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023