TFS 2015 vNext build execute SQL scripts from folder aganst databese

Question

I need to create integration checking build that also checks if update skripts doesn't throw errors when executed against database (microsoft sql server 2008 r2)
I cant figure out how to execute all SQL files from specific folder to test database?
Any suggestions how to do that?

In our old TFS 2010 someone already created libraries for that and configurated in MSbuild like this.

<Project DefaultTargets="DesktopBuild" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="3.5">
    <UsingTask TaskName="PP.Builder.VersionUpdateTask" AssemblyFile="PP.Builder.dll"/>
    <UsingTask TaskName="PP.DbUpdater.Library.DbBuildTask" AssemblyFile="PP.DbUpdater.Library.dll"/>
    .....
    .....
  <Target Name="AfterGet">
     <Exec Command="$(TF) get /noprompt /all $(AssemblyInfoFile)"/>
  </Target>

  <Target Name="AfterCompile">
     <DbBuildTask DbServer="TestServer\sql2008r2" DbBackupPath="c:\beckUp backups" DbName="empty" ScriptsPath="$(SolutionRoot)\Proj\dev\PP.Veyron.Db" UpdateVersionNumber="false" UpdateVersionNumberYesterday="false" TestOnly="true" SkipDbBackup="false"/>
    </Target>
</Project>

Any ideas how to transfer this to TFS2015 vNext build system?

Answer 1

Powershell scripts can be used to execute SQL Scripts. So you can add a Powershell script step in your build template, the script should look like:

#Provide SQLServerName
$SQLServer ="MySQLSErver\InstanceName"
#Provide Database Name 
$DatabaseName ="Test"
#Scripts Folder Path
$FolderPath ="C:\MyScripts\Test\"

#Loop through the .sql files and run them
foreach ($filename in get-childitem -path $FolderPath -filter "*.sql")
{
invoke-sqlcmd –ServerInstance $SQLServer -Database $DatabaseName -InputFile $filename.fullname
#Print file name which is executed
$filename 
} 

Refer to blog: http://www.techbrothersit.com/2015/08/how-to-execute-sql-files-from-directory.html