VisualStudio SQL sqlcmdvars Database Project TFS Deployment Overrides User File Path?

Question

I have a SQL database project in VisualStudio 2010 in source control with TFS 2010 with several branches, each of which is deployed to different servers depending on the configuration profile.

Using the sqlcmdvars file with the configuration profile added to the file name (Debug.sqlcmdvars, etc.) for each configuration profile, I can specify a unique file path for a specific deployment environment using the reserved names Path1 and Path2 for the mdf file and the log file .

<?xml version="1.0" encoding="utf-8"?> <SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars"> <Version>1.0</Version> <Properties> <Property> <PropertyName>Path1</PropertyName> <PropertyValue>C:\SQLSERVER LOG\$(DatabaseName)\</PropertyValue> </Property> <Property> <PropertyName>Path2</PropertyName> <PropertyValue>C:\SQLSERVER DATA\$(DatabaseName)\</PropertyValue> </Property> </Properties> </SqlCommandVariables> 

Now I'm trying to add my own FileStream file with the associated FileStream FileGroup

I added an extra entry to the sqlcmdvars files:

 <Property> <PropertyName>PathBlobStream</PropertyName> <PropertyValue>C:\SQLSERVER DATA\$(DatabaseName)\BlobStream\</PropertyValue> </Property> 

But I'm not sure how to tell the database that SchemaObjects \ Database Level Objects \ Storage \ BlobStore.sqlfile.sql declared:

 ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [BlobStore], FILENAME = 'C:\SQLSERVER DATA\####\BlobStream') TO FILEGROUP [BlobStreamFileGroup]; 

How do you use the new entry in sqlcmdvars to override the path for the new FileStream file?

Answer 1

The best way I found this is to add additional entries to the dbproj file to use a different schema file depending on the configuration profile:

  <ItemGroup Condition=" '$(Configuration)' == 'Debug' "> <Build Include="Schema Objects\Database Level Objects\Storage\Files\BlobStore.sqlfile.sql"> <SubType>Code</SubType> </Build> </ItemGroup> <ItemGroup Condition=" '$(Configuration)' == 'Internal' "> <Build Include="Schema Objects\Database Level Objects\Storage\Files\BlobStore.sqlfile.Internal.sql"> <SubType>Code</SubType> </Build> </ItemGroup> 

The key was copied / pasted into the source schema file and the name of the configuration profile was added to the file name. This saves debugging rejection with the original file name, so any future schema mappings will not pay attention to additional options. You want to include additional schema files in the original control, but if the next set is to the above entries, they will not be displayed in the project. I have confirmed that MSBuild is handling this correctly. I cross my fingers that TFS will do the same.