why is my release executing the baseline file?

tee

Project : Development DB is in an advanced state over the Target DB. The baseline is created as the existing target. But the release keeps attempting to execute the baseline file. The migrationLog for the baseline showed deployed as '0' but I set it to 1 (after the release kept failing on the execution of the baseline). Setting the baseline as deployed =1 doesn't work either. The baseline script is included in execution script. 
I have the project [x] mark first folder as baseline checked. I've clicked the [mark as deployed] button when it shows up, too. T 

Dan B

Hi Tee,

Thanks for posting on the Redgate forums.

In the target database does the dbo._MigrationLog table exist? 

If so, you can drop that table, and since the database is not empty, then SQL Change Automation will not attempt to deploy the Baseline.

tee

Thanks, Dan B. Yes, it does exist but I tried to add a filter to the project to exclude objects that begin with '_' but the filter doesn't load up in Visual Studio 2017. The instructions are simply to drop the filter file into the root of the project (same folder where the project file exists) and then unload  and load the project and the filter should load up in the project. But this isn't working (the filter file loads up in VS 2015 but not VS 2017). Have the filter instructions changed? 

I'm not understanding the logic of removing the migration_log from the target database to stop the baseline script from running. The objective of the baseline script is to run against an empty target database. If the migration_log exists on the target database doesn't that mean that the target database is NOT empty?

BTW - I'm just using a simple 3 step CI/CD method:  1 build task = "build the sql change automation project"  & 2 release tasks 1) PeformCreate & 2 ) PerformDeployFromResources. 


Thanks for any help; much appreciated.

Dan B

Hi tee,

You won't be able to affect the __MigrationLog table using a filter for a couple of reasons:

1 - Filters only work when importing and generating scripts
2 - __MigrationLog is a special object that's not scripted and it isn't affected by scripts

This is the documentation for filters, which hasn't changed in a while https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/configuring-script-generation/filtering-database-objects

This should work equally in VS2015 and 2017.

Regarding the logic of __MigrationLog, in normal circumstances when it's created the baseline will be marked as deployed (regardless whether it ran or not). This would only be a problem if an exception happnened, such as this being edited.

stevechisholm

Can you clarify the values in __MigrationLog.Deployed ? I see zeroes ones and twos...

Dan B

Hi stevechisholm,

For the _MigrationLog the 

The user friendly column here is script_filename
The deployed column indicates whether the script is marked as deployed or not in the database. 0 is not deployed and 1 is deployed.

stevechisholm

But I saw a isDeployed value of 2...

Dan B

Hi stevechisholm,

So, _MigrationLog.Status column has the following meanings:
0 = migration was not deployed, e.g. a button "Mark as deployed" was clicked in the UI
1 = migration was deployed
2 = migration was imported to the project and no changes in the database were necessary. The most common scenario is when a developer makes some changes directly in the database, and then click the Refresh button in SCA VS, followed by an Import and Generate Script.

stevechisholm

Super helpful Dan!
So,  it'll insert a record into _migrationlog when I click "mark as deployed" in the project, just to ensure that the migrationID is tracked, but the zero means no actual action is executed on the target DB.  1 is what one would expect, and 2 typically denotes when the changes are flowing the other direction:  from my development work in my database into scripts in my project.  Looking now at my recent log records, it makes perfect sense.  Thanks so much!

asadc

I'm also having an issue with the baseline script. I had an existing database in DEV, UAT and PROD.

When I added the Redgate SQL project I selected DEV as the baseline. When I deployed to UAT I was expecting only the migration scripts to be executed given that the database already existed, but it executed the baseline script which meant changes were made that were not wanted.

How can I avoid this problem? I only want the baseline to be executed if the database doesn't already exist.

sasankj

What should I mark a script's deployed column value in my source DB so that it doesnt get executed on my target DB? I tried 0,1,2...none of them worked...i dont want to delete the script from my VS project.

tee

Dan B. While I don't understand the logic. I followed your suggestion to remove the target migration objects from a target database and the baseline stopped deploying.

Truly I don't comprehend the logic, but, thank you for your valuable help in this forum.