Try, Fail, Try, Fail, Try, Fail - SQL Restore 19 to 17
-
I did this to myself, working away from the office yesterday on the laptop. The customer admin for one of our web-based systems is in quarantine at the very time when an annual purge/staffing adjustments is due by Monday morning. I volunteered to help them out the jam, requiring around 6 hours of my time but meant that my best opportunity would be yesterday working offline on a recently rebuilt laptop. It started when I realized that the laptop only had sql server 2019 (trying to keep it light) and the database I had pulled from the server is 2017. I've been down this one-way road before but I didn't have a choice. To be honest, I naively considered that maybe if I kept it at the 2017 compatibility setting, I might be allowed to somehow get the database back to 2017 and simply get it back on the server and call it a weekend. (the app isn't normally used on the weekends) Sadly, after all these years, sql server still cannot backup to a previous version or restore from a newer version...at all. :sigh: What I tried: 0: straight up restore...straight up failed! 1: export/import data-tier (.bacpac)...failed! 2: ssms copy database wizard...failed! The way I see it, all is not lost as there are a couple more options: (in order of likely outcomes) 0: install 2019 on the server and change the appropriate connection strings 1: manually copy records from a 2019 instance to the 2017 instance (around a dozen possible affected tables) locally 2: write a custom script to do #1 I'm going to let this one sit for a bit while I cure the new Traeger wood-pellet grill. The missus is going to do something with a chicken and a beer can later. Funny thing, their instruction manual uses a six-pack as a step indicator...by beer six, you are ready to start cooking! :laugh: This should be interesting! :) Anyway, I'm sure I'll find a solution! :laugh:
"Go forth into the source" - Neal Morse "Hope is contagious"
-
I did this to myself, working away from the office yesterday on the laptop. The customer admin for one of our web-based systems is in quarantine at the very time when an annual purge/staffing adjustments is due by Monday morning. I volunteered to help them out the jam, requiring around 6 hours of my time but meant that my best opportunity would be yesterday working offline on a recently rebuilt laptop. It started when I realized that the laptop only had sql server 2019 (trying to keep it light) and the database I had pulled from the server is 2017. I've been down this one-way road before but I didn't have a choice. To be honest, I naively considered that maybe if I kept it at the 2017 compatibility setting, I might be allowed to somehow get the database back to 2017 and simply get it back on the server and call it a weekend. (the app isn't normally used on the weekends) Sadly, after all these years, sql server still cannot backup to a previous version or restore from a newer version...at all. :sigh: What I tried: 0: straight up restore...straight up failed! 1: export/import data-tier (.bacpac)...failed! 2: ssms copy database wizard...failed! The way I see it, all is not lost as there are a couple more options: (in order of likely outcomes) 0: install 2019 on the server and change the appropriate connection strings 1: manually copy records from a 2019 instance to the 2017 instance (around a dozen possible affected tables) locally 2: write a custom script to do #1 I'm going to let this one sit for a bit while I cure the new Traeger wood-pellet grill. The missus is going to do something with a chicken and a beer can later. Funny thing, their instruction manual uses a six-pack as a step indicator...by beer six, you are ready to start cooking! :laugh: This should be interesting! :) Anyway, I'm sure I'll find a solution! :laugh:
"Go forth into the source" - Neal Morse "Hope is contagious"
You might be interested in How to migrate a SQL Server database to a lower version[^] Based on my experiences long ago with SQL Server 2012: if your database is large using SQL Server Management Studio with large scripts probably won't work, in this case I would recommend using SQLCMD. But of course it would be even better to dump SQL Server in favor of PostgreSQL :-\
-
I did this to myself, working away from the office yesterday on the laptop. The customer admin for one of our web-based systems is in quarantine at the very time when an annual purge/staffing adjustments is due by Monday morning. I volunteered to help them out the jam, requiring around 6 hours of my time but meant that my best opportunity would be yesterday working offline on a recently rebuilt laptop. It started when I realized that the laptop only had sql server 2019 (trying to keep it light) and the database I had pulled from the server is 2017. I've been down this one-way road before but I didn't have a choice. To be honest, I naively considered that maybe if I kept it at the 2017 compatibility setting, I might be allowed to somehow get the database back to 2017 and simply get it back on the server and call it a weekend. (the app isn't normally used on the weekends) Sadly, after all these years, sql server still cannot backup to a previous version or restore from a newer version...at all. :sigh: What I tried: 0: straight up restore...straight up failed! 1: export/import data-tier (.bacpac)...failed! 2: ssms copy database wizard...failed! The way I see it, all is not lost as there are a couple more options: (in order of likely outcomes) 0: install 2019 on the server and change the appropriate connection strings 1: manually copy records from a 2019 instance to the 2017 instance (around a dozen possible affected tables) locally 2: write a custom script to do #1 I'm going to let this one sit for a bit while I cure the new Traeger wood-pellet grill. The missus is going to do something with a chicken and a beer can later. Funny thing, their instruction manual uses a six-pack as a step indicator...by beer six, you are ready to start cooking! :laugh: This should be interesting! :) Anyway, I'm sure I'll find a solution! :laugh:
"Go forth into the source" - Neal Morse "Hope is contagious"
set the compatibility to 2017, or whatever. you can still have 2019 and run your databases as 2017. View or Change the Compatibility Level of a Database - SQL Server | Microsoft Docs[^]
-
I did this to myself, working away from the office yesterday on the laptop. The customer admin for one of our web-based systems is in quarantine at the very time when an annual purge/staffing adjustments is due by Monday morning. I volunteered to help them out the jam, requiring around 6 hours of my time but meant that my best opportunity would be yesterday working offline on a recently rebuilt laptop. It started when I realized that the laptop only had sql server 2019 (trying to keep it light) and the database I had pulled from the server is 2017. I've been down this one-way road before but I didn't have a choice. To be honest, I naively considered that maybe if I kept it at the 2017 compatibility setting, I might be allowed to somehow get the database back to 2017 and simply get it back on the server and call it a weekend. (the app isn't normally used on the weekends) Sadly, after all these years, sql server still cannot backup to a previous version or restore from a newer version...at all. :sigh: What I tried: 0: straight up restore...straight up failed! 1: export/import data-tier (.bacpac)...failed! 2: ssms copy database wizard...failed! The way I see it, all is not lost as there are a couple more options: (in order of likely outcomes) 0: install 2019 on the server and change the appropriate connection strings 1: manually copy records from a 2019 instance to the 2017 instance (around a dozen possible affected tables) locally 2: write a custom script to do #1 I'm going to let this one sit for a bit while I cure the new Traeger wood-pellet grill. The missus is going to do something with a chicken and a beer can later. Funny thing, their instruction manual uses a six-pack as a step indicator...by beer six, you are ready to start cooking! :laugh: This should be interesting! :) Anyway, I'm sure I'll find a solution! :laugh:
"Go forth into the source" - Neal Morse "Hope is contagious"
I think #2 would be your best option, having had to do something similar once. [rant] While we're on the subject, I'm so annoyed that SSMS doesn't support 2019 for anything other than reading the DB and schema. That new fangled SSMS, the one that looks like it was built to be cross-platform by someone who has no clue what people want to do as a DB Admin, is a POS. I ended up installing DBeaver but it has some problems with SQL 2019, but is at least a lot more usable than that newfangled crap Microsoft put out as an "upgrade" of SSMS. [/rant]
Latest Articles:
DivWindow: Size, drag, minimize, and maximize floating windows with layout persistence -
You might be interested in How to migrate a SQL Server database to a lower version[^] Based on my experiences long ago with SQL Server 2012: if your database is large using SQL Server Management Studio with large scripts probably won't work, in this case I would recommend using SQLCMD. But of course it would be even better to dump SQL Server in favor of PostgreSQL :-\