0

I have an Azure pipeline that refuses to run my integration tests (well, prior to running the tests, it must create necessary databases from .bak files - and that's the part that is failing)

This SQL is being invoked as part of my MSTest ClassInitialize routine:

IF DB_ID('saleslogix_full') IS NOT NULL
    BEGIN

      ALTER DATABASE [saleslogix_full] SET SINGLE_USER WITH
      ROLLBACK IMMEDIATE;

      DROP DATABASE [saleslogix_full];

    END

restore database saleslogix_full
from disk = 'C:\x\y\z\data-snapshots\saleslogix_full.bak' with Recovery,
move 'saleslogix_full' to 'C:\x\y\z\saleslogix_full.mdf',
move 'saleslogix_full_log' to 'C:\x\y\z\saleslogix_full_log.ldf'

The above is invoked by connecting to the 'master' datatbase of the (localdb)\MSSQLLocal server. Again, "works on my machine" running Visual Studio 2019. But, doesn't work on either of these Azure agents: windows-2019 and vs2017-win2016

Additional Details - Azure Agent Log Snippet

A total of 6 test files matched the specified pattern.
  X SeededSuccessfully_SalesforceIntegration
  Error Message:
   Class Initialization method MyCorp.Data.ReplicationWorker.Tests.Test1.ClassInit threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: Cannot create more than one clustered index on table 'dbo.sysschemaarticles'. Drop the existing clustered index 'PK_dbo.sysschemaarticles' before creating another.
Database 'saleslogix_full' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
RESTORE DATABASE is terminating abnormally.
Processed 1120 pages for database 'saleslogix_full', file 'saleslogix_full' on file 1.
Processed 2 pages for database 'saleslogix_full', file 'saleslogix_full_log' on file 1..

Now the jewel buried in the above log message is: Cannot create more than one clustered index on table 'dbo.sysschemaarticles'. Drop the existing clustered index 'PK_dbo.sysschemaarticles' before creating another

Before I go digging into how to do this, can anyone point me in a direction as to why this works on my Windows 10 box but not my Azure pipeline agent? My initial gut is to say it's a difference in "localdb" SQL editions

bkwdesign
  • 103
  • 1
  • 4
  • Ok, I'm starting to think it's due to the .bak file being specific to my box after reading [MSSQL_ENG003165 in SQL Server Books Online](https://docs.microsoft.com/en-us/sql/relational-databases/replication/mssql-eng003165?view=sql-server-ver15) – bkwdesign Jan 25 '20 at 16:51

1 Answers1

0

I ended up putting a try{...}catch{..} around my .NET code that was responsible for the bombing SQL statement.

The catch is specific to a System.Data.SqlClient.SqlException error

Within the catch, I execute this SQL:

"ALTER DATABASE saleslogix_full SET ONLINE"

And all my integration tests seem to pass against the database

bkwdesign
  • 103
  • 1
  • 4