Uruchamianie migracji bazy w Azure DevOps

build.yml

- task: CopyFiles@2
  inputs:
    SourceFolder: '$(Build.SourcesDirectory)'
    Contents: '**/$(BuildConfiguration)/**/?(*.exe|*.dll|*.pdb|*.config)'
    TargetFolder: '$(Build.ArtifactStagingDirectory)/bin'

cmd

WebApiTests.Migrator.exe -c "Server=.\sqlexpress;Database=WebApiTests;Trusted_Connection=True;" -s true -p "C:\db\snapshot\WebApiTests_Snapshot.ss"

cmd.log

2019-09-15T06:55:52.2510007Z ##[section]Starting: Run database migration
2019-09-15T06:55:52.2637001Z ==============================================================================
2019-09-15T06:55:52.2637104Z Task         : Command line
2019-09-15T06:55:52.2637193Z Description  : Run a command line script using Bash on Linux and macOS and cmd.exe on Windows
2019-09-15T06:55:52.2637265Z Version      : 2.151.2
2019-09-15T06:55:52.2637340Z Author       : Microsoft Corporation
2019-09-15T06:55:52.2637418Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/command-line
2019-09-15T06:55:52.2637511Z ==============================================================================
2019-09-15T06:55:53.5204729Z Generating script.
2019-09-15T06:55:53.5431189Z Script contents:
2019-09-15T06:55:53.5438365Z WebApiTests.Migrator.exe -c "Server=104.40.135.99;Database=WebApiTests;User Id=daniel;Password='PasswordToSqlServer';" -s true -p "C:\db\snapshot\WebApiTests_Snapshot.ss"
2019-09-15T06:55:53.5787804Z ========================== Starting Command Output ===========================
2019-09-15T06:55:53.6058032Z ##[command]"C:\windows\system32\cmd.exe" /D /E:ON /V:OFF /S /C "CALL "d:\a\_temp\942d3048-d85e-4997-8ea3-aa3becf6f5ca.cmd""
2019-09-15T06:56:00.1463923Z 2019-09-15 06:56:00.0025 INFO Restore snapshot for WebApiTests database
2019-09-15T06:56:01.4606166Z 2019-09-15 06:56:01.4271 TRACE IF DB_ID('WebApiTests_Snapshot') IS NOT NULL
2019-09-15T06:56:01.4606744Z BEGIN
2019-09-15T06:56:01.4606943Z     ALTER DATABASE WebApiTests SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
2019-09-15T06:56:01.4607153Z     RESTORE DATABASE WebApiTests FROM DATABASE_SNAPSHOT = 'WebApiTests_Snapshot';  
2019-09-15T06:56:01.4607338Z     ALTER DATABASE WebApiTests SET MULTI_USER;
2019-09-15T06:56:01.4607716Z END
2019-09-15T06:56:02.1985831Z 2019-09-15 06:56:02.1939 INFO Drop snapshot for WebApiTests database
2019-09-15T06:56:02.1986673Z 2019-09-15 06:56:02.1939 TRACE IF DB_ID('WebApiTests_Snapshot') IS NOT NULL
2019-09-15T06:56:02.1986915Z     DROP DATABASE WebApiTests_Snapshot;
2019-09-15T06:56:06.6874425Z 2019-09-15 06:56:06.6766 TRACE Target database is: 'WebApiTests' (DataSource: 104.40.135.99, Provider: System.Data.SqlClient, Origin: Explicit).
2019-09-15T06:56:07.7639754Z 2019-09-15 06:56:07.7615 INFO No pending explicit migrations.
2019-09-15T06:56:07.8954448Z 2019-09-15 06:56:07.8708 INFO Running Seed method.
2019-09-15T06:56:07.8981609Z 2019-09-15 06:56:07.8865 INFO Create snapshot for WebApiTests database
2019-09-15T06:56:07.8984340Z 2019-09-15 06:56:07.8865 TRACE CREATE DATABASE WebApiTests_Snapshot ON  
2019-09-15T06:56:07.8985341Z ( NAME = WebApiTests, FILENAME = 'C:\db\snapshot\WebApiTests_Snapshot.ss' )  
2019-09-15T06:56:07.8985500Z AS SNAPSHOT OF WebApiTests;
2019-09-15T06:56:09.8687363Z ##[section]Finishing: Run database migration

cmd.task

WebApiTests.Migrator.exe -c "$(MigrationConnectionString)" -s true -p "$(SnapshotPath)"

