SQL Server BACKUP and RESTORE statements broken

[andrey-poltavskiy]

What version of Flyway are you using?

Flyway 4.2.0 by Boxfuse

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Command-line

What database are you using (type & version)?

Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
Jun 10 2015 03:35:45
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

What operating system are you using?

Microsoft Windows [Version 10.0.10586]

What did you do?

In sql file:
BACKUP DATABASE [POS_TEST] TO DISK = N'e:\Shared\Backup\OLCHA$SQLEXPRESS\POS_TEST\FULL\OLCHA$SQLEXPRESS_POS_TEST_FULL_20170716_212844.bak' WITH CHECKSUM, NO_COMPRESSION

After execute in Flyway:
Database: jdbc:jtds:sqlserver://192.168.20.22/POS_TEST;instance=SQLEXPRESS (Microsoft SQL Server 12.0)
Successfully validated 2 migrations (execution time 00:00.096s)
SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo
Current version of schema [dbo]: 1
Migrating schema [dbo] with repeatable migration 1600 ANGUS
ERROR: Migration of schema [dbo] with repeatable migration 1600 ANGUS failed! Changes successfully rolled back.
ERROR:
Migration R__1600_ANGUS.sql failed

SQL State : S1000
Error Code : 3013
Message : BACKUP DATABASE is terminating abnormally.
Location : C:/Flyway/POS/sql/R__1600_ANGUS.sql (C:\Flyway\POS\sql\R__1600_ANGUS.sql)
Line : 5
Statement : BACKUP DATABASE [POS_TEST] TO DISK = N'e:\Shared\Backup\OLCHA$SQLEXPRESS\POS_TEST\FULL\OLCHA$SQLEXPRESS_POS_TEST_FULL_20170716_212844.bak' WITH CHECKSUM, NO_COMPRESSION

No problem if exeute in Management Studio

What did you expect to see?

What did you see instead?

[axelfontaine]

Can this statement be executed within a transaction? Could this be the issue?

[axelfontaine]

I investigated and BACKUP and RESTORE are indeed not able to run inside a transaction. Solving this would probably require a good answer to https://stackoverflow.com/questions/47497351/sql-server-how-to-lock-and-unlock-a-table-exclusively-outside-of-a-transaction

[axelfontaine]

OK, I did find a way to make this work after all. Fixed.

[jbryu]

axelfontaine, How did you fix the issue? I tried to run the following command, but I couldn't execute it inside a transaction.

USE [TARGET_DB];
GO
BACKUP DATABASE [TARGET_DB]
TO DISK = 'D:\Backups[TARGET_DB].bak'
WITH FORMAT,
MEDIANAME = '[TARGET_DB]_Backup',
NAME = 'Full Backup of [TARGET_DB]';
GO

[axelfontaine]

@jbryu Are you having trouble running that statement with Flyway? If so, please open a new issue with all necessary details to reproduce.