Scripts not running/ or not in order?

Mij

Hi, I went back to an older SQL Change Automation project that I thought was running last time.  I had tried to turn off programmable objects because they are included in the baseline and the migrations.
This time I drop database, create database, and try to run the 20 scripts from SSMS project starting with baseline through migrations.  I receive an error that a migration script is erroring because there is no table.  When I look, the baseline has not run and none of the tables or objects have been created.  I'm not sure why not running all scripts or not running in order?

Mij

I guess I should say that the baseline script is the first script in the Migrations folder as folder 1.0.0-Baseline with just that one item.

DanC

Hi @Mij

Can I double-check the database you're executing the scripts against, is it completely empty?

The baseline won't be executed if there are existing objects in the database. 

Mij

As I said, I freshly dropped and created the database and then I tried to run the scripts.  What else would you be looking for?  If there are gMSA accounts or users that somehow came back, would that affect it?  I haven't added anything to the database after I recreated it.

DanC

Hi @Mij

Can I ask how you're executing these? are you using PowerShell and executing the project against the target database?

You could try modifying my script here and this will output all the debug information as it tries to run the operation:

$DebugPreference= 'Continue'

$VerbosePreference= 'Continue'

 

$project = "C:\Users\Dan.Calver\Downloads\Ticket_162740\AzureDatabase\AzureDatabase.sqlproj"

 

$temporaryDatabase = New-DatabaseConnection -ServerInstance "PS-DANC\DC_SQL2019" -Database "TemporaryDb1"

 

$Target = New-DatabaseConnection -ServerInstance "PS-DANC\DC_SQL2019" -Database "AzureDatabase"

$validatedProject = $project | Invoke-DatabaseBuild -TemporaryDatabase $temporaryDatabase

$databasePackage = $validatedProject | New-DatabaseBuildArtifact -PackageId 9999 -PackageVersion 1.0.0

 

$databasePackage | Export-DatabaseBuildArtifact -Path "c:\Packages"

 

$package = "C:\Packages\9999.1.0.0.nupkg"

 

$update = New-DatabaseReleaseArtifact -Source $package -Target $Target

 

Use-DatabaseReleaseArtifact $update -DeployTo $Target

Mij

I haven't used Powershell.   I'd have to see what to do with this.  Basically, I open SSMS, drop the database, recreate the database, and then open the SCA project and see that there are 20 scripts to be applied.  I click the "Apply/All" button.  It errors on the later migrations because the baseline has not run to populate with tables and objects even though that is the first migration script.

Mij

Thank you for you quick responses though.

DanC

Mij said:

I haven't used Powershell.   I'd have to see what to do with this.  Basically, I open SSMS, drop the database, recreate the database, and then open the SCA project and see that there are 20 scripts to be applied.  I click the "Apply/All" button.  It errors on the later migrations because the baseline has not run to populate with tables and objects even though that is the first migration script.

Ah, that makes more sense, apologies was trying to wrap my head around how you were performing this task!

I haven't actually tested this behaviour, so I'm going to try this myself and perhaps query the devs, I'll let you know once I have more details!

DanC

Hi @Mij

If it's okay, I'm going to reach out via a support ticket as the developers have asked for the project to review and see what's going on!