Using SQL Backup in Flyway Development Work
For a development team, SQL Backup provides a simple way to restore development or test databases, if required, while minimizing the tedium of the task of taking, managing, and restoring the backups. We can generate the required backup commands in the GUI and then use them to construct a SQL script that backs up all the required databases. We can even use the backup commands in a beforeMigrate SQL callback, in Flyway, to make sure the current version is safely backed up before we run migration.
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
/** Summary: > This is a batch using the DATABASES list with SQL Backup that does a backup of every database on the server with the simple recovery model that isn't a system database. The filter in the SQL that creates the list can be simply altered to include and exclude databases. Author: Phil Factor Date: Wednesday, 14 September 2022 **/ USE master; SET NOCOUNT ON; DECLARE @backupstring NVARCHAR(4000), @DBNAME sysname; /* insert the backup string generated by the SQL Backup GUI here, but use the ##list## placeholder instead of the list of databases */ SET @backupstring = N'-SQL "BACKUP DATABASES [##list##] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\<AUTO>.sqb'' WITH PASSWORD = ''<OBFUSCATEDPASSWORD>DT678U12upsc576Nmm</OBFUSCATEDPASSWORD>'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3, COPYTO = ''<MyNetworkDrive>'', KEYSIZE = 256, THREADCOUNT = 2"'; DECLARE @exitcode INT; --output variable DECLARE @sqlerrorcode INT; --output variable /* we use a string to list all the databases that we want to back up */ DECLARE @TheDatabaseNames NVARCHAR(4000); -- insert the names into the tables that wee use as the ordered list SELECT @TheDatabaseNames = Coalesce (@TheDatabaseNames + ',', '') + name FROM sys.databases WHERE -- name NOT IN ('Dave', 'Dee', 'Dozy', 'Beaky', 'Mick','Tich') AND --for example -- name NOT LIKE '%Redgate%' AND --just an example database_id > 4 --not a system database AND recovery_model = 3 --simple recovery model AND state = 0 --online ORDER BY name ASC; SELECT @backupstring = Replace (@backupstring, '##list##', @TheDatabaseNames); PRINT 'Backing up ' + @DBNAME; --now we execute EXEC master..sqlbackup @backupstring, @exitcode OUTPUT, @sqlerrorcode OUTPUT; IF (@exitcode <> 0) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode); END; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
/** Summary: > This is a batch that does a backup of every database with the simple recovery model that isn't a system database. The filter can be simply altered to include and exclude databases. Author: Phil Factor Date: Wednesday, 14 September 2022 **/ USE master; SET NOCOUNT on DECLARE @backupstring VARCHAR(4000), @CurrentBackupString VARCHAR(4000), @DBNAME sysname; /* insert the backup string generated by the SQL Backup GUI here, but use the ##database## placeholder instead of the name of the database. This will have the name of each database substituted into this template to create the SQL statement that is executed. You need to specify your network location before you use this command where I have put <MyNetworkLocation> */ SET @backupstring='-SQL "BACKUP DATABASE [##database##] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\<AUTO>.sqb'' WITH FILEOPTIONS = 4, PASSWORD = ''<OBFUSCATEDPASSWORD>DT678U12upsc576Nmm</OBFUSCATEDPASSWORD>'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''<MyNetworkLocation>'', INIT, KEYSIZE = 256, THREADCOUNT = 2"' DECLARE @exitcode INT; --output variable DECLARE @sqlerrorcode INT; --output variable /* we create a temporary table to list all the databases that we want to back up */ DECLARE @TheDatabaseName TABLE (TheOrder INT IDENTITY, TheName sysname NOT NULL); -- insert the names into the tables that we use as the ordered list INSERT INTO @TheDatabaseName (TheName) SELECT name FROM sys.databases WHERE database_id > 4 --not a system database AND recovery_model = 3 --simple recovery model AND state = 0 --online ORDER BY name ASC; --the number in the list DECLARE @iiMax INT = (SELECT Max (TheOrder) FROM @TheDatabaseName), @ii INT = 1; WHILE @ii <= @iiMax --work through the list BEGIN SELECT @DBNAME = TheName FROM @TheDatabaseName WHERE TheOrder=@ii; SELECT @CurrentBackupString = Replace(@BackupString,'##Database##', @DBNAME) PRINT 'Backing up '+ @DBName --now we execute EXEC master..sqlbackup @CurrentBackupString, @exitcode OUTPUT, @sqlerrorcode OUTPUT; IF (@exitcode <> 0) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode); END; SELECT @ii = @ii + 1; -- and on to the next one END; |
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?”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
DECLARE @DatabaseVersion nvarchar(20)=N'NoVer'; IF (Object_Id('${flyway:defaultSchema}.${flyway:Table}') IS NOT NULL) SELECT @DatabaseVersion=CASE WHEN ( -- check whether we are dealing with an UNDO SELECT type FROM ${flyway:defaultSchema}.${flyway:Table} WHERE installed_rank = (SELECT Max(installed_rank) FROM ${flyway:defaultSchema}.${flyway:Table} WHERE success = 1) ) LIKE 'UNDO%' /* IF so then we need to get the next version before the UNDO version */ THEN -- if it is an UNDO (SELECT TOP 1 Version FROM ${flyway:defaultSchema}.${flyway:Table} where version is not NULL -- probably a pesky repeatable and success = 1 AND TYPE NOT LIKE 'UNDO%' AND installed_Rank<( /*the latest migration that is lower then the version quoted in the UNDO operation which is actually the version you are undoing */ select min(installed_rank) FROM ${flyway:defaultSchema}.${flyway:Table} where version = ( Select version FROM ${flyway:defaultSchema}.${flyway:Table} WHERE installed_rank = (SELECT Max(installed_rank) FROM ${flyway:defaultSchema}.${flyway:Table} WHERE success = 1 ) ) and success = 1 ) ORDER BY installed_rank desc ) ELSE -- then it is simple (Select version FROM ${flyway:defaultSchema}.${flyway:Table} WHERE installed_rank = (SELECT Max(installed_rank) FROM ${flyway:defaultSchema}.${flyway:Table} WHERE success = 1 ) ) END DECLARE @backupstring NVARCHAR(4000); /* insert the backup string generated by the SQL Backup GUI here, but use the ${flyway:database} placeholder */ SET @backupstring = N'-SQL "BACKUP DATABASE ${flyway:database} TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\<AUTO>_' + @DatabaseVersion + N'.sqb'' WITH FILEOPTIONS = 4, PASSWORD = ''<OBFUSCATEDPASSWORD>Sze2ZdjGb2bgve00qg==</OBFUSCATEDPASSWORD>'', CHECKSUM, ' + N'DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''\\PhilsNetwork\databaseBackups\Philf01'', INIT, KEYSIZE = 256, THREADCOUNT = 2"'; DECLARE @exitcode INT; --output variable DECLARE @sqlerrorcode INT; --output variable /* we use a string to list all the databases that we want to back up */ EXEC master..sqlbackup @backupstring, @exitcode OUTPUT, @sqlerrorcode OUTPUT; IF (@exitcode <> 0) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode); END; |
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.