Backup and Restore of a TDE enabled database on Managed Instance

azure sql database

This video is a quick walkthrough showing the backup and restore process of a database on a SQL Managed Instance with bring your own key and TDE enabled.

Here is the SQL code from the video as a starting point for using the copy only backups yourself.

--Show the database is TDE Enabled
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

--Backup the DB to blob storage
BACKUP DATABASE [TestDB] TO  URL = N'https://yourblobaccount.blob.core.windows.net/media/testdb_backup_2019_07_03_140416.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'TestDB-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--Show the access to the backup and that it is still TDE enabled
RESTORE FILELISTONLY FROM URL = 'https://yourblobaccount.blob.core.windows.net/media/testdb_backup_2019_07_03_140416.bak'
GO

--Drop the original DB
DROP DATABASE TestDB
GO

--Restore the database!
RESTORE DATABASE [TestDB] FROM URL = 'https://yourblobaccount.blob.core.windows.net/media/testdb_backup_2019_07_03_140416.bak'
GO

Next Post