# How to restore a SQL database

**URL:** <https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597>\
**Category:** Technical Forum\
**Created:** [March 25, 2007, 12:08pm UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597 "2007-03-25T12:08:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![David\_Singleton](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/david_singleton/32/7471_2.png) [@David\_Singleton](https://www.dynamicsuser.net/u/David_Singleton)\
**Post date:** [March 25, 2007, 12:08pm UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/1 "2007-03-25T12:08:54Z")

</div>

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:

1. Restore SQL 2000 into a SQL 2005 server, using SQL 2000 compatibility mode.
2. Run the Cast as INT script in the new db.
3. Try to connect with 3.70.
4. 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.
5. I created the same user name on the SQL Server as in the Navision Database.
6. Now again logging in with 3.70 and basically I am getting lots of different errors.
7. Next try to log in using 4.00SP3.
8. I get the message to convert database, and after this I get:

> [@](#):
>
> 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

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.

---

<div class="post-metadata">

**Author:** ![nunomaia](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@nunomaia](https://www.dynamicsuser.net/u/nunomaia)\
**Post date:** [March 25, 2007, 1:16pm UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/2 "2007-03-25T13:16:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![David\_Singleton](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/david_singleton/32/7471_2.png) [@David\_Singleton](https://www.dynamicsuser.net/u/David_Singleton)\
**Post date:** [March 25, 2007, 2:53pm UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/3 "2007-03-25T14:53:38Z")

</div>

> [@](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![stryk](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@stryk](https://www.dynamicsuser.net/u/stryk)\
**Post date:** [March 26, 2007, 12:16am UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/4 "2007-03-26T00:16:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![David\_Singleton](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/david_singleton/32/7471_2.png) [@David\_Singleton](https://www.dynamicsuser.net/u/David_Singleton)\
**Post date:** [March 26, 2007, 12:28am UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/5 "2007-03-26T00:28:40Z")

</div>

> [@](#):
>
> 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

[\<:o)] 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.

---

<div class="post-metadata">

**Author:** ![nunomaia](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@nunomaia](https://www.dynamicsuser.net/u/nunomaia)\
**Post date:** [March 26, 2007, 3:32am UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/6 "2007-03-26T03:32:33Z")

</div>

David,

During your error fix, were you able to run 3.70 with SQL 2005 ?

---

<div class="post-metadata">

**Author:** ![David\_Singleton](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/david_singleton/32/7471_2.png) [@David\_Singleton](https://www.dynamicsuser.net/u/David_Singleton)\
**Post date:** [March 26, 2007, 7:03pm UTC](https://www.dynamicsuser.net/t/how-to-restore-a-sql-database/16597/7 "2007-03-26T19:03:09Z")

</div>

> [@](#):
>
> 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.
