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:
The Following SQL server error(s) occured: 2705, "42S21",[Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'securityoption' in table 'dbo.$ndo.$dbproperty' is specified more than once. SQL: ALTER TABLE [dbo].[$dnproperty] ADD securityoption INTEGER NOT NULL DEFAULT 0
2705, "42S21",[Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique. Column name 'securityoption' in table 'dbo.$ndo.$dbproperty' is specified more than once.
SQL:
ALTER TABLE [dbo].[$dnproperty] ADD securityoption INTEGER NOT NULL DEFAULT 0
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
Nuno Maia: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.
Hi David!
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!
Maybe this could help you, too?
Kind regards,
Jörg
Jörg A. StrykSTRYK System ImprovementPerformance Optimization & Troubleshootinghttp://www.stryk.info
Jörg Stryk:Hi David! 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! Maybe this could help you, too? Kind regards, Jörg
Thanks Jörg, that was it.
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.
thanks.
David,
During your error fix, were you able to run 3.70 with SQL 2005 ?
Nuno Maia:David, During your error fix, were you able to run 3.70 with SQL 2005 ?
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.