This article describes,
How do I backup my SQL Server database to Azure blob storage? or how to resolve SQL server backup to url?
SQL Server support URL backup from SQL Server 2012. We can directly take SQL database backup on Azure Blob Storage.
Azure Blob storage is storage space which can store your file. Blob storage support page blob and block blob. SQL backup can be stored in page blob or blob-blob format.
If you are storing SQL backup in page blob you can directly restore database from azure storage. If SQL backup in blob-blob, then file need to be downloaded from azure storage to local disk and then restore.
Steps for sql server backup to url azure blob storage or how do I backup my SQL Server database to Azure blob storage?
Step 1: Create storage account on azure subscription.
Step 2. Get storage account name and key. This key needed to create SQL Credential.
Step 3. Create SQL credential using storage key and name. We can create that either using shred access signature or using storage key as identity. Run below query in SSMS to create SQL credential.
--CREDENTIAL using storage account name and access key
USE [master]
GO
CREATE CREDENTIAL [storageaccount] WITH IDENTITY = N'storageaccount', SECRET = N'Keyofstorage'
GO
--CREDENTIAL using shared access signature. This can be created in Azure Storage Explorer
USE [master]
GO
CREATE CREDENTIAL [https://storageaccountname.blob.core.windows.net/dailylogbackups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'Shared access signature secret'
#Key will look like 'sv=2021-02-02&st=2022-10-08T03%3A34%3A44Z&se=2050-10-09T03%3A34%3A00M&sr=c&sp=rapgtl&sig=OWV%2F9KZ871qFPWbQA547v4BpB4WUCC2Aus0Y7yEmaT0%3D
Step 4 . Now we can take backup on Azure Blob storage using below script.
--Backup using Credential
Backup Database DBName to url = 'https://storagename.blob.core.windows.net/containername/DBname.bak' with Credential = 'Credential name created in step3',stats =10--Backup using Shared access signature. If we have created Credential with Shared access signature Credential parameter is not required.
Backup Database DBName to url = 'https://storagename.blob.core.windows.net/containername/DBname.bak' with stats =10You can schedule SQL backup in URL using SQL Agent job. Creation of SQL job is same as taking SQL Server backup on local disk.
how to resolve sql server backup to url? or How do I backup my SQL Server database to Azure blob storage?
If you are using Ola Hallengren’s SQL Server Maintenance Solution for backup, sometimes you will receive in Transaction log backup
1117(The request could not be performed because of an I/O device error.) [SQLSTATE 42000].
backup/LOG/databasename_LOG_20221218_191534.trn” failed: 1235(The request was aborted.) [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 1 : BACKUP LOG is terminating abnormally. [SQLSTATE 42000]
This issue occurred because size of database log file size grown in TB is huge. Because of that backup stop working.
In this case you need to take split log backup for the database where log backup stopped working.
Exclude the database from your SQL Backup job before taking split backup.
Take Transaction log backup on same path where you SQL backup job taking backup to keep consistency.
-- Use this script to take Split T log backup.
--Make sure you have shared access signature created. If not, you can use with Credential parameter in script
Declare @bkpDate varchar (100)
,@file1 varchar (300), @file2 varchar (300),@file3 varchar (300), @file4 varchar (300), @file5 varchar (300)
,@file6 varchar (300), @file7 varchar (300)
set @bkpDate = REPLACE (REPLACE (REPLACE (CONVERT(VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_')
set @file1 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServeName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_01.trn'
set @file2 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_02.trn'
set @file3 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_03.trn'
set @file4 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_04.trn'
set @file5 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_05.trn'
set @file6 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_06.trn'
set @file7 = 'https://storageaccountname.blob.core.windows.net/uat-transactionlog-backup/ServerName/DatabaseName/LOG/' + @@SERVERNAME + 'DatabaseName' + '_Log_' + @bkpDate + '_07.trn'
Once this backup complete you can check SQL log file space.
DBCC SQLPERF(LOGSPACE)and try to shrink log file. We may need to run split backup script multiple time to release space in SQL Server log file.
Once SQL log file shrunk you can add database again in daily SQL Server Backup job and backup will start working again.
1 thought on “How do I backup my SQL Server database to Azure blob storage? | how to resolve SQL server backup to URL?”