2

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?

Thanks!

  • Does the folder F:\sql\MyDB_FS exist, and does the SQL Server service account have access to that folder? – Max Vernon Aug 10 '14 at 14:04
  • @MaxVernon - yes and yes – Shaul says I Support Monica Aug 10 '14 at 14:07
  • I presume you've enabled filestream features at the instance level on the QA server? msdn.microsoft.com/en-us/library/gg509097.aspx#BasicsFilestream – Max Vernon Aug 10 '14 at 14:18
  • @MaxVernon - yes... but I think I have solved it. The SYSTEM account also needed access to the filestream folder. Posting as an answer now. – Shaul says I Support Monica Aug 10 '14 at 14:44
  • I had the same problem, and the solution was change to local acount sql services, change folder rigths and select replace in restore options – user75554 Sep 8 '15 at 14:50
2

I believe the mystery is cleared up. The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access. I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.