December 6, 2011 at 8:15 am
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
December 6, 2011 at 8:37 am
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
December 6, 2011 at 10:03 pm
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
December 7, 2011 at 1:27 am
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.
December 7, 2011 at 2:17 am
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
December 7, 2011 at 2:26 am
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