How To: Support NOINIT for SqlServer Backups

The default backup process for Liquibase Enterprise SqlServer databases uses the following backup logic:

BACKUP DATABASE [${databaseDef.databaseName}] TO DISK = N'${filename}' WITH INIT, COPY_ONLY, NOUNLOAD, NAME = N'${databaseDef.databaseName} backup', NOSKIP, STATS = 10, NOFORMAT

The code includes the WITH INIT parameter so that backups taken by Liquibase Enterprise overwrite the previous backup taken during the packager process.

If your system does not support WITH INIT logic, a custom backup and restore script may be used to perform the backup. The below article describes how to setup this custom backup.

Potential errors from WITH INIT

Customers whose database configuration does not support WITH INIT may see an error such as:

The backup cannot be performed because 'INIT' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'INIT' or specify 'FORMAT'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Instructions

1. Download the custom script here. This script has modified logic:

   1. Uses the following backup command including WITH NOINIT logic:

      BACKUP DATABASE [${databaseDef.databaseName}] TO DISK = N'${filename}' WITH NOINIT, COPY_ONLY, NOUNLOAD, NAME = N'${databaseDef.databaseName} backup', NOSKIP, STATS = 10, NOFORMAT

   2. Includes code to delete any existing backups prior to taking a new one:

      def connection
      def sql
      (connection, sql) = scriptUtils.openConnection(daticalDBHelper, databaseDef)
      	
      boolean success = scriptUtils.tryExecuteSql(sql,
         "EXECUTE master.dbo.xp_delete_file 0,N'${fullFilename}'",
         "Deleting existing backup")

2. The code to delete any existing backups requires SYSADMIN role. Ensure your Liquibase user has the SYSADMIN role.

3. There are two ways to house the custom script. The script can be housed per project (Method A) or in the Liquibase Enterprise installation directory (Method B).

   1. Method A - house script per project

      1. Create a folder in the DDB project repo called BackupRestoreScripts

      2. Place custom script in this folder

      3. In the deployPackager.properties add

         extensionsPath=./BackupRestoreScripts
         databaseBackupRestoreMethod=CustomBackupRestoreSqlServer

   2. Method B - house script in the Liquibase installation directory

      1. Place script in the <Liquibase Enterprise install directory>/repl/scripts/extensions directory.

      2. In the deployPackager.properties add

         databaseBackupRestoreMethod=CustomBackupRestoreSqlServer

Testing the custom script

The custom script can be tested by running the Packager operation with a sample SQL script. Note: the backup and restore will not be triggered without a sample SQL script to be packaged.

There is also a test harness for testing backup and restore.

1. From the DDB project root folder run:

   hammer groovy database_backup_restore.groovy <refDb name> backup

2. The restore can also be tested using:

   hammer groovy database_backup_restore.groovy <refDb name> restore