How can I backup and restore a database with FileTable to the same SQL server instance; with FILESTREAM 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".
The issue: I have FileTables in the backup and it looks like its trying to restore the files over the existing database's files.
This is the script I'm using to do the restore:
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?
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 DIRECTORY_NAME for the FILESTREAM 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)
