Scripts not running/ or not in order?

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?

Answers

  • MijMij Posts: 23 Bronze 1
    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.
  • 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. 

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • MijMij Posts: 23 Bronze 1
    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.
  • 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

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • MijMij Posts: 23 Bronze 1
    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.
  • MijMij Posts: 23 Bronze 1
    Thank you for you quick responses though.
  • 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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • 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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

Sign In or Register to comment.