The site is all back up and now running on ASP.net 2.0, SQL Server 2005 and DotNetNuke 4.0.2. I had a few little hiccups. First, DotNetNuke's instructions weren't clear that I needed to comment out the SQL 2005 provider and uncomment the SQL 2000/2005 deprecated driver.
I upgraded DotNetNuke before SQL Server because that's where I expected to see my issues. After that the SQL Server upgrade went very smooth. I had one warning message indicating that my server didn't meet the minimum requirements (it did) but the install went flawlessly.
Then I couldn't get my modified skins to apply correctly, finally I simply reapplied the skins and cleared my browser cache. After that everything worked.
Hopefully, I'll be able to spend some time on the site in the near future. I've got some nice plans, it's all about time now.