{"id":1087,"date":"2008-02-04T19:57:33","date_gmt":"2008-02-04T19:57:33","guid":{"rendered":"\/blogs\/paul\/post\/SQL-Server-2008-Enabling-FILESTREAM-through-T-SQL-and-SSMS.aspx"},"modified":"2017-04-13T09:54:16","modified_gmt":"2017-04-13T16:54:16","slug":"sql-server-2008-enabling-filestream-through-t-sql-and-ssms","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/","title":{"rendered":"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS"},"content":{"rendered":"<p><FONT face=Verdana><FONT size=2><br \/>\n<P>The second post in my series on FILESTREAM (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/filestream\/\">here<\/a>) deals with how to enable FILESTREAM through T-SQL and using SSMS.<\/P><br \/>\n<P>You can&#8217;t just create FILESTREAM data &#8211; you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there&#8217;s a file-system filter driver (called RsFx) installed to aid access to the data through the NTFS Streaming APIs. The rules for this are:<\/P><br \/>\n<UL><br \/>\n<LI>The computer needs to be restarted if this is the first time FILESTREAM has been enabled on any instance hosted on the machine<br \/>\n<LI>The instance needs to be restarted if you disable FILESTREAM after its been enabled<\/LI><\/UL><br \/>\n<P>In the previous article I mentioned that FILESTREAM can&#8217;t be enabled on a mirrored database &#8211; there&#8217;s another restriction I forgot: FILESTREAM isn&#8217;t supported for instances running on WOW64. I don&#8217;t remember the architectural limitation that prevents this but I do remember the heated discussions in the dev team back in early 2007.<\/P><br \/>\n<P>Anyway, I digress. You can enable FILESTREAM through T-SQL using the <FONT face=\"Courier New\">sp_filestream_configure<\/FONT> stored procedure. It takes two parameters <FONT face=\"Courier New\">@enable_level<\/FONT> and <FONT face=\"Courier New\">@share_name<\/FONT>. The level of support is configured through the first and has the following options:<\/P><br \/>\n<UL><br \/>\n<LI>0 &#8211; FILESTREAM is disabled for the instance<br \/>\n<LI>1 &#8211; FILESTREAM is enabled for T-SQL access only<br \/>\n<LI>2 &#8211; FILESTREAM is enabled for T-SQL AND local file-system access<br \/>\n<LI>3 &#8211; FILESTREAM is enabled for T-SQL, local file-system, AND remote file-system access<\/LI><\/UL><br \/>\n<P>If level 3 is specified, then the a share name for remote access must also be specified. For example:<\/P><br \/>\n<BLOCKQUOTE dir=ltr style=\"MARGIN-RIGHT: 0px\"><br \/>\n<P><FONT face=\"Courier New\"><FONT color=#0000ff>EXEC<\/FONT> <FONT color=#a52a2a>sp_filestream_configure <\/FONT>@enable_level <FONT color=#808080>=<\/FONT> 3<FONT color=#808080>,<\/FONT> @share_name <FONT color=#808080>=<\/FONT> <FONT color=#ff0000>&#8216;MyFilestreamSQLServerInstance&#8217;<\/FONT><\/FONT><FONT face=\"Courier New\"><FONT color=#808080>;<BR><\/FONT><FONT color=#0000ff>GO<\/FONT><\/FONT><\/P><\/BLOCKQUOTE><br \/>\n<P>Note that once the share name is specified, it can&#8217;t be changed without disabling and re-enabling FILESTREAM on the instance.<\/P><br \/>\n<P>Now, if you don&#8217;t want to have to remember the options you can use SSMS to do the enabling (with no change to the restart requirements listed above). To do this you right-click on the server name in the Object Explorer and select Properties. When the Server Properties window comes up, select the Advanced tab and right at the top is the FILESTREAM section. Here&#8217;s what I mean:<\/P><br \/>\n<P><IMG src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/enablefilestream.jpg\"><\/P><br \/>\n<P>Next up is creating a FILESTREAM filegroup and adding data.<\/P><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The second post in my series on FILESTREAM (see here) deals with how to enable FILESTREAM through T-SQL and using SSMS. You can&#8217;t just create FILESTREAM data &#8211; you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there&#8217;s a [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,86],"tags":[],"class_list":["post-1087","post","type-post","status-publish","format-standard","hentry","category-filestream","category-sql-server-2008"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"The second post in my series on FILESTREAM (see here) deals with how to enable FILESTREAM through T-SQL and using SSMS. You can&#8217;t just create FILESTREAM data &#8211; you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there&#8217;s a [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-02-04T19:57:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:16+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/\",\"name\":\"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-02-04T19:57:33+00:00\",\"dateModified\":\"2017-04-13T16:54:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal","og_description":"The second post in my series on FILESTREAM (see here) deals with how to enable FILESTREAM through T-SQL and using SSMS. You can&#8217;t just create FILESTREAM data &#8211; you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there&#8217;s a [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/","og_site_name":"Paul S. Randal","article_published_time":"2008-02-04T19:57:33+00:00","article_modified_time":"2017-04-13T16:54:16+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/","name":"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-02-04T19:57:33+00:00","dateModified":"2017-04-13T16:54:16+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-enabling-filestream-through-t-sql-and-ssms\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1087","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1087"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1087\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1087"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1087"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1087"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}