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.
SQL server 2005 has a different security model and Navision below 4.0 SP3 is incompatible. I already a similar issue when making a db conversion from 3.70B SQL 2000 to 4.0 SP3 in SQL 2005 (DB objects are still from 3.70B).
The only way I solved was to restore using Navision Client and not using SQL Server.
Probably another member from community has a way to solve that issue directly using sql restore tool
Thanks for the help Nuno, but I already considered the 4.00 security model as an issue, but as I said in my post, I tried both 3.70B AND 4.00 SP3, and both gave the same errors. Also as i said, I would prefer to use 3.70 if possible, but will use 4.00 if thats the only solution.
PS the database is now converted to 4.00, so I now can’t get back with 3.70, but its no problem to delete the DB, and start again.
Once, I encountered similar problems; means that after a failed conversion of a database, I got the same error as you described. The problem is, that the column “securityoption” was added to $ndo$dbproperty with the first attepmt, after failure the column still remained, thus the error occurred on the second attempt to convert.
Here I simply deleted this column, tried the conversion again - and it worked!
Makes sense now, the first time I deliberately had a bad user ID to check to make sure I was correctly creating the correct password. So deleted the column, and it works perfect.
By the time I got it all working, I had already converted to 4.00SP3. But I don;t think there would be a problem with 3.70, and I will try that next time.