Restore backup Nav 4.03 sp 3 SQL Srvr 05 Win Srvr 2003 Std

SereneTiger

We have been doing the following:

Backup production data using Backup in Navision. Options: Entire Database, Data Common to All Companies, Application Objects for a selected company.

Open development database, selected company, and delete database in Navision. Choose database>new and create new database, data on one drive, log on another; accepting most defaults except Recovery model set to Simple and Security Model set to Standard and increase log size to 1000. After the database is created, choose Restore, navigate to backup made above, set options: Entire Database, Data Common to all Companies, Application Objects. When restore has completed, change and delete users as required for development, synchronize security.

We have been following this procedure since we migrated to SQL server with no problems.

Today, I tried to do this, but when the restore window came up, the Data Common to All Companies and Application Objects options were grayed out. After reading many posts, I thought maybe I messed up making the backup, so chose earlier backups, including one I had previously restored in this manner, and all of them had those options grayed out. If I proceed with the restore, all I get are the tables.

All of our Navision databases are on the same server, so SQL server backup doesn't work to accomplish the task.

I also noticed that the new database says I'm the owner, where in the past the database owner was the sa, no matter who created it.

I'm wondering if one of Microsoft's security patches could have caused this problem. Has anyone encountered this and what did you do to fix it?

Or alternatively, does anyone know of a work around? All I want to do is duplicate my production data and structure for development and testing purposes.

Thanks!

bbrown

SereneTiger wrote:

...All of our Navision databases are on the same server, so SQL server backup doesn't work to accomplish the task.
...
Thanks!

SQL Server backup/restore is fully capable of handlng this task. It is also much faster. We have a site where the live database, a copy for training, plus a copy for development are all on the same server. The development and training databases are refreshed from live about every 4 to 6 weeks. Each database is 100 GB. With SQL, updating the 2 databases takes less than 2 hours.

bbrown

Some helpful SQL scripts:

To backup the live database:

BACKUP DATABASE Your_Live_Database
TO DISK = 'J:\Rebuild\ForTraining.bak'
WITH Copy_Only

The Copy_Only prevent the transaction log from being truncated

To Restore

RESTORE DATABASE Your_Testing_Database
FROM DISK = 'J:\Rebuild\ForTraining.bak'
WITH MOVE 'live_Data' TO 'H:\Training\test_data.mdf',
MOVE 'live_1_Data' TO 'H:\Training\test_1_data.ndf',
MOVE 'live_Log' TO 'G:\Training\test_Log.ld

kine

The options are grayed out if:

1) They are not part of the backup
2) There is some object alread created in the database
3) There is some record created in the tables

For me it seems that someone created company in the new database before restoring the backup or entered some user into login tables etc. Try to delete the database and create it once again.

SereneTiger

bbrown, thanks for your help. We are trying your backup and restore method on our qa server now.

I'm still thinking these problems were caused by security patches in SQL Server 2005 (since we have been following the same procedure above for almost 5 years with no problem) and would be interested in hearing from anyone who has encountered and fixed this problem.

Kine, sorry, but none of your answers apply to my circumstance. We tried deleting, creating, and restoring the new db several times before posting, including attempting to restore a file that previously restored with no problems. There seems to be an issue with SQL server 2005 or the OS.

Thanks to everyone for responding.

kine

Ok, it seems that my collegue has same problem today. It seems that NAV 4.00SP3 in some version has this problem, but I didnt' tested it yet.

SereneTiger

I have solved this problem. After creating the new database, change the dbo to sa. Navision apparently creates the database with the logged in user as the dbo. Then go back into Navision and follow the restore procedure. The objects are then available to be restored.

Thanks for all your posts.

DenSter

Create the database from the NAV client, it will ask for user credentials when connecting to the server. Enter the SA credentials and it should be created with the SA as dbo.

SereneTiger

That works when you have access to the sa credential. When you don't, you have to change the owner in the database.