SQL Server Run migration script every time

pzfprimi 于 2023-08-02 发布在其他

关注(0)|答案(4)|浏览(137)

I have a particular DB script that needs to run as the last migration script and it needs to run every time I run migrations on a DB (even if it's been run before).

I can use a very large migration ID to ensure that this migration runs last. However, I don't know what to do to force re-running this migration every time: once it runs, it gets added to the dbo.VersionInfo table and the next time FluentMigrator sees it in that table, it doesn't run it again.

[Migration ( 209912312359 )]
public class M209912312359 : Migration
{
    public override void Down ()
    {
        // I want this particular migration to always run
        // as the last migration script and to run every time.
    }
}

Is there some kind of migration attribute that tells FluentMigrator to run this script every time regardless of previous runs? (The Migration base class doesn't have anything to override for this.)

I'm using FluentMigrator 1.4.

Edit:

This is a data migration that cleans up some data in the database. At work we have a large number of databases (same schemas, different data). One particular table (let's call it dbo.A ) must have the same data in the same order in all databases. We add data to this table using migrations but sometimes - depending on what migrations run in a particular DB - instances of dbo.A may get out of sync.

The purpose of this migration is to make sure all instances of dbo.A contain the same data in the same order. We cannot drop dbo.A and just recreate it because its ID column is used as a foreign key.

I'd like to have a migration solution, if possible, because migrations are the only thing that are guaranteed to run in all of our environments as part of deployment and changing this would be very hard. (If FluentMigrator cannot do this, we probably don't have a choice.)

sql-server

来源：https://stackoverflow.com/questions/30487581/run-migration-script-every-time

4条答案

按热度按时间

8cdiaqws1#

Just use the [Maintenance(MigrationStage.AfterAll)] attribute as below and it will always run the Up() method:

using FluentMigrator;
using System;

namespace Example
{
    [Maintenance(MigrationStage.AfterAll)]
    public class RefreshAllViews : FluentMigrator.Migration
    {
        public override void Up()
        {
            Execute.Sql(@"
                DECLARE views_cursor CURSOR FOR
                SELECT name FROM sysobjects WHERE type = 'V' AND uid = 1
                OPEN views_cursor

                DECLARE @view NVARCHAR(500)
                FETCH NEXT FROM views_cursor INTO @view
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    BEGIN TRY
                        EXEC sp_refreshview @view
                    END TRY
                    BEGIN CATCH
                        PRINT 'VIEW NAME: ' + @view + 
                              ', ERROR NUMBER: ' + Cast(ERROR_NUMBER() as VARCHAR) + 
                              ', ERROR MESSAGE: ' + ERROR_MESSAGE()
                    END CATCH
    
                    FETCH NEXT FROM views_cursor INTO @view
                END

                CLOSE views_cursor
                DEALLOCATE views_cursor");
        }

        public override void Down()
        {
            throw new NotImplementedException();
        }
    }
}

赞(0）回复(0）举报 2023-08-02

bfnvny8b2#

To solve your problem I just created an abstract class that implements the method Down and inherit this on your new Migrations classes.

public abstract class MigrationBase : FluentMigrator.Migration
{
    public override void Down()
    {
        //Do what you want for every migration
    }
}

[FluentMigrator.Migration(209912312359)]
public class M209912312359 : MigrationBase
{
    public override void Up()
    {
        //New migration...
    }
}

赞(0）回复(0）举报 2023-08-02

0aydgbwb3#

Seems that you either have to use the MaintenanceAttribute or do something else with the Execute.Sql command, where it would delete the record of the migration from the VersionInfo table.

赞(0）回复(0）举报 2023-08-02

vsdwdz234#

When FluentMigrator runs using runner.MigrateUp(); it looks in the DB for the VersionInfo table to see what version it's up to - then uses this to determine which of the migration scripts to run.

If you want to create a situation where one migration script runs every time - you'll need to remove that version from the VersionInfo table before you next run runner.MigrateUp(); I managed to achieve this by adding two migrations that run at the end of FluentMigrator's runtime - clearing the VersionInfo table. There needs to be two migrations here, otherwise the version number would exist sometimes, preventing it from executing.

If you only want a specified Migration({your migration}) to re-run every time, just remove that in the VersionInfo table, rather than all the records, like I am.

using FluentMigrator;

namespace dal.FluentMigrator
{
    /* 
     * Utilised to ensure the database always clears its version info after each run 
    */

    [Migration(2147483647)]
    public class ResetVersionInfoFinish : Migration
    {

        public override void Up()
        {
            Delete.FromTable("VersionInfo").AllRows();
        }

        public override void Down()
        {
            Delete.Table("VersionInfo");
        }
    }

    // Ensures we always finish on a ResetVersionInfoFinish
    [Migration(2147483646)]
    public class ResetVersionInfoFinishReserve : Migration
    {

        public override void Up()
        {
            Delete.FromTable("VersionInfo").AllRows();
        }

        public override void Down()
        {
            Delete.Table("VersionInfo");
        }
    }
}

It should be said that this goes against how FluentMigrator's supposed to be used - which is meant to increment with Migration versions.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Run migration script every time

4条答案

相关问题

热门标签

最新问答