Restore database backup with SQL FileTable to same instance
<p>How can I backup and restore a database with <code>FileTable</code> to the <em>same</em> SQL server instance; with <code>FILESTREAM</code> enabled; but with a different name? Whether I do it through the GUI or write out scripts to do so, I get stuck at the same place: The restore to the new, non-existent database fails with "cannot obtain exclusive access to database".</p>

<p>The issue: I have FileTables in the backup and it looks like its trying to restore the files over the existing database's files.</p>

<p>This is the script I'm using to do the restore:</p>

<pre><code>RESTORE DATABASE [MyDatabase_Test]
FROM  DISK = N'D:\Backup\MyDatabase.bak' 
WITH  FILE = 1,  
MOVE N'MyDatabase' TO N'D:\Data\MyDatabase_Test.mdf',  
MOVE N'MyDatabase_log' TO N'E:\Data\MyDatabase_Test_log.ldf',  
MOVE N'MyDatabase_Docs' TO N'D:\Data\MyDatabase_Test_Docs',  NOUNLOAD,  STATS = 5
-- BUT now my database has a FileTable called 'Documents' which is setup as \\DBSERVER\MyDatabase\Documents.
-- How do I specify that my files should go to \\DBSERVER\MyDatabase_Test\Documents during the restore?
</code></pre>

<p>This only breaks if it's the same SQL instance it is being run on. My guess is that I'd need to specify the <code>DIRECTORY_NAME</code> for the <code>FILESTREAM</code> of the database that I am restoring to... but haven't figured out how to do it (or if I'm on the right track)</p>