I have a SQL DB to which I have recently added Filestream data. Now we have a daily backup/restore to a QA server, and since I added the Filestream data, this has started failing. So I modified the "restore" statement to include a "with move":
RESTORE DATABASE MyDb FROM DISK = 'c:\somepath\mydb.bak' WITH RECOVERY, REPLACE, MOVE 'MyDb' TO 'f:\sql\MyDb.mdf', MOVE 'MyDb_Log' TO 'f:\sql\MyBb_log.ldf', MOVE 'MyDb_Filestream' TO 'f:\sql\MyDb_FS', NOUNLOAD, STATS = 10;
But this is failing with the following error:
File 'MyDb_Filestream' cannot be restored to 'f:\sql\MyDb_FS'. Use WITH MOVE to identify a valid location for the file.
I've Googled all over to find a solution for this. This answer says that you need 3 separate RESTORE DATABASE statements, but doesn't spell out what they are. The accepted answer here indicates that you need to copy over the whole Filestream folder manually, which I find very difficult to believe. And in any case it doesn't make clear what the process is.
Can anyone please spell out to me, in idiot-proof, step-by-step instructions, how exactly one is supposed to backup a DB containing a filestream, copy it to another server and restore it there?