locked
Schedule DB Backup on Azure Blob RRS feed

  • Question

  • Hi Guys,

    I want to schedule backup task on SQL Server (Azure VM with SQL Server 2016 EE) and will take on azure blob. I am using below query (on secondary replica) details for the same but getting error:-

    BACKUP DATABASE [SMSProd]
    TO URL=N'https://dthblobstorage.blob.core.windows.net/imagesdocs/SMSProd_21062018_1100AM.bak'
    WITH copy_only,stats = 10

    and Error details :

    Msg 3201, Level 16, State 1, Line 46
    Cannot open backup device 'https://dthblobstorage.blob.core.windows.net/imagesdocs/SMSProd_21062018_1100AM.bak'. Operating system error 50(The request is not supported.).
    Msg 3013, Level 16, State 1, Line 46
    BACKUP DATABASE is terminating abnormally.

    Please share the reason & solution for the same.

    Thanks

    dk

    Thursday, June 21, 2018 8:43 AM

Answers

  • CREATE CREDENTIAL BackupCred
    WITH IDENTITY='<BlobStorageAccountName>',
    SECRET = '<Blob Storage Account Key>'
    GO

    --Check the Credential in below table
    SELECT * FROM sys.certificates

    --Use below code to take backup of DB and push on blob URL
    DECLARE @IsSecondaryReplica int=0
    SET @IsSecondaryReplica = (SELECT [master].sys.Fn_hadr_backup_is_preferred_replica('DTHWorld')) 

    IF (@IsSecondaryReplica = 1) 
    BEGIN 
    BACKUP DATABASE MDMProd
    TO URL = N'https://dthworldstorage.blob.core.windows.net/imagesdocs/DTHWorld_21062018_1500PM.bak'
    WITH credential = 'BackupCred',COPY_ONLY, COMPRESSION,STATS = 10;
    END 

    Try above code

    • Marked as answer by Dhanush007 Thursday, June 21, 2018 12:20 PM
    Thursday, June 21, 2018 10:56 AM

All replies

  • Are you sure you followed the steps exactly as specified in below document?

    http://dallasdbas.com/sql-backups-azure-storage/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, June 21, 2018 8:50 AM
  • CREATE CREDENTIAL BackupCred
    WITH IDENTITY='<BlobStorageAccountName>',
    SECRET = '<Blob Storage Account Key>'
    GO

    --Check the Credential in below table
    SELECT * FROM sys.certificates

    --Use below code to take backup of DB and push on blob URL
    DECLARE @IsSecondaryReplica int=0
    SET @IsSecondaryReplica = (SELECT [master].sys.Fn_hadr_backup_is_preferred_replica('DTHWorld')) 

    IF (@IsSecondaryReplica = 1) 
    BEGIN 
    BACKUP DATABASE MDMProd
    TO URL = N'https://dthworldstorage.blob.core.windows.net/imagesdocs/DTHWorld_21062018_1500PM.bak'
    WITH credential = 'BackupCred',COPY_ONLY, COMPRESSION,STATS = 10;
    END 

    Try above code

    • Marked as answer by Dhanush007 Thursday, June 21, 2018 12:20 PM
    Thursday, June 21, 2018 10:56 AM