Make backups portable to on-premise SQL
Get this message when attempting to restore an SMI backup to on-premise SQL:
The database was backed up on a server running version 15.00.0700. That version is incompatible with this server, which is running version xx.xx.xxxx. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Lock in is risky for customers for a number of reasons. A workaround is using BACPAC but getting an established database into a state where BACPAC doesn't spew errors is difficult in our experience.
Hi all and thank you for your feedback. While this will not directly address backup and restore from MI to back on-premises SQL (this is also being considered and some updates might follow), we are considering a replication technology which might help support this scenario. We are exploring product improvement opportunities in the area of hybrid link using distributed availability groups (AG) replication technology between SQL Server and MI. This will enable near real time data replication between SQL Server and MI. We are also considering failover scenarios both ways. If this might interest you, we invite you to provide your feedback to the engineering team through this online survey: https://aka.ms/mipg-chimera-survey. Thank you.
Dan Polivy commented
I want to add my vote to having some easier way to restore a SQL MI database to on-premise SQL Server. Our scenario is primarily for development, so developers can have a local copy of the database on their machines for dev/test work. I am attempting to use the bacpac method, but it's been running for 18+ hours and isn't close to done.
Nigel Morse commented
This might to be a show stopper for us to migrate if we can't easily get local backups. Making a BACPAC of the DB took 12 hours (!). Could there not be an option to hold DBs back to known version level on a manged instance for example? one that is compatible with latest on prem release at least?
Ajay Prakash (Tec Partners) commented
In my case the SQL MI is behind the on-prem SQL, it still fals. Not sure why?
System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.2070. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
We have a few dbs in SMI and we need to provide a rollback plan in case we need to move back to on-premises. Choosing AWS RDS would have been much better where the rollback would be easier.
First of all congratulations! Getting rid of BE and using VEEAM instead is something that reminds of sooo many happy customers and smiling faces 😄
When you create an Agent Backuo Job in the B&R console you have to choose if it's a workstation, server or failover cluster backup.
What did you choose here? I don't see what type of clustering you're using for SQL.
The "volume or entire computer" question is different. What do you want to restore, the computer or only the app data? If you want to restore everything you pick entire computer, if you only want app data you pick the volumes. (https://surveyreward.co)
Anthony Genovese commented
Ultimately, I need a way to snapshot and move data. It needs to be as easy and as quick as a backup and restore is right now.
The DDL layer is simple. Schema compare & dac pacs get you where you need to go. But storing and moving that data seems to be the hard part. What I want is something packaged all together that easy to use and automate. And quick.
While it is true that the DB Engine of SMI may be changing frequently...the Internal Database Version number should not be changing frequently. It would be extremely helpful if the Internal Database Version number used in SMI was coordinated with the latest Internal Database Version number used on the latest version of SQL Server On-Premise (or IaaS). This of tremendous value for folks that want to push forward into hybrid cloud, but are fearful of an inability to return back fully in tack. BACPAC and Replication are not solutions, they are half-baked work arounds. If they were solutions then there would be no need for backup to exist in SQL Server at all.
We got the information that DMS should do the job. The feature should be implemented some time in Q2/Q3. Can you confirm this?
Or the option or opportunity to upgrade on premise version to match the MI version. Some off MI platform development, POCing, and testing is useful.
Our use case for this request would be to be able to restore an archived backup of a database from within the past 3-7 years to be able to answer auditing questions. We would prefer not to have to create a new managed instance for this. Transaction replication would not work, BACPAC might work but is not ideal.