Phil Factor

30 September 2022

• Flyway
• Database Build tasks
• Callbacks and placeholders

Using SQL Backup in Flyway Development Work

What’s SQL Backup?

I barely raised an eyebrow, recently, when one of my database servers died. Why? I knew all my databases and their data were safely backed up, using SQL Backup. As it turned out, it was just an expired power supply, but I only found that out and rectified it days later. In the meantime, the service was resumed on another server within minutes.

SQL Backup isn’t an exciting topic for an article because it just works and works well. Although it is in its element managing backups for a host of operational SQL Servers, it is just as useful for development work. With Flyway, I take a compressed backup of the development databases before a migration purely by executing a SQL statement in a beforeMigrate callback. This SQL statement contains all the information required for the task and calls the SQL Backup extended stored procedure to take the required backups. It hardly takes a moment, and then, if anything goes seriously wrong, I can do a restore, which takes around the same time as an undo.

As I’m a developer by inclination, rather than a database administrator, I’d much rather leave a tool to do the boring jobs like backups, backup management, and log shipping. The great virtue of SQL backup is that it just gets on with these tasks, with minimal supervision. You define which types of backups you need, set the backup schedule, and then let the tool take over. It will automatically compress and encrypt the backups, run backup checks, write the backup archive to a network location, as well as cloud storage, and send an email notification when it’s finished. It takes care of the business of managing backup retention so that your storage is not overloaded with old, redundant backups. You can schedule test restores, as well as backups. If you need Log Shipping, it will do that for you too. It can even do an object-level recovery if you have to restore just a table or so. It is just a shame that there isn’t s similar tool for MySQL!

Aha, you might be thinking, but what if I need to do an urgent restore of one of SQL Backup’s proprietary compressed and encrypted backup files (.sqb) at a time or place where I’ve no license to use SQL Backup? This is no sweat at all: you can simply convert it to a standard .mtf file, via a free utility (Tools | Utilities | SQL Backup File Converter, in the GUI) and then restore it. You can even store the utility with the backups in case you are of a nervous disposition.

How does SQL Backup work?

SQL Backup works by executing an extended stored procedure in SQL Server that must be installed into each instance of every database server. The advantage this gives is that backups and restores can then be arranged purely with SQL calls. All the processes involving creating the backups files, or copying them into cloud or offsite storage, is handled by the procedure.

The SQL command for running backups or restores, using the sqlbackup extended stored procedure, is just an extension of the native SQL Server Backup or Restore command, so you’ll probably know a lot of the commands already if you’re a DBA. You can set off a backup or restore from anything that can make a connection to the instance involved.

The SQL Backup GUI

Why bother with the SQL Backup GUI? The timeline is cute, of course, and it is useful for the installation of the extended stored procedure to every instance, without using the command-line. However, the serious answer to that question, from my perspective, is that you can use it to create the SQL BACKUP and RESTORE statements. You use the Backup or Restore wizard to specify what you want from the various menus, and then on the final step of the GUI wizard, which provides a summary of the backup to be performed, you can grab the string that represents the SQL that does it, from the Script tab:

You can execute this string against the SQL Server instance using your favorite way of doing so. When you are just using the GUI to do your work, then you just let the GUI do the actual backup or restore, perhaps scheduling it as a regular event.

You can set up backup reporting, via the GUI, although this isn’t the only way to do it. In theory, you could also write the backup reports to a database for custom reporting, but I couldn’t find a way to configure this so the backup system would write to this database, if your backups are done directly via a SQL statement rather than the GUI.

Doing backups in SQL

You can do a lot merely by building the SQL statement using the GUI. You can get it to do all the databases, with exceptions, or you can get it to do a list, with or without exceptions.

Other than just executing a static string from the SQL Backup GUI, probably the simplest way of specifying the databases you wish to include is to select from the sys.databases metadata table. Here, as an example, we backup all the user databases that are online and are running the SIMPLE recovery model (most development database will be operated in SIMPLE recovery):

Doing a series of backups

Sometimes, we want to do a bit more. We could, for example, save each backup to a different network address, use a different password, or add the database version into the name if there was a version number attached to the database. I’ve illustrated just the basic technique, but it is easy to add the code into the routine to include the information that you need, most usually in the part that creates the table.

Running a backup before a Flyway migration

The following script is a beforeMigrate Flyway SQL callback called beforeMigrate__SQLBackup.sql. It is written in SQL and designed to make sure that each database version is backed up before any changes are made. It adds the Flyway version number into the name of the backup file.

It uses the default placeholders to do its work. These are automatically populated before Flyway executes the callback, and their values are substituted into the code:

• ${flyway:defaultSchema} = The default schema for Flyway
• ${flyway:user} = The user Flyway will use to connect to the database
• ${flyway:database} = The name of the database from the connection url
• ${flyway:timestamp} = The time that Flyway parsed the script, formatted as ‘yyyy-MM-dd HH:mm:ss’
• ${flyway:filename} = The filename of the current script
• ${flyway:workingDirectory} = The user working directory as defined by the ‘user.dir’ System Property
• ${flyway:table} = The name of the Flyway schema history table

It is the sort of code you’d put in to satisfy the pessimists like me who always ask, after you confidently alter an important database ‘you did take a backup before you did that, didn’t you?”

When we run each migration, we even get a nice report in the command line (this is in addition to the normal screeds of information, which I’ve deleted from this output):

Executing SQL callback: beforeMigrate - SQLBackup
+-----------------------------------------------------------------------------------+
| SQL Backup v10.1.18.2060                                                          |
+-----------------------------------------------------------------------------------+
| Backing up PubsBackedUp (full database) to:                                       |
|   <mypath>\FULL_(local)_PubsBackedUp_20220915_105708_1.1.2.sqb              |
|                                                                                   |
| Database size       : 80.000 MB                                                   |
| Compressed data size: 375.000 KB                                                  |
| Compression rate    : 99.54%                                                      |
|                                                                                   |
| Processed 616 pages for database 'PubsBackedUp', file 'PubsBackedUp' on file 1.   |
| Processed 8 pages for database 'PubsBackedUp', file 'PubsBackedUp_log' on file 1. |
| BACKUP DATABASE successfully processed 624 pages in 0.527 seconds (9.240 MB/sec). |
| SQL Backup process ended.                                                         |
|                                                                                   |
|                                                                                   |
+-----------------------------------------------------------------------------------+
+--------------+--------------------------------------------------------------------+
| name         | value                                                              |
+--------------+--------------------------------------------------------------------+
| exitcode     | 0                                                                  |
| sqlerrorcode | 0                                                                  |
| filename001  | <mypath>\FULL_(local)_PubsBackedUp_20220915_105708_1.1.2.sqb |
+--------------+--------------------------------------------------------------------+

If you repeatedly run migrations, or you run a lot of migrations, you will want to switch off this report, occasionally. In this case, you just add a conditional statement for the actual execution of the code and provide a placeholder to switch the code in or out (I’ll explain this technique in more detail in another article).

Conclusions

I’ve always thought of SQL Backup as being the tool for people who aren’t interested in getting to grips with the details of all the backup knobs and dials. They are either the ‘Accidental DBA’ who hasn’t the inherent interest in ‘Tail of the Log’ backups, checksums, verifications and so on but must, when necessary, do a good job with backups and restores. It is also an ideal tool for database developers who have other things on their minds. It works obligingly, whatever your skill or knowledge level, but is about as exciting as beans on toast.