Wednesday, September 22, 2010

Remove FILESTREAM support from SQL Server 2008 (also R2) database: Part 2–Visual Studio Database Project

Well, after FILESTREAM support is removed from your database definitions, you will definitely like to update your visual studio database project.

Started as always – compare schema definitions, write updates. But the FILESTREAM sql’s are not removed. Trying to remove it manually (just exclude from project) causes an error, saying <unnamed> has reference to non existing file group FILESTREAM.

Checked all the files in project manually – no references to FILESTREAM at all. Rebuild – same error.

The source of the message is the .dbmdl file still containing references to FILESTREAM. This file is a binary one and cannot be edited.

Workaround:

  1. Close VS if open.
  2. Manually rename the .dbmdl file (for example, in .dbmdl.org – just for rollback if required).
  3. Start VS and open the database project.
  4. Exclude FILESTREAM dependent sql Script under <your project>\Schema Objects\Database Level Objects\Storage\Filegroups.
  5. Exclude FILESTREAM dependent file definition sql script under <your project>\Schema Objects\Database Level Objects\Storage\Files.
  6. Rebuild and redeploy the project – you shouldn’t see any references to FILESTREAM in output window and in the target database as well.

Enjoy!

1 comment:

Anonymous said...

You rule. Thanks.