In the end, it looks like I should have searched through the code for all SQL commands like "SELECT" and "UPDATE" and "INSERT" and determined which tables I needed. Might have saved a few days. But you know how you get tunnel vision when you're down the rabbit hole. So, instead of something simple yet tedious, here is what happened:
- in order to run app, need to connect to certain tables in database
- only need a couple but not sure which, to be sure need to trace through a bunch of code so... quicker to download all tables
- except... there are a dozen tables some of them gigabytes one of them 44 gigabytes
- have to download 7zip to server to compress
- compress, the largest one takes over an hour to compress
- copy from server to jump server
- copy from jump server to dev machine
- meanwhile searching and installing sql server express
- downloaded all but largest one, which is 7g and requires 12 hours to download
- cannot connect to db
- open tcp connection
- still fails
- running out of space on jump server, split file, etc
- attempt script to import all quickly, fails
- import data takes hours, fails
- it turns out to be corrupted
- download it again, another 12 hours
- download another archiver which has ability to repair this type of corruption
- repair takes hours
- corruption not fixed
- re-compress using 7z instead of zip so file is 3g instead of 7g
- download to jump, download to dev
- import, fails because exceeds limit of sql server express
- download install sql server dev edition
- installer fails because service pack 2 not in place
- download install sp2
- installer fails because wmi running
- disable wmi
- installs okay
- imports okay
- total time so far? 3 days.
- still cannot connect. might be because upgrade from sqlexpress to sqlserver was corrupted. ([Later Edit]: YES that was why!)
- attempt to rename instance. cannot be done.
- backup databases (44g takes long time again)
- uninstall sqlserver, reinstall
At this point I stopped taking notes, I think it took a couple more days. I eventually got it, though. the upgrade from sqlexpress to sqlserver had to be wiped completely.