title: SQL 2008 FILESTREAM - How to Check if DB Has Filestream Enabled Before Altering It
body: <p>In my situation, there is a possibility of a script being run many times (don't ask why).</p>
  
  <p>So I want to make my script bullet proof before it runs in prod.</p>
  
  <p>I have a pretty big change where I add FILESTREAM capabilities.  I have already got it to work in my dev environment with the necessary scripts by enabling FILESTREAM, altering the database to add a filestream file group, and then adding a file location to that file group, and lastly creating a table with filestream on the data column (varbinary(MAX)).  </p>
  
  <p>That is all good.  But running the ALTER DATABASE script many times can produce errors if a file group is already there.  So i did this...</p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE [name]='NewFileGroup')
      BEGIN
          ALTER DATABASE [MyDb]
          ADD FILEGROUP [NewFileGroup] CONTAINS FILESTREAM
      END 
  GO
  </code></pre>
  
  <p>But the next part of the code is the part that i don't want running many times...</p>
  
  <pre><code>DECLARE @Path NVARCHAR(MAX)
  SET @Path = (SELECT REPLACE(filename, 'MyDb.mdf', 'NewFileGroup') FROM sysfiles WHERE Name = 'PrimaryFileName')
  
  DECLARE @SQL NVARCHAR(max)
  
  SET @SQL = 
  'ALTER DATABASE [MyDb]
  ADD FILE
    (NAME = ''NewFileGroup''
     , FILENAME = ' + QuoteName( @Path, '''' ) 
              + ')
  TO FILEGROUP [NewFileGroup]'
  
  EXEC(@SQL)
  </code></pre>
  
  <p>That code works fine but how do i check if the FILENAME / file path already exists in that file group? Please someobody help. I just want to make another if statement around it.</p>