Generating Migration ID for custom migration script

We are not quite ready yet to use the Include Table Data option in SCA, but I do have some scripted data changes that I would like to deploy using SCA.  My plan is to just add the script as the next numbered migration file.  My question is if the Migration ID in the comment at the top of the generated migration files is simply a GUID.  Can I just include a new GUID at the top of my custom migration file and have SCA pick it up appropriately for deployments?

Thanks,
Jay
Tagged:

Best Answer

Answers

  • danielpottengerdanielpottenger Cambridge, UK Posts: 7 Bronze 1
    Hey @jaydarrenmiller ,

    The migration scripts support data insertion, and you should be able to do this just fine, as long as the migration scripts have a valid GUID at the top.

    You're also free to rename the scripts, as long as they contain the numeric prefix (i.e 001, 002, 003), as SQL Change Automation uses the prefix to determine the execution order.

    Once you've applied your scripts, you can check that the __MigrationLog table within your database contains the GUID that you've used, and that both your development database, and your shadow database contain the data as expected.

    For a little bit more information on this, and some potential alternatives, you might find the following link a good source:
    https://documentation.red-gate.com/sca/developing-databases/concepts/data-population/strategies-for-data-population.

    If you need any more help, feel free to reach out,

    Many thanks,
    Dan

  • Thanks Dan, this is what I needed for now.  We have been using SCA for schema and are now starting to look at using it for some data as well.  How would I go about baselining the data in a project that we already have a baseline for the schema?
  • danielpottengerdanielpottenger Cambridge, UK Posts: 7 Bronze 1
    Hey @jaydarrenmiller ,

    If a baseline script has already been generated for your schema and applied to your production databases, you can add another one within the baseline folder.

    If you do this directly using our tool, it should also generate you a file name for the migration (i.e 002_20201006-1620_Joe.Bloggs.sql), and a migration id within the file.

    From in SSMS, on the migrations tab, right click the baseline folder and click 'Add migration'.
    From within Visual Studio, right click the baseline folder, then 'Add' -> 'New Item'.

    You can add any of the statements you need to run,  to the new baseline migration script, and then when you deploy the project to your production databases, the new baseline script should run (which can be verified by checking the migration log table).

    Many thanks,
    Dan
Sign In or Register to comment.