Azure SQL - snapshot create db fails but reported as successful

Because regular Azure SQL backup is timing out, I’m trying to do a backup with snapshot option enabled I noticed an issue.

When using an SQL user for backup that doesn’t have sufficient permissions for database creation, the backup application doesn’t properly report the create database error. As a result, in the log, the creation of database is successful and following drop database command fails on an error that the database to drop doesn’t exist:

|23/07/2021 12:36:54|[Info]|Starting job “SQL mydb” - “Full”. Server “DESKTOP-F8RTRFJ”. Account “SYSTEM@WORKGROUP”. App v.“12.5.13”.|
|23/07/2021 12:36:54|[Info]|Connecting to: 2014 RTM SQL Azure. |
|23/07/2021 12:36:55|[Info]|The backup folder “C:\WINDOWS\TEMP\SYSTEM\Pranas.NET\SBF\50391546-65bc-4ad6-b496-59cf545db509\backup” has “27,387GB” free space. The temporary folder “C:\WINDOWS\TEMP\SYSTEM\Pranas.NET\SBF\50391546-65bc-4ad6-b496-59cf545db509” has “27,387GB” free space.|
|23/07/2021 12:36:55|[Info]|Backing up “xxx” Azure SQL databases.|
|23/07/2021 12:36:55|[Info]|Backing up “tcdev-audit” with “Full” backup type.|
|23/07/2021 12:36:55|[Info]|Creating snapshot of “tcdev-audit” database to “tcdev-audit_SQLBackupAndFTP_Snapshot”.|
|23/07/2021 12:37:05|[Info]|Drop “tcdev-audit_SQLBackupAndFTP_Snapshot”.|
|23/07/2021 12:37:15|[Error]|Failed to backup “tcdev-audit” database with “Full” backup type: One or more errors occurred. > Database ‘tcdev-audit_SQLBackupAndFTP_Snapshot’ does not exist. Make sure that the name is entered correctly.|
|23/07/2021 12:37:16|[Info]|Cleaning up backups of “SQL tcdev” job.|
|23/07/2021 12:37:16|[Error]|Job “SQL tcdev” finished with “1” errors.|
|23/07/2021 12:37:23|[Info]|Sending job log to ales@trueclicks.com.|

I logged in to SSMS and run the create command myself under the backup user:

CREATE DATABASE [tcdev-audit_SQLBackupAndFTP_Snapshot] AS COPY OF [tcdev-audit] (EDITION=‘Standard’, SERVICE_OBJECTIVE=‘S2’);

error:
CREATE DATABASE permission denied in database ‘master’.

I can also see that no snapshot db is created during the process so I believe that it is a bug and it is misleading which makes it difficult to find the real issue.

Version: 12.5.13 Professional

UPDATE
I also found out finally the right permissions for a snapshot backup.

  1. On master db:
    EXEC sp_addrolemember 'dbmanager ', ‘your-sql-user’

  2. On every backed up database:
    EXEC sp_addrolemember ‘db_owner’, ‘your-sql-user’

The latter I wasn’t aware of and took me quite some time to figure out. Not sure if it is documented anywhere.

Hi Ales_Sturala,

Thank you for the details.

It seems the issue has been resolved. If you still have any questions, please let us know.

Thank you!