I have NAV 5.0 client installed on machines on my network and DBMS is SQL 2005.
Recently, I restored a backup from the production server to a new application server I’m setting up.
However, most application objects did not restore to my new server. I resorted to importing the objects one after the other.
The problem now is it did not restore the user roles. I have only SUPER role and I need all the roles on the current production server. Anytime I try to import user roles table from (an exported user role file the production server) to my new server it report that there are conflicts I try merge with existing and it does not update the roles. I still end up with only the SUPER role.
Please I need help urgently.
You did not restore into a clean and new database.
If possible, delete the new database from SQL and create a new one through NAV. (Menu File, Database, New)
Then restore the backup and you will see that everything is there.
Otherwise you would just need to open two clients, one connected to production and one connected to the new server
Then open up the tables 2000000004 (User Role) and 2000000005 (Permission) and copy all records over to your new database.
The issue when restoring backups into a database which had NAV companies in before is that these databases do have a number of tables already which are not company specific (Common to all Companies) such as Objects and Roles and Permissions.
Thank you Thomas. Your latter suggestion worked although, it was pain-staking.
If I were to restore a more recent full backup of the 'data’ associated to the all companies only to my newer database will have be faced with all the same challenges of having to import application objects all over again?
I feel this is still relevant and possibly useful for anyone still on classic client. It gave me clues recently and I came up with another solution.
Having a somewhat customized version of Navision 2009 SP1 with several custom module packages, I was having an issue restoring my DB from an fbk. Mostly issues with tables fields not existing and non empty fields being restored into base Navision. No issues at all with SQL backup and restore BTW.
I started out by creating a new company in the existing DB. Make sure it is different from your current production company names. Then I created a custom NAV backup “application objects” only from the new temporary company. Follow that to created another backup of the companies “data common to all companies” only. Make sure to leave out or delete the temporary company at this point. I then proceeded to create a new Navision DB using the client.
Now that you have a “CLEAN” DB with nothing at all and base Navision 2Billion object range, open the new database and restore the entire backup of the temporary company “application objects” only fbk file. Then follow that by restoring the data only fbk for the companies that you want in the DB. Now you have duplicated your database with all of your company specific objects and data roles and permissions included.
As a side note: This is a good way of fixing any collation inconstancies, issues, or the full re-indexing of your SQL database. When a native Navision restore is performed structures are rebuilt at that time.