SSDT Publish Script Output

Question

When doing a publish from visual studio or from within Team Build the resulting *.publish.sql file contains error in the Data File naming.  Is there a way to stop the script from naming my Data file and Log file *_Primary.mdf and *_Primary.ldf respectively?

Shouldn't the Data file and Log file be _Data.mdf and _log.ldf respectively?

Output of script as is now:

CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Answer 1

Hello Ryan

You can control the file names explicitly by adding files to your project. The downside of this solution is that you now need to model the files in your project instead of having SSDT manage these for you. The script below illustrates how to do this:

ALTER DATABASE [$(DatabaseName)] 
ADD FILE (  NAME = [SqlFile1],  FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_Data.mdf'    ) 
TO FILEGROUP [Primary]  
GO
ALTER DATABASE [$(DatabaseName)] 
ADD LOG FILE (  NAME = [SqlFile2],  FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_Log.ldf'   )

This will result in the following script being generated when you publish:

PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON
     PRIMARY(NAME = [SqlFile1], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_Data.mdf')
     LOG ON (NAME = [SqlFile2], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_Log.ldf') 
COLLATE SQL_Latin1_General_CP1_CI_ASGO

Answer 2

Lonny,

Thanks for the feedback solution.  I did end up doing something to this.  I added a filegroup script for creating the Log and data file and passed in the required parameters.  Although this does add another point to manage, I am ok with this solution for now until the SSDT tools change how they are naming things.

---

Ryan Perlman (MSFT)