2

I have a requirement to backup the SQL database present in Azure Managed instance and restore it on local server for root cause analysis. This needs to be performed whenever we encounter a certain error on the DB. Since the automated backups are TDE enabled, what are the available options to restore the db on local machine?

Upon browsing, I came across two options but need some guidance in them.

One option is to disable the TDE on the database, backup the db and then enable the TDE. But in this option, the backup file will not be in encrypted format and since we have confidential data, this doesn't seem to be a secure solution. Do we have any options to use our own encryption for backing up the database and use those for restore on local machine?

Second option to get the data is to use BACPAC files. But again they too suffer from the same security issue. Can we encrypt the bacpac files while creating and decrypt them while loading to local server?

Kindly advise if there are any other alternatives too.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
PraveenDS
  • 51
  • 3
  • We can not encrypt the bacpac files while createing or loading. Another way is that you export the BACPAC file to blob storage，then you encrypt and decrypt it. Please reference [Encrypt dacpac/bacpac file](https://social.msdn.microsoft.com/Forums/en-US/7ecc9439-a2b8-4c4d-84cc-055e5c603e4f/encrypt-dacpacbacpac-file?forum=ssdsgetstarted). If you need, I can post it as answer. – Leon Yue Dec 26 '19 at 02:48

1 Answers1

1

The big consideration is that you won't be able to use a binary backup ( BACKUP DATABASE command) to transfer a database from any SQL Azure offering, including Managed Instance, to on-premise. The reason for this is that the software in Azure SQL DB is constantly being updated, so the version running in Azure SQL DB (or Managed Instance) will always be ahead of the version running on-premise.

So, while you can physically create a backup file in a Managed Instance, and restore it to the same or another Managed Instance, if you transfer it to on-premise, you won't be able to restore it until the on-premise version of SQL catches up with the software used to create the backup. So there are more obstacles than just the TDE issue.

After all is said above your best option is to use a bacpac instead. You can create a bacpac of an Azure SQL database and store it on your local computer. All information that goes inside or outside Azure is encrypted by default.

SqlPackage /Action:Export /SourceServerName:SampleSQLServer.sample.net,1433 /SourceDatabaseName:SampleDatabase /TargetFile:"F:\Temp\SampleDatabase.bacpac"
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30