Deploy a database project build output (dbschema & co) to various databases

Question

I am trying to build a database project, and then use the output along with VSDBCMD to deploy it to different databases. I have added different sqldeployment & sqlcmdvars files corresponding to the environments I aim, but I am not able to change the following variables, which seem to be readonly (DatabaseName, DefaultDataPath, DefaultLogPath). For example I would like to have QA and UA databases on the same server instance so I need my deployment script to work with different database names and files path.

Moreover when I build my database project in TFS I cannot seem to find the specific sqlcmdvars & sqldeployment files (ex MyDatabase-QA.sqlcmdvars, MyDatabase-UA.sqlcmdvars) in my drop folder.

Am I doing something wrong? What other options do I have?

Thanks in advance!

Answer 1

A little late for you maybe, but after a bit of digging, if you want to set, for a example a custom file path for your MDF/LDF, I did the following.

• Create a new var in the sqlcmdvars file.
• use this var in the File sql file at as follows: DBProject\Schema Objects\Database Level Objects\Storage\Files

ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [MYDBNAME], FILENAME = '$(DataLocation)\MyDatabaseName.mdf', SIZE = 2304 KB, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];