Using a DB File to run Tests on Azure Devops

Question

Is there a way to create a DB File on the Azure Devops pipeline using one of the tasks?

My line of thought is Create a localdb on the agent (Using VS) and run the unit tests (SSDT) on that DB file like I do with VS. I can create db file Tools>connect db> Sql server db file and putting in a name. I can connect to it and run the tests. It seems like I cant do this on Azure devops pipeline.

I know the preferred way is to allocate an Azure SQL server and run the tests against those but the DB is very small and if i can run those against the db file it seems like a better idea.

user_9278080 · Answer

Basically I found a way to do it all on the agent. However, the agent localdb has to be updated if you are using newer syntax.

- task: CopyFiles@2
  inputs:
    Contents: '**/Output/*.dacpac'
    flattenFolders: true
    TargetFolder: '$(Build.ArtifactStagingDirectory)'

- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: |      
      sqllocaldb start MSSQLLocalDB
      sqllocaldb info MSSQLLocalDB

      #import SqlServer module
      Import-Module -Name "SqlServer"

      # create variable with SQL to execute
      $sql = "
      CREATE DATABASE [MyDatabase]
        CONTAINMENT = NONE
        ON  PRIMARY
        ( NAME = N'MyDatabase', FILENAME = N'd:\a\1\s	esting.Data	est\bin\Output\MyDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 262144KB )
        LOG ON
        ( NAME = N'MyDatabase_log', FILENAME = N'd:\a\1\s	esting.Data	est\bin\Output\MyDatabase_log.ldf' , SIZE = 524288KB , FILEGROWTH = 131072KB )
      GO

      USE [master]
      GO
      ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
      GO

      ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
      GO "
      Invoke-SqlCmd -ServerInstance "(localdb)\MSSQLLocalDB" -database master -Query $sql

- task: SqlDacpacDeploymentOnMachineGroup@0
  inputs:
    TaskType: 'dacpac'
    DacpacFile: '$(Build.ArtifactStagingDirectory)/*.dacpac'
    TargetMethod: 'connectionString'
    ConnectionString: 'Data Source=(localdb)\.;Initial Catalog=MyDatabase;Integrated Security=True;'

With this you can attach a created .mdf file that was generate to your localdb and publish your dacpac to it. Then if you want to run your tests, you can do so.

user_7460777 · Answer

Using a DB File to run Tests on Azure Devops

As workaround, you could try to check-in the LocalDb files (mdf and ldf), copy the files to output and change the connection string to use the current execution path:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
     optionsBuilder.UseSqlServer($”Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=Contacts;AttachDbFilename={AppDomain.CurrentDomain.BaseDirectory}Core\IntegrationTests\Contacts.mdf;Integrated Security=True”);
}

You could check the document Integration Testing with SQL LocalDb on your build server for some details.

Hope this helps.

Using a DB File to run Tests on Azure Devops

2 Answers