Backup to local and MIRROR TO Azure Files returns ERROR

I have an Azure VM with SQL Server 2014, with a local drive (X:) for storing backups. Using Ola Hallengren’s scripts. I added an Azure Files share and tried to do a mirror backup, but I get the following error:

BACKUP DATABASE [Test] TO DISK = 
    N'X:BackupSRVSQL01TestFULLSRVSQL01_Test_FULL_20150420_185840.bak' 
MIRROR TO DISK = 
    N'\storage.file.core.windows.netbackupSRVSQL01TestFULLSRVSQL01_Test_FULL_20150420_185840.bak' 
WITH NO_CHECKSUM, COMPRESSION, FORMAT

Msg 3212, Level 16, State 0, Line 3
The mirror device "X:BackupSRVSQL01TestFULLSRVSQL01_Test_FULL_20150420_185840.bak" 
and the mirror device "\storage.file.core.windows.netbackupSRVSQL01TestFULLSRVSQL01_Test_FULL_20150420_185840.bak" 
have different device specifications.

Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Outcome: Failed

Initially, the local drive was formatted with 64K allocation unit size. Tried switching to 4K, and using the BlockSize switch, but no luck. Any ideas?

I can’t see that you are using backup to URL. It looks like a local drive and a file share.

If you read about mirrored backups you see that there are some special requirements:

“Mirroring applies both to disk and tape (disks do not support continuation tapes). All backup devices for a single backup or restore operation must be of the same type, disk or tape.”

“Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer.”

https://msdn.microsoft.com/en-us/library/ms175053.aspx

Starting in SQL Server 2016 MIRROR TO is supported with backup to URL: https://msdn.microsoft.com/en-us/library/dn435916(v=sql.130).aspx

But still remaing what Ola Hallengren said:

“Mirroring applies both to disk and tape (disks do not support continuation tapes). All backup devices for a single backup or restore operation must be of the same type, disk or tape.”

So, you can use a old trick that is use xp_cmdshell to map an Azure File Share as local drive and then use this new drive as mirror. You can map an Azure File Share but not a Blob:

Using Backup script

EXEC xp_cmdshell 'net use Z: \<YOUR_STORAGE_ACCOUNT_NAME>.file.core.windows.net<YOUR_FILE_SHARE> /u:<YOUR_STORAGE_ACCOUNT_NAME> <YOUR_ACCESS_KEY>'

BACKUP DATABASE <DATABASE_NAME> TO DISK = 'C:Backup<BACKUP_NAME>.bak' MIRROR TO DISK = 'Z:Backup<BACKUP_NAME>.bak' WITH FORMAT

EXEC xp_cmdshell 'net use Z: /delete'

Using Ola Hallengren DatabaseBackup procedure:

EXEC xp_cmdshell 'net use Z: \<YOUR_STORAGE_ACCOUNT_NAME>.file.core.windows.net<YOUR_FILE_SHARE> /u:<YOUR_STORAGE_ACCOUNT_NAME> <YOUR_ACCESS_KEY>'

EXEC xp_cmdshell 'sqlcmd -E -S <YOUR_SERVER_NAME> -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''ALL_DATABASES'', @Directory = N''C:Backup'', @MirrorDirectory = N''Z:Backup'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 336, @MirrorCleanupTime = 336, @CheckSum = ''Y'', @LogToTable = ''Y''" -b'

EXEC xp_cmdshell 'net use Z: /delete'

MIRROR TO is not supported with backup to URL: https://msdn.microsoft.com/en-us/library/dn435916.aspx#limitations

Specifically under the “Support for Backup Arguments” section, you’ll see “Mirror To” has a “-” for not supported.