Conditional filegroup script file in a Visual Studio 2010 database project

Question

I have a Visual Studio 2010 database project. Inside the project, I added two Storage → Filegroup File scripts: one for my main data file (.mdf) and one for my log file (.ldf). This will be expanded in the future.

Build Action is currently installed on Build for both of these files.

The content of each file is simple. The only reason I created them is to fully control their file names.

Primary.sql:

 ALTER DATABASE [$(DatabaseName)] ADD FILE ( NAME = [Primary], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf' ); 

Log.sql:

 ALTER DATABASE [$(DatabaseName)] ADD LOG FILE ( NAME = [Log], FILENAME = '$(DefaultLogPath)$(DefaultFilePrefix)_Log.ldf' ); 

When I publish my data project to create a new database, the next TSQL / SQLCMD is created behind the scenes. This is exactly what I want:

 PRINT N'Creating $(DatabaseName)...' GO CREATE DATABASE [$(DatabaseName)] ON PRIMARY(NAME = [Primary], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf') LOG ON (NAME = [Log], FILENAME = '$(DefaultLogPath)$(DefaultFilePrefix)_Log.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS GO 

However, when I post changes to an existing database (for example, for example, I add a new table), the following TSQL / SQLCMD is created instead:

 PRINT N'Creating [Primary]...'; GO ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [Primary], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf') TO FILEGROUP [PRIMARY]; GO PRINT N'Creating [Log]...'; GO ALTER DATABASE [$(DatabaseName)] ADD LOG FILE (NAME = [Log], FILENAME = '$(DefaultLogPath)$(DefaultFilePrefix)_Log.ldf'); 

This is not what I want, as the .mdf and .ldf files already exist. How can I modify the storage scripts to fit in both scenarios: creating a new database and updating an existing database?

Any ideas?

Update 8/18

I thought I could just put the storage scripts into an IF / EXISTS type check. If the file already exists, do nothing. This does not work. This actually prevents the creation of a solution at all:

So something like:

 IF(EXISTS(SELECT TOP(1) 1 FROM sys.master_files as mf WHERE DB_NAME(mf.[database_id]) = [$(DatabaseName)] AND name = '[Primary]')) BEGIN ALTER DATABASE [$(DatabaseName)] ADD FILE ( ... ); END 

throws the following compiler error:

 Error 200 SQL70001: This statement is not recognized in this context. 

The way these scripts are processed behind the scenes (new database and update) makes me think that they cannot be conditional.

Answer 1

Create 1 sql file, set the build action as PreDeploy

And add your scripts to this file:

 IF(EXISTS(SELECT TOP(1) 1 FROM sys.master_files as mf WHERE DB_NAME(mf.[database_id]) = '[$(DatabaseName)]' AND name = '[Primary]')) BEGIN ALTER DATABASE [$(DatabaseName)] ADD FILE ( NAME = [Primary], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf' ); ALTER DATABASE [$(DatabaseName)] ADD LOG FILE ( NAME = [Log], FILENAME = '$(DefaultLogPath)$(DefaultFilePrefix)_Log.ldf' ); END 

This file will be executed every time you deploy and validate the database. But you will only have one PreDeploy file, so any srcips should be added to this file, but this is not a big problem.