February 19, 2024 at 4:35 pm
Dear DB Gurus
I have an application that was running in 2019 database. We tested that application using 2022 and it was all working fine. But after putting that in production we found an issue that was not tested before. The situation is that vendor of the application is no longer reachable and able to help fixing that problem in 2019. So the only option is to migrate the new data from 2022 to 2019. I assume I am not the first one who did a crappy testing before going to production and found wanting. What would be the best way to downgrade it?
Let us say this is the only option i have now!
February 19, 2024 at 4:40 pm
Script out all the tables and data via SSMS / Tasks / Generate Scripts, then replay the output script on a 2019 server.
Make a BACPAC of the database (may or may not be possible due to size of the DB) and then import this BACPAC on your 2019 server.
If BACPAC wont work due to size, create a DACPAC of the schema only, recreate this on 2019, then use Import Export Wizard to ETL out the data from 22 to 19.
If you still have your 2019 server you could write scripts to do a delta load of changed / new info without having to write the whole DB.
No easy way to do it, it's all going to take some work.
February 19, 2024 at 4:45 pm
First of all, establish what the issues are and then target those issues! Without a proper RCA (Root Cause Analysis), I will not provide any guesswork on a possible solution.
😎
Another serious thought and concern is the unavailability of the vendor, is this a business-critical application?
February 19, 2024 at 5:01 pm
Thanks a lot for the quick reply. It is a critical application and everything is working fine except for a small technical glitch in the application that gives an error when using 2023. The technical glitch does not affect the end user but afffect the maintenance of the application. So from technical aspect it is important. Let us say vendor will not help us on this one. Since we dont own the application we cannot do a RCA to find what causes the problem and how to fix it. So without keeping the database too long which result in too much data difference want a tried and tested method to do the migration. I have explored all the possible options and all went south. So that is why i mentioned that this is my last option.
@Ant-Green: thanks for some of your suggestions.
"Make a BACPAC of the database (may or may not be possible due to size of the DB) and then import this BACPAC on your 2019 server."
Is it possible, I was told that the back up file created would not be possible to restore it in 2019?
I have the copy of the 2019 back up which i used to restore the 2022. So that database contains all data to that date. the 2022 database contains that data + the delta. What is the best option to script the delta out and add it to the old 2019? It is a huge database with self referencing tables also there.
February 19, 2024 at 5:19 pm
A bacpac is version agnostic. It is different from a BAK (backup). But bacpac have limitations, size being one of them. So if it’s a big DB then bacpac may not work.
Best way to make a delta is calculate the hash of the 2019 row against the hash of a 2022 row and insert/update/delete as needed.
It’s going to be a hard slog for you but these things are not simple especially if you can’t get downtime and if data is constantly moving you’re going to have to delta multiple times.
Probably best to try and get the vendor on the phone or attempt a fix forward.
February 19, 2024 at 5:41 pm
Thank you! Looks like there are no solutions exists for this kind of problem. only have to manually genereate a merge statement to find the delta and either insert or update it.
February 19, 2024 at 8:17 pm
After restoring your 2019 backup, you could try running this tool to get the rest of your data moved back:
https://www.devart.com/dbforge/sql/datacompare/
This is not a recommendation, just a possibility. I have never used it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 19, 2024 at 8:34 pm
Thanks a lot Phil. Let me check that and see if it could get me the comparison and the script i need!
February 20, 2024 at 1:03 am
Just to ask the question, have you tried running the database in the 2019 Comparability Level to see if that fixes the issue?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2024 at 11:11 am
HI Jeff
yes I have done that already. But it is something in the application which vendor cannot support.
February 20, 2024 at 11:28 am
HI Jeff
yes I have done that already. But it is something in the application which vendor cannot support.
Are you able to provide more details about this issue? I know for sure that Jeff will be intrigued as to exactly what the issue is.
If you provide the technical details, there are many knowledgeable people here who may suggest a solution which you have not even considered.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2024 at 7:37 pm
I am also curious - is this a situation where you upgraded SQL Server and the vendor has not certified their application for that version? In other words, the vendor is pushing back because your configuration is not a supported configuration?
If that is the case - then it is a hard lesson learned. For any vendor supported application you *must* make sure the vendor will support the upgrades. The vendor could invalidate any support agreements you had - hopefully that isn't the case here.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply