Restore Database in a Network Location having FILESTREAM enabled

  • Hello,

    I am running a restore Database command from one machine say "abc" to restore the Database in another machine say "fsipl" using SQL Server 2008 R2. I use the following command

    DECLARE @DataPath nvarchar(500)

    DECLARE @LogPath nvarchar(500)

    DECLARE @FSPath nvarchar(500)

    SET @DataPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.mdf'

    SET @LogPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.ldf'

    SET @FSPath= N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\FS_TEST'

    RESTORE DATABASE TEST

    FROM DISK = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.FNG'

    WITH MOVE 'TEST' TO @DataPath,

    MOVE 'TEST_log' TO @LogPath,

    MOVE 'FS_TEST' TO @FSPath

    GO

    Both the machines are in the same Workgroup.

    On executing the above command, I get the following error,

    The path '\\fsipl\DNetWorkBackupPath\DATAFOLDER\FS_TEST' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.

    How do I restore the Database having FILESTREAM enabled in a network location. Need to solve this issue urgently. Please help.

    Madhu

  • this is a limitation of FILESTREAM from what I have been reading, the FILESTREAM file needs to be on a local drive to the SQL server hosting the DB.

    I would also put your MDF and LDF on the SQL server as well, I wouldn't have them on a UNC path.

    http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-FILESTREAM-storage.aspx

  • http://msdn.microsoft.com/en-us/magazine/dd695918.aspx

    I have gone through an article in the above mentioned link which states that

    Filestream can be disabled, enabled for local access, or enabled for local and remote access. In addition, the DBA must define a database filegroup that ties an NTFS file system location to a SQL Server database. Note that the filegroup needs to point to a local file system location; filestreams can't live on a remote server or a network addressable storage (NAS) devices unless the NAS device is presented as a local NFS volume via iSCSI. Accessing the filestream uses the server message block (SMB) protocol, so if you're going to allow file I/O-style access from outside the machine where SQL Server is installed, you must allow access to the SMB port (usually port 445, with port 139 as a fallback) through the firewall.

    How can I set the configuration through iSCSI and relate it to FILESTREAM. I am totally unaware of this system.

    Madhu

  • local access is accessed from only the SQL server itself, local and remote is the share name which can be used to unc from a client machine to the SQL server. For example the server will see it as X:\FileStreamShare but when you access from an app or a client it would be \\SQL1\FileStreamShare

    to present the drive as iSCSI you will need to speak to your server admins to see if they can give you a space on the NAS which can be attached as a sudo local drive, but again the server sees it as a local drive which gets around the storage on unc paths, while you may access it as \\NAS1\FileStreamShare on a client the server will see it as Z:\FileStreamShare for example so its a local drive not a network drive according to the server.

  • Thank You for the information. Since I am not aware of iSCSI, I spoke to my System Admin. He needs to work it out but not sure.

    I have tried doing the following but still the same error is there

    DECLARE @DataPath nvarchar(500)

    DECLARE @LogPath nvarchar(500)

    DECLARE @FSPath nvarchar(500)

    SET @DataPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.mdf'

    SET @LogPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.ldf'

    SET @FSPath= N'S:\DATAFOLDER\FS_TEST'

    RESTORE DATABASE TEST

    FROM DISK = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.FNG'

    WITH MOVE 'TEST' TO @DataPath,

    MOVE 'TEST_log' TO @LogPath,

    MOVE 'FS_TEST' TO @FSPath

    GO

    WHERE S="\\fsipl\DNetWorkBackupPath"

    I need to solve this issue but can't understand that how do I do it. So long the filestream was not enabled I was able to Restore database in Network Location with the following command

    DECLARE @DataPath nvarchar(500)

    DECLARE @LogPath nvarchar(500)

    SET @DataPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.mdf'

    SET @LogPath = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.ldf'

    RESTORE DATABASE TEST

    FROM DISK = N'\\fsipl\DNetWorkBackupPath\DATAFOLDER\TEST.FNG'

    WITH MOVE 'TEST' TO @DataPath,

    MOVE 'TEST_log' TO @LogPath

    GO

    Please suggest how to do it because my Project has the facility to Backup and Restore databack up is Network Location.

    Madhu

  • RESTORE DATABASE databasename FROM DISK = 'path to unc bak file' WITH REPLACE,

    MOVE 'data file logical file name' TO 'physical file name', --must be a local drive

    MOVE 'log file logical file name' TO 'physical file name', --must be a local drive

    MOVE 'filestream logical file name' TO 'folder path' --must be a local drive

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply