Azure file share. Backup database to mounted drive

2
votes

Using Azure protal I have created a folder(backup) under fileshare(abcFileShare) and then mounted this on one of the azure virtual machine.

I can copy /paste file on to this drive, but when I try to backup a database on to this shared drive, using sql server query or job, it gives me an error:

Cannot open backup device '\xxxstorage.file.core.windows.net\abcfileshare\backup\AdventureWorks2012.BAK'. Operating system error 1326(The user name or password is incorrect.)

I am using this TSQL to backup:

DECLARE @name VARCHAR(50) 
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
SET @name = 'AdventureWorks2012'
SET @path = '\\xxxstorage.file.core.windows.net\abcfileshare\backup\'
SET @fileName = @path + @name +'.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH INIT, COMPRESSION

Not sure what I am doing wrong?

1
azureazure-storageazure-filesazure-storage-files

1 Answers

2
votes

There are a few steps to take for this to work.

  1. Persist your storage account credentials used to access the Azure File Share. Run the following in a command prompt:

    cmdkey /add:yourstorageaccount.file.core.windows.net /user:storageaccountname /pass:yourstorageaccountkey==

  2. The account running the MS SQL Server service must have access to the share, the default NT Service\MSSQLSERVER account doesn't work

  3. After you complete the steps above you can make backups from both SSMS or a script. Below is the script I used successfully

    BACKUP DATABASE [TestDB] TO DISK = N'\mystorageaccount.file.core.windows.net\uploads\test\newbackup.bak' WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10