SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Enable Filestream on Instance and database

By Theo Jacobs,

Firstly what is FileStream (from the interwebs somewhere)

FILESTREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc.) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table

This procedure was created because having to do this more than 3 times manual is just plain wasting time.

We have several databases that use Filestream. Some clients have been lax in upgrading their schemas and I had to roll out remotely several setups to check and enable Filestream on their SQL instances. As I did not have remote access and their "DBA" did not know where to look/enable this feature I decided to create the script. Another spanner for my toolbelt.

So all you need to do is run the CREATE PROCEDURE script to create the sp on your master db (please no emails saying i am a heritic for adding custom sp to master, i know i know)

At the top of the sp is a comment line that you can execute afterwards to do what it says on the box.

There is very little error handling in the SQL (say it aint so) but at least you can run the sp several times without SQL error messages poping up and spoiling your day.

Feel free to add comments on enhancements that can be added or any caveats that might be useful like you need to be logged in as admin on SQL instance to change configuration settings.

Happy Coding.


Total article views: 1304 | Views in the last 30 days: 0
Related Articles

FILESTREAM storage requirement




Turning on FileStream post installation


Main Store procedure to execute several store procedures or Trigger?

Main Store procedure to execute several store procedures or Trigger that executes a store procedure?...


Create a Filestream Filegroup for Filetables – SQL Server 2012

Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the fil...


Filestream - Clean up unwanted files

Can't find a way to get rid of reports stored in a filestream enabled data base