An Azure relational database service.
Hi,
How could we control the timepoint of SQL MI backup?
Speaking about the automatic backup, "Both 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." in addition "By default, SQL Database and SQL Managed Instance store data in geo-redundant storage blobs that are replicated to a paired region."
Are you sure that a backup of 5-10 minutes ago does not fit you?
Note! The solution of backup manually is NOT recommended in most cases! It require to disable the TDE encryption and remove all current connections. I wrote about this in 2019 as a respond to Jovan Popovic (from Microsoft MI team) tutorial on how to Take a COPY_ONLY backup of TDE protected database on Azure SQL Managed Instance. Read my comment in the post and Jovan respond before you start!
The big comment in the blog was added as a result of my comment. Read it...
The basic steps are (follow the original post as well):
(1) Create Blob container for the backups
<blob name>.blob.core.windows.net/<container name>
(2) Create db for the test
CREATE DATABASE TestBackupDB
GO
(3) You can confirm that the database is encrypted by default
Select d.name, e.encryption_state_desc
from sys.databases d
LEFT JOIN sys.dm_database_encryption_keys e on e.database_id = d.database_id
GO
(4) If needed then create credential to the blob container
CREATE CREDENTIAL [https://<blob name>.blob.core.windows.net/<container name>/]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2014-02-14&sr=c&sig=GV0T9y%2B9%2F9%2FLEIipmuVee3jvYj4WpvBblo%3D&se=2019-06-01T00%2A%3AZ&sp=rwdl';
(5) If you try to backup the database "WITH COPY_ONLY" then you will get error!
BACKUP DATABASE [TestBackupDB]
TO URL = N'https://<blob name>.blob.core.windows.net/<container name>/TestBackupDB_backup_2021_12_03.bak'
WITH COPY_ONLY, CHECKSUM
GO
-- ERROR; BACKUP WITH COPY_ONLY cannot be performed since database encryption key for the database 'TestBackupDB' still exists.
(6) Remove TDE encryption - this is the part which I do not like :-(
-- We cannot backup encrypted database, so you need to turn off encryption
Alter database TestBackupDB set encryption Off
GO
-- Note! this can take time!
(7) Confirm encryption was removed
Select d.name, e.encryption_state_desc
from sys.databases d
LEFT JOIN sys.dm_database_encryption_keys e on e.database_id = d.database_id
GO
(8) You must execute Checkpoint
use TestBackupDB
GO
Checkpoint
GO
(9) You must drop the encryption key
DROP DATABASE ENCRYPTION KEY
GO
(10) Good time to shrink file before backup
-- get file name
SELECT [file_id], type_desc, [name] FROM sys.database_files
GO
DBCC SHRINKFILE ('<log file name>', 1)
GO
(11) Verify that there are no active VLF that is encrypted by thumbprint.
select * from sys.dm_db_log_info(null)
GO
(12) Finally we can backup the database
BACKUP DATABASE [TestBackupDB]
TO URL = N'https://<blob name>.blob.core.windows.net/<container name>/TestBackupDB_backup_2021_12_03.bak'
WITH COPY_ONLY, CHECKSUM
GO