5

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:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak' 
MIRROR TO DISK = 
    N'\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak' 
WITH NO_CHECKSUM, COMPRESSION, FORMAT

Msg 3212, Level 16, State 0, Line 3
The mirror device "X:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak" 
and the mirror device "\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_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?

3

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

1

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'
0

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.

  • I see what you mean, but how could I have known that backing up to Azure Files share equals BACKUP TO URL? – nulldotzero Apr 23 '15 at 15:17
  • You're not using backup to url directly, but having an azure filesahre means you're using page/blobs underneath (if it's not a fileshare on a separate VM but a generic azure fileshare). In that case I doubt you'll be able to get the metadata on the underlying pinnings. – Sean Gallardy Apr 23 '15 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.