MSSQL – Purpose of Keeping Filegroup or Filestream Backup

backupfilestreamrestoresql server

I'm a new DBA for SQL SERVER. Currently I'm learning about filestream on SQL server. So I tried to restore only filegroup of filestream to other database, but I get an error message:

An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)

File 'testdb1' was not backed up in file 1 on device 'C:\data\New
folder\FSbackup.bak'. The file cannot be restored from this backup
set. RESTORE DATABASE is terminating abnormally. (Microsoft SQL
Server, Error: 3144)

Is it possible to restore only filestream to other database or restore only an old version of filestream to the current database of FILESTREAM?

Best Answer

So I tried to restore only filegroup of filestream to other database, but I get an error message

As Laughing Vergil originally mentioned in a comment, your question is answered in this great Stack Overflow post: Restore filegroup to different database

The gist of the answer is that you always have to start by restoring the database with the PRIMARY filegroup. Then you can restore additional filegroups (like the filestream one) after that. This called a "piecemeal restore" and is described in the documentation.

Is it possible to restore only filestream to other database

No, as mentioned above it needs to be restored along with the PRIMARY filegroup.

or restore only an old version of filestream to the current database of FILESTREAM?

Tibor responded to this part in a comment. The basic answer is "no." This is because of the way database recovery works - you can't restore different parts of the database that are from different points in time.

As a side note, there is a restriction specifically related to filestream filegroups:

If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported.

Related Question