iamsitting

.NET On-Demand Backups

I had the joy of starting my web development career with Django. One of the most powerful features within Django is the built-in ORM. Like most ORMs it's very good at abstracting out the SQL API and able to version a database with migrations.

What made Django even more special was the Django Admin. The Admin CLI was a place where developers could run administrative tasks using a Python interpreter instance. The instance automatically imported Django tools and of course the ORM. This combination allowed developer to do data dumps which was very convenient for local development.

After several years of working on .NET Core and EF Core projects, I wanted a similar tool. This especially became more important as I was in an organization were there were dedicated DBAs for anything database related.

On-Demand Backups #

The first thing we need to tackle is how do we create database backups. Well, that's going to vary from DBMS to DBMS. For this article, we'll look at MSSQL, but it doesn't really matter.

In MSSQL, this is how we would create a backup:

			BACKUP DATABASE dbname TO DISK = '/path/to/backup.bak' WITH FORMAT

Now the trick is to wrap that around a SQL Client. Fortunately, there are several and EF Core comes with one. 

 // DbUtility.cs
public static async Task BackupDatabaseAsync(string backupLocation, string connectionString, string dbname)
{
	var filename = $"{dbname}-{DateTime.Today.ToString("d").Replace("/", "-")}.bak";
	var fullFilePath = Path.Combine(backupLocation, filename);

	using var connection = new SqlConnection(connectionString);
	await connection.OpenAsync();

	var backupCommandText = $"BACKUP DATABASE [{dbname}] TO DISK = '{fullFilePath}' WITH FORMAT;";

	using var command = new SqlCommand(backupCommandText, connection);
	await command.ExecuteNonQueryAsync();
	Console.WriteLine($"backup created at: {fullFilePath}");
}

On-Demand Restore #

Now, that we our back up code. We can implement something similar to restore from a backup.

This is how we would do it directly in MSSQL

			
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE dbname FROM DISK = /path/to/backup.bak WITH REPLACE;
ALTER DATABASE dbname SET ONLINE;

Similar to our backup wrapper method, we can create a restore wrapper method.

			 // DbUtility.cs
public static async Task RestoreDatabaseAsync(string backupFilePath, string connectionString, string dbname)
{

	using var connection = new SqlConnection(connectionString);
	await connection.OpenAsync();

	var commandText = $"ALTER DATABASE {dbname} SET OFFLINE WITH ROLLBACK IMMEDIATE; " +
						$"RESTORE DATABASE {dbname} FROM DISK = '{backupFilePath}' WITH REPLACE; " +
						$"ALTER DATABASE {dbname} SET ONLINE;";

	using var command = new SqlCommand(commandText, connection);
	await command.ExecuteNonQueryAsync();
	Console.WriteLine($"Database successfully restored from {backupFilePath}");
}

Adding as a CLI Command#

The previous methods are simply static utility methods.The last part of this endeavoer is to add them as CLI arguments commands for our application. We can do that by modifying the Program.cs file.

			// Program.cs
var shouldBackup = args.Length > 0 && args.ToList().Contains("--db-backup");
var shouldRestore = args.Length > 0 && args.ToList().Contains("--db-restore");
var connString = builder.Configuration.GetConnectionString();
if(shouldBackup)
{
	await DbUtility.BackupDatabaseAsync("/backups/backup.bak", connectionString, "LocalDb");
}
else if(shouldRestore)
{
	var argIndex = Array.IndexOf(args, "--db-restore");
	if(argIndex < args.Length - 1)	
	{
		var backupFilePath = args[argIndex + 1];
		await DbUtility.RestoreDatabaseAsync(backupFilePath, connectionString, "LocalDb");
	}
}

Now we can use the following commands right from the CLI:

			dotnet run --db-backup
			dotnet run --db-restore /backups/backup.bak