how to restore a sql server backup containing filestream data assigning a new folder name for filestream data?

Question

To create a new database and populate it from a backup I typically do:

CREATE DATABASE MyDatabase -- I create an empty database

-- I retrieve the filepath of mdf and ldf files
DECLARE @FileData varchar(1000)
DECLARE @FileLog varchar(1000)

set @FileData = (select filename from MyDatabase.dbo.sysfiles where fileid = 1)
set @FileLog = (select filename from MyDatabase.dbo.sysfiles where fileid = 2)

-- I Restore the database from backup by substituting the mdf and ldf files
RESTORE DATABASE MyDatabase
FROM DISK = 'c:\Test\Test.bak'
WITH REPLACE,
MOVE 'MyApp_Data' TO  @FileData, -- I forced the name to be MyApp_Data for simplicity
MOVE 'MyApp_Log' TO  @FileLog    -- I forced the name to be MyApp_Log for simplicity

How to do the same for the filestream data too? May I force the folder creation for filestream data or should I create it manually?

May you also comment on my approach?

Answer 1

I think you can use the MOVE option with the filestream data as well. You can run the following command to view the file list in your backup file. This will help identify the filestream file.

RESTORE FILELISTONLY
FROM DISK = 'c:\Test\Test.bak'

As for your overall approach, I think you may be doing more than you need to do. You don't have to create a database before you restore a backup to it. The RESTORE command will take care of the create DB steps.

So, your RESTORE command would be the same as your using, except that you can exclude the REPLACE option. You can specify your destination file information in the MOVE option.