Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Uruchamianie migracji bazy w Azure DevOps
- task: CopyFiles@2
inputs:
SourceFolder: '$(Build.SourcesDirectory)'
Contents: '**/$(BuildConfiguration)/**/?(*.exe|*.dll|*.pdb|*.config)'
TargetFolder: '$(Build.ArtifactStagingDirectory)/bin'
WebApiTests.Migrator.exe -c "Server=.\sqlexpress;Database=WebApiTests;Trusted_Connection=True;" -s true -p "C:\db\snapshot\WebApiTests_Snapshot.ss"
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
WebApiTests.Migrator.exe -c "$(MigrationConnectionString)" -s true -p "$(SnapshotPath)"
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();
}
}
public interface IDatabaseRestoreService
{
Result Restore(string connectionString);
Result CreateSnapshot(string connectionString, string path);
Result DropSnapshot(string connectionString);
}
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; }
}
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);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment