I am having trouble restoring a database, and hope someone can give me some help. (Note this is not a live scenario, its just for me to create a local live system, so it doesn’t need to be perfect, it just needs to work).
The client it on 3.70B running on SQL server 2000. From this database they created a SQL server backup, that is about 50Gig. This database uses DATABASE logins.
I need to restore this onto a SQL 2005 server, I would prefer to run 3.70B, (but changing the cast as INT script), but there is no problem also if I just use 4.00SP3 executables. (As I said its only for testing).
About a month ago, we did a backup restore (in the same environments) using a Navision FBK backup, and all went fine. This time though we decided to do a SQL backup for speed. (45 min SQL restore vs about 15 hours using FBK).
First we actually tried just copying the mdf, ndf and ldf to a USB drive and working from there. We attached the SQL server fine, and have access to the DB, but can’t connect with Navision. So we tried the SQL backup, did a SQL restore, and now we get exactly the same issues, so I don’t think it is a restore issue, but a Navision 3.70 vs SQL 2005 issue.
So here is what I did:
- Restore SQL 2000 into a SQL 2005 server, using SQL 2000 compatibility mode.
- Run the Cast as INT script in the new db.
- Try to connect with 3.70.
- This gave me a security issue, (which I expected). I wanted to make sure that I was resolving security, so step one was to make sure I got a log iin error.
- I created the same user name on the SQL Server as in the Navision Database.
- Now again logging in with 3.70 and basically I am getting lots of different errors.
- Next try to log in using 4.00SP3.
- I get the message to convert database, and after this I get:
I still think I am messed up on security, but not sure exactly what I did wrong. Of course I could just do a navision backup, but that is so slow compared to this… IF I can sort it out.