Setting DefaultDataPath and DefaultLogPath on Depl

2019-05-24 11:03发布

问题:

To be true the following query is not originally mine, but I am facing the exact issue in my work. And thus copying the problem statement as it is. I am not able to find any solution. I would really appreciate any suggestions or highlights of the following Problem Set:

I do not have any hardcoding in Schema Objects\Database Level Objects\Storage\Files - both in database and log files.

The deployment script that gets generated when I click "deploy" from db project has hardcoding in :SETVAR with database and log file path assigned to $(DefaultDataPath) and $(DefaultLogPath) - see below: :setvar DefaultDataPath "C:\DB\Microsoft SQL Server\MSSQL10_50.MITCHELL\MSSQL\DATA\" :setvar DefaultLogPath "C:\DB\Microsoft SQL Server\MSSQL10_50.MITCHELL\MSSQL\DATA\".

I want the deployment script to accept input for data and log path. But based on script that is being currently generated, I'll have to manually comment the above 2 lines and modifiy the CREATE DATABASE statement as shown below every single time to replace any reference to "DefaultDataPath" and "DefaultLogPath" with "InputDefaultDataPath" and "InputDefaultLogPath" CREATE DATABASE [$(DatabaseName)] ON PRIMARY(NAME = [TEST_DB], FILENAME = '$(InputDefaultDataPath)$(DatabaseName).mdf', >SIZE = 3072 KB, FILEGROWTH = 1024 KB) LOG ON (NAME = [TEST_DB_log], FILENAME = >'$(InputDefaultLogPath)$(DatabaseName)_log.ldf', SIZE = 1024 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS

 Question 1: How do I prevent db project from scripting the below two lines and also 

prevent it from subsituting these variables in CREATE DATABASE statement? :setvar DefaultDataPath "C:\DB\Microsoft SQL Server\MSSQL10_50.MITCHELL\MSSQL\DATA\" :setvar DefaultLogPath "C:\DB\Microsoft SQL Server\MSSQL10_50.MITCHELL\MSSQL\DATA\".

Question 2: How do I make db project to automaticaly script deployment script in such a way that it accepts user input for DefaultDataPath and DefaultLogPath without me manually changing it to include $(InputDefaultDataPath) and $(InputDefaultLogPath) variable?

回答1:

In visual studio 2010, there is a project setting called 'CommentOutSetVarDeclarations'. If you check this option, the two default SETVAR lines you mention will be commented out.

This enables you to supply values for $(DefaultDataPath) and $(DefaultLogPath) from the command line when running the deployment script from SQLCMD.