Using SQL Backup in 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
Source sql central
