Introduction:
Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from corruption or deletion.
Azure SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes. The frequency of transaction log backups is based on the compute size and the amount of database activity.
The exact timing of all database backups is determined by the SQL Database or SQL Managed Instance service as it balances the overall system workload. You cannot change the schedule of backup jobs or disable them.
Overview:
In case you have a different requirements rather than automated backup comes with Azure SQL Managed Instance PaaS offering, you can schedule copy_only backup for one/all databases in your Azure SQL managed instance.
In this article, we will take you through the steps on how to schedule SQL managed instance backup to Azure Blob storage using T-SQL script and SQL server agent job.
| Note:- Azure SQL Managed Instance does not currently support exporting a database to a BACPAC file using the Azure portal or Azure PowerShell. To export a managed instance into a BACPAC file, use SQL Server Management Studio (SSMS) or SQLPackage. |
Resolution:
- You would need to store credentials that would be used to access Azure Blob Storage.
CREATE CREDENTIAL [https://myaccount.blob.core.windows.net/testcontainer/]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = 'XXXXXXXXXXXXX';
The name of the credential should be the same as the URL of the target Azure Blob Storage container. In this case, this is myaccount account and testcontainer container stored on the URL: https://myaccount.blob.core.windows.net/testcontainer
2. Once you create a credential, you can backup any database using the standard copy_only backup T-SQL command:
BACKUP DATABASE Mydb
TO URL = 'https://myaccount.blob.core.windows.net/testcontainer/Mydb.bak'
WITH COPY_ONLY
3- To schedule the backup job, follow below screenshot from SSMS.
Expand the SQL server agent from SSMS and click on New Job and provide the jobname
Click on new and provide the step name, select the Transact SQL and database name and paste the T-SQL command.
In schedules, click on new and schedule it as suitable.
You can get more information on the job runs from Job History.
Hereunder you can find script to take one database or all databases backup.
-- Script to take the backup with timeformat(yyyy-mm-ddThh:mm:ss) by providing the dbname at @name
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- dateformat
set @name = 'MI' --provide dbname here
-- specify database backup directory
SET @path = 'https://myaccount.blob.core.windows.net/testcontainer/' --required backslash at the end of the storage account
-- specify filename format
SELECT @fileDate = CAST(FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss') as nvarchar(max))
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @fileName with copy_only
--Script to take the backup of all the databases with the time stamp (here excluded system databases)
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for filename
-- specify database backup directory
SET @path = 'https://myaccount.blob.core.windows.net/testcontainer/' --required backslash at the end of storage account
-- specify filename format
SELECT @fileDate = CAST(FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss') as nvarchar(max))
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read-only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @fileName with copy_only
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Limitations
- Taking manual COPY-ONLY backup of a database encrypted by service-managed TDE is not supported in Azure SQL Managed Instance, since the certificate used for encryption is not accessible.
In case the database is encrypted with service managed key, you might receive an error like:
Msg 41922, Level 16, State 1, Line 29
The backup operation for a database with service-managed transparent data encryption is not supported on SQL Database Managed Instance.
You can use below PowerShell commands to know get more information regarding the database encryption either Service Managed Key (SMK) or Customer managed Key (CMK)
Get-AzSqlDatabaseTransparentDataEncryption -ServerName $AzureSQLServerName -ResourceGroupName $ResourceGroupName -DatabaseName $DatabaseName
Get-AzSqlServerTransparentDataEncryptionProtector -ServerName $AzureSQLServerName -ResourceGroupName $ResourceGroupName | Select-Object ServerName,Type
You can still disable the encryption in case you want to proceed with manual backup, by running the command, or switch to Customer Managed Key(CMK):
select name , is_encrypted from sys.databases where name = 'dbname'
Alter database dbname set encryption off
use dbname
go
drop database encryption key
IMPORTANT: you will need to plan for disabling the encryption and perform this action off-peak hours, making sure that are no open transactions,
For more information, see
Remove Transparent Data Encryption (TDE) from SQL Server user databases (sqlshack.com)
Additional References
Native database backup in Azure SQL Managed Instance - Microsoft Tech Community
Automate exporting of azure sql database as .bacpac to blog storage. - Microsoft Tech Community
Quickstart: Backup & restore to Azure Blob Storage - SQL Server | Microsoft Docs
We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.
Durgaprasad Srikurmadasu (Author)
Ahmed Mahmoud (Co-Author)
Microsoft