title: Setting DefaultDataPath and DefaultLogPath programmatically (Using SQL Statements to initialize the path)
body: <p>My Query is regarding using NOT hard coded File Locations to initialize the the Variables DefaultDataPath and DefaultLogPath. Prior to adopt Database Projects as our standard Deployment and Database Management Tools and migrating our existing Scripts to Database projects we have been using the SET of CREATE and INITIALIZE scripts for Setting up Database. We are having following SQL Query to CREATE the Database with the FILE location:</p>
  
  <pre><code>SET @data_path = (SELECT SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1) 
  
            FROM sys.sysaltfiles WHERE dbid = 1 AND fileid = 1);
  
  set @mdb_file=@data_path + 'CF_DB.mdf'
  set @cfdata='CF_DB_Data'
  set @cflog='CF_DB_Log'
  set @ldf_file=@data_path + 'CF_DB_log.ldf'
  declare @sql nvarchar(500)
  set @sql = 'CREATE DATABASE [CF_DB]  ON (NAME = ' + quotename(@cfdata) + ',FILENAME =' + quotename(@mdb_file) + ',SIZE = 53, FILEGROWTH = 10%) LOG ON (NAME =' + quotename(@cflog) + ',FILENAME = ' + quotename(@ldf_file) + ', SIZE = 31, FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'
  exec(@sql)
  </code></pre>
  
  <p>Here we are trying to figure out the location of MDF file for MASTER DB and using the same location to CREATE DATABASE.</p>
  
  <p>Problem: With the scripts generated (after Deploy action) , there is an auto Generated SQLCMD variables , initialized with some default path (hardcoded one ) or Empty strings (which are using Default Datafile path used by SQL Server 2008 or 2005).</p>
  
  <pre><code>:setvar DatabaseName "CF"
  :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER2008\MSSQL\DATA\"
  :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER2008\MSSQL\DATA\"
  </code></pre>
  
  <p>We need to make it work like our existing system. We need to know path of MASTER DB data and log files and using the same path to initialize DefaultDataPath and DefaultLogPath. We can't go with PreDeployment scripts because Database settings are done by Database Project generated script before embedding PreDeploymentScript in the final Deploy Scripts.</p>
  
  <p>NEXT big thing: Developer need to switch to SQLCMD Mode in SQL Server Management Studio to run the scripts generated by DB Project. This is our implementation Team's requirement NOT TO USE SQLCMD mode to setup DATBASE. To overcome these step, I need to modify the generated SQL file and use SQL Variables instead of SQLCMD variables. Can we generate the clean SQL Statements and keeping automation script generation intact? I know both of these issues are corelated thus the solution for one is going to Fix the other one.</p>
  
  <p>Thanks for any good suggestions or help upon the above discussions.</p>
  
  <p>Regards</p>
  
  <p>Sumeet</p>