DatabaseRestoreService.cs

    public class DatabaseRestoreService : IDatabaseRestoreService
    {
        private static NLog.Logger _logger = NLog.LogManager.GetCurrentClassLogger();

        public Result Restore(string connectionString)
        {
            var connectionBuilder = new SqlConnectionStringBuilder(connectionString);

            var databaseName = connectionBuilder.InitialCatalog;
            _logger.Info($"Restore snapshot for {databaseName} database");

            var connectionToMasterBuilder = new SqlConnectionStringBuilder(connectionString)
            { InitialCatalog = "master" };

            using (var conn = new SqlConnection(connectionToMasterBuilder.ConnectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = $@"IF DB_ID('{databaseName}_Snapshot') IS NOT NULL
BEGIN
    ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
    RESTORE DATABASE {databaseName} FROM DATABASE_SNAPSHOT = '{databaseName}_Snapshot';  
    ALTER DATABASE {databaseName} SET MULTI_USER;
END";

                    _logger.Trace(cmd.CommandText);
                    cmd.ExecuteNonQuery();
                }
            }

            return Result.Ok();
        }

        public Result CreateSnapshot(string connectionString, string path)
        {
            var connectionBuilder = new SqlConnectionStringBuilder(connectionString);

            var databaseName = connectionBuilder.InitialCatalog;
            _logger.Info($"Create snapshot for {databaseName} database");

            var connectionToMasterBuilder = new SqlConnectionStringBuilder(connectionString)
            { InitialCatalog = "master" };

            using (var conn = new SqlConnection(connectionToMasterBuilder.ConnectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = $@"CREATE DATABASE {databaseName}_Snapshot ON  
( NAME = {databaseName}, FILENAME = '{path}' )  
AS SNAPSHOT OF {databaseName};";
                    _logger.Trace(cmd.CommandText);
                    cmd.ExecuteNonQuery();
                }
            }

            return Result.Ok();
        }

        public Result DropSnapshot(string connectionString)
        {
            var connectionBuilder = new SqlConnectionStringBuilder(connectionString);

            var databaseName = connectionBuilder.InitialCatalog;
            _logger.Info($"Drop snapshot for {databaseName} database");

            var connectionToMasterBuilder = new SqlConnectionStringBuilder(connectionString)
            { InitialCatalog = "master" };

            using (var conn = new SqlConnection(connectionToMasterBuilder.ConnectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = $@"IF DB_ID('{databaseName}_Snapshot') IS NOT NULL
    DROP DATABASE {databaseName}_Snapshot;";
                    _logger.Trace(cmd.CommandText);
                    cmd.ExecuteNonQuery();
                }
            }

            return Result.Ok();
        }
    }

IDatabaseRestoreService.cs

public interface IDatabaseRestoreService
{
    Result Restore(string connectionString);

    Result CreateSnapshot(string connectionString, string path);

    Result DropSnapshot(string connectionString);
}

Options.cs

public class Options
{
    [Option('c', "connectionString", Required = true, HelpText = "The connection string to database that needs to be updated.")]
    public string ConnectionString { get; set; }

    [Option('s', "createSnapshot", Required = false, HelpText = "Should create database snapshot after running migrations.")]
    public bool CreateSnapshot { get; set; }

    [Option('p', "snapshotPath", Required = false, HelpText = "The path for snapshot database.")]
    public string SnapshotPath { get; set; }
}

Program.cs

class Program
{
    private static DatabaseRestoreService _databaseRestoreService;

    protected static DatabaseRestoreService DatabaseRestoreService
    {
        get
        {
            if(_databaseRestoreService == null)
            {
                _databaseRestoreService = new DatabaseRestoreService();
            }

            return _databaseRestoreService;
        }
    }

    static void Main(string[] args)
    {
        var result = Parser.Default.ParseArguments<Options>(args);

        result
            .WithParsed(r => Migrate(r));
    }

    private static void Migrate(Options options)
    {
        if(options.CreateSnapshot)
        {
            DatabaseRestoreService.Restore(options.ConnectionString);
            DatabaseRestoreService.DropSnapshot(options.ConnectionString);
        }

        var configuration = new Configuration();

        configuration.TargetDatabase = new DbConnectionInfo(
            options.ConnectionString,
            "System.Data.SqlClient");

        var migrator = new DbMigrator(configuration);

        MigratorLoggingDecorator logger = new MigratorLoggingDecorator(migrator, new MigrationLogger());
        logger.Update();

        if(options.CreateSnapshot)
        {
            DatabaseRestoreService.CreateSnapshot(options.ConnectionString, options.SnapshotPath);
        }
    }
}