How to set up a local SQL Server instance for Azure DevOps Pipeline

Question

For context: I am currently tasked with creating a pipeline that tests the application for my work. The application is coded in C# and uses a local database instance of a SQL Server.

Part of our solution is a tool to reset the database with all needed data and our application needs input from the database on startup. Part of the application is a supporting tool, that sets up the database with all important data via a .bak file and some SQL scripts.

For testing we are using AzureDevops pipelines. Currently we are able to cover all our needs via the hosted agents in a 'windows-latest' vm.

So to my question:

To fully test the application, I would need to create a SQL Server instance within the VM of the pipeline. But so far I only found information on how to connect to an external server or how to install it manually, so not really any approach for what I am trying to create. Is creating a local instance within the pipeline VM such a bad idea that nobody covers it? How do I go about doing this? Any help would be greatly appreciated.

Currently the application is tested manually with a local SQL Server 15.0.2130 instance. We use SSMS to view and test SQL-scripts. The instance is setup via SQL Express.

Update:

Trying to answer the first comment, I found SQL Server Express LocalDB as mentioned in this question. This seems promising so far, I will update on whether this solved my problem or not.

You could improve your question be specifying the exact edition and version of SQL Server you're attempting to use. e.g.: you mention "local database instance" but do you actually mean the special "SQL Server Express LocalDB" edition? If so, that only works on Windows platforms so you couldn't use it in a Linux VM. — AlwaysLearning
– AlwaysLearning, Commented Jun 24 at 7:13
I hope the edit answers your questions. The application only targets windows systems, so compatibility to linux or maxOS is not important — MaxH
– MaxH, Commented Jun 24 at 7:20
I don't know much about Azure pipelines, but we do exactly that via github... we just fire up a container with SQL Server and seed it with data. This might be what AlwaysLearning was eluding to, most docker containers are linux... even if you end up running your solution on windows when deployed. — Dale K
– Dale K, Commented Jun 24 at 8:00

MaxH · Accepted Answer · 2025-06-25 07:25:56Z

The code with SQL Server Express LocalDB worked, this is the resulting entry in the .yml file

# Start local SQL Server instance
- task: CmdLine@2
  displayName: 'Start mssqllocaldb $(dbInstanceName)'
  inputs:
    targetType: 'inline'
    script: |
        sqllocaldb create "$(dbInstanceName)" 15.0.2130
        sqllocaldb share "$(dbInstanceName)" "$(dbInstanceShared)"
        sqllocaldb start "$(dbInstanceName)"
        
        setlocal enabledelayedexpansion
        for /f "delims=" %%a in ('sqllocaldb info $(dbInstanceName)') do set "ret=%%a"
        set "temp=!ret:*: =!"
        for /f "delims=" %%i in ("!temp!") do endlocal & set "instance=%%i"
        
        sqlcmd -S %instance% -q "CREATE LOGIN $(dbUser) WITH PASSWORD = '$(dbPassword)'"
        sqlcmd -S %instance% -q "CREATE USER $(dbUser)"

sqllocaldb info $(dbInstanceName) returns the instance name needed to access it with sqlcmd. The middle block of code just takes away the context information to leave me with only the result np:\\.\pipe\LOCALDB#<pipe name>\tsql\query

Now i can log in from any task with
sqlcmd -S (localdb)\.\$(dbInstanceShared) -U $(dbUser) -P $(dbPassword)
and execute any query with
sqlcmd -S (localdb)\.\$(dbInstanceShared) -Q "<query>"

And via

RESTORE DATABASE [myDB] FROM DISK = 'myDB.bak' 
WITH RECOVERY, REPLACE, 
MOVE 'myDB_Data' TO 'myDB.mdf', 
MOVE 'myDB_log' TO 'myDB_log.mdf'

i am able to restore the db and work on it

Thanks go to AlwaysLearning, since his comment enabled me to find out about the preinstalled SQL Server Express LocalDB and this related question

Collectives™ on Stack Overflow

How to set up a local SQL Server instance for Azure DevOps Pipeline

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related