What are the challenges you face when working across database platforms? Take the survey
Options

Database considered not empty while deploying to a newly created empty DB

robertopescerobertopesce Posts: 4 New member
edited September 17, 2020 12:30PM in SQL Change Automation
on some systems, while I deploy the package created with SCA I get this error: the database is seen as non empty and the baseline script is not deployed... this causes a later error while deploying the first migration because of objects missing.

New-DatabaseConnection, SQL Change Automation 4.2.20133.20020, Copyright © Red Gate Software Ltd 2014-2019
Import-DatabaseBuildArtifact, SQL Change Automation 4.2.20133.20020, Copyright © Red Gate Software Ltd 2014-2019
New-DatabaseReleaseArtifact, SQL Change Automation 4.2.20133.20020, Copyright © Red Gate Software Ltd 2014-2019
Creating SQL Change Automation release artifact
Building patch for the [SACDB] database on [localhost]...
Database not considered empty. 1 object differences found
SACDB: A baseline build/deploy of the existing non-empty database will be required as the MigrationLog table doesn't exist and there are unapplied migrations. ProjectCount: 28.
If you were expecting to run the baseline scripts against the database, see http://www.red-gate.com/sca/dev/baseline-troubleshoot for information on how to set up appropriate filtering.
Generating patch script C:\Users\fish6\AppData\Local\Temp\DLM Automation\ozdha30k.ogk\artifacts\SACDB_SACDB_Patch.sql
1 migration(s) in baseline, 27 migration(s) pending deployment
  (Pre-Script) Pre-Deployment\01_Create_Database.sql
  (Mark as Deployed) Migrations\1.0.0-Baseline\001_20200515-1100_fish6.sql

<div><br></div>
I would like to know either:

1. how is SCA determining whether a DB is empty or not?
2. is it possible to force deployment of the baseline in case the __MigrationLog table is not present?
thank you
Roberto



Tagged:

Best Answer

  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    Any user objects in a database will cause SQL Change Automation to consider it not empty.
    If you want to override this behavior and still deploy the Baseline you have 2 options:

    1- User a filter file that excludes the additional object or objects (in this case this would be the local user).
    Please note that unlike with SQL Source Control projects you will not be able to apply this filter file only when deploying: you need to set this in the .sqlproj file 
    https://documentation.red-gate.com/sca/developing-databases/concepts/filtering

    2 - Pre-create the dbo.__MigrationLog table (this is the table that keeps track of the migrations that have been applied) and leave it completely empty (you can copy the structure of this table from your development database
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools

Answers

  • Options
    I did some more investigations and.... apparently if the empty DB has any user more than this list it's considered not empty... in my case I had a local user and after deleting it everything worked fine; is there any setting in the project to control this behavior?

    dbo
    guest
    INFORMATION_SCHEMA
    sys

Sign In or Register to comment.