SQL Server FILE STREAM error: Share name '<name>' is not valid or is already in use. Try a different share name or delete the existing share
In most of the newsgroups and forums I have seen asking how to store/manage the image/video/documents/files within the SQL Server database. SQL Server 2008 version has addressed this issue by enabling the FILE STREAM feature that will integrate the data that is created every day in unstructured way, such as text documents, images, and videos. As you will use the database itslef to store the structured data, so having such unstructured (external) data in addition to data will lead to complex management & administration within your environment. So for such flexibility FILE STREAM feature integrates the SQL Server Database Engine with an NTFS file system by storing such data with varbinary(max) data type BLOB data as files on the file system. Such that you can use the TSQL to insert, update, query, search, and back up FILESTREAM data, where the underlying the Operating System 'Win32' file system interfaces provide streaming access to the data.
The system cache on operating system plays important role here where it uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. One discount to the DBAs that (in terms of performane) SQL Server buffer pool is not used; therefore, this memory is available for query processing.
Until now there wasn't a case or testing to test this new feature in SQL Server 2008 enviornment, so here is my path of problems & resolution and further information on the subject.
As a test I was trying to restore the sample database AdventureWorks2008 that is downloaded from CodePlex site. See this link what changes have been made in the design of AdventureWorks2008. In particular I wanted to test FileStream facilities, so by trying the restore method from SSMS tried to restore the database and got following error:
As the FILESTREAM enabled on that database backup in order to restore you must enable this feature within the SQL instance, as by default this FILESTREAM storage is disabled on the SQL instance. By using the sp_filestream_configure statement you can enable, change the access level of, or disable FILESTREAM storage on an instance of SQL Server, as by default FILESTREAM storage is disabled. Refer to Paul Randal's post about EnablingFileStream-TSQL-SSMS feature.
After following what has been referred above I get the following error :
Share name '<name>' is not valid or is already in use. Try a different share name or delete the existing share
Which is obvious because I have used option 3 on that level of access, which requires a share name for remote access on server level. Again by referring what Paul said above "....that once the share name is specified, it can't be changed without disabling and re-enabling FILESTREAM on the instance....".