Tuesday, September 21, 2010

Remove FILESTREAM support from SQL Server 2008 (also R2) database

The FILESTREAM feature came with Microsoft SQL Server 2008 and offers a very promising functionality in processing of large data portions. Unfortunately, this function does not allow usage of database mirroring as a high availability feature of SQL database:

http://technet.microsoft.com/en-us/library/bb895334.aspx#DatabaseMirroring

Sometimes you may be happy with just varbinary column type: but if you plan to configure your database for mirroring you have to remove FILESTREAM support form your database completely.

One possible solution is described here:

http://beyondrelational.com/blogs/jacob/archive/2010/03/11/completely-removing-filestream-features-from-a-sql-server-2008-database.aspx 

Alternatively you may use some easier way with SQL Management Studio:

  1. Start SSMS and navigate to your database.
  2. Select tables using FILESTREAM in column definitions and generate DROP and CREATE scripts for them
    (you may need also to DROP and recreate additional tables bound via foreign keys).
  3. Remove FILESTREAM and FILESTREAM_ON directives in table definitions.
  4. Re-create tables.
  5. Open database properties and remove FILESTREAM file group.

That’s all.

If your database contains data, you may need to export the data (before 2) and re-import them (after 5).

Enjoy!

No comments: