FileStream Backup and Restore
I have been asked to provide some information on best practice for SQL Server Backup and Restore with Filestream Databases.
For Backup.
The method is exactly the same like you do for your regular non-Filestream databases. This means you can include your Filestream database in your current backup strategy with no additional work.
For Restore.
There is a bit of understanding that is required.
Filestream has the following to restore
Data
Log
Filestream Data
The last part Filestream Data is what you need to be aware of.
For example if I want to restore a Filestream database unto the same instance, I can't just rename the database, because that only changes the data and log name, the Filestream Data which is stored in a directory on the same environment needs to be modified as well. The image below is where you can do this. I recommend using a folder completely different to avoid any issues. If you have more question around this. Please let me know
If you're restoring to a new environment. The Filestream data and log will be restore to the default data directory and so will the Filestream data. If you do not want it in the Data directory, you will have to specify that like in the image below.
Here is a Microsoft blog that explains the restore process for Filestream
How to: Restoring SQL File stream Data https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/07/23/how-to-restoring-sql-file-stream-data/
This is another resource that I found that might be useful.
The book has a lot of information about Filestream https://assets.red-gate.com/community/books/art-of-ss-filestream.pdf
Please let me know if you have any other questions.
------------
One thing I forgot to mention is that when you restore the filestream database on the same instance
Make sure you are aware of the tail-log backup.
You should have this unchecked. If you don't, you will but the original database in a restoring state, meaning no one can access the database, until it is recovered.
If you have the tail-log check here is what the script looks like
USE [master]
BACKUP LOG [Archive] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Archive_LogBackup_2018-08-16_11-36-52.bak' WITH NOFORMAT, NOINIT, NAME = N'Archive_LogBackup_2018-08-16_11-36-52', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [1234] FROM DISK = N'C:\Archive.bak' WITH FILE = 1,
MOVE N'Arch1' TO N'C:\FSDemo\archdat1.mdf',
MOVE N'Archlog1' TO N'C:\FSDemo\archlog1.ldf',
MOVE N'Arch3' TO N'C:\FSDemo\filestream1',
MOVE N'arch4' TO N'C:\FSDemo\filestream2',
MOVE N'arch5' TO N'C:\FSDemo\filestream3', NOUNLOAD, STATS = 5
GO
Notice the second line is taking a backup of the original database, when this is done, this puts the original database in a restore mode. If you accidently do this. You have to recover the database with the following script
RESTORE DATABASE <NAME> WITH NO RECOVERY
Ex. RESTORE DATABASE Archive WITH NO RECOVERY
This should recover the database and business can continue as usual.
Here is a script without the tail-log. This will work fine without affecting the original database.
USE [master]
RESTORE DATABASE [1234] FROM DISK = N'C:\Archive.bak' WITH FILE = 1,
MOVE N'Arch1' TO N'C:\FSDemo\archdat1.mdf',
MOVE N'Archlog1' TO N'C:\FSDemo\archlog1.ldf',
MOVE N'Arch3' TO N'C:\FSDemo\filestream1',
MOVE N'arch4' TO N'C:\FSDemo\filestream2',
MOVE N'arch5' TO N'C:\FSDemo\filestream3',
NOUNLOAD, STATS = 5
GO