Hi,
I have the backup of a 85GB database taken both in SQL and Navision.
But, restoring the database in Navision will requrire more time compared to restoring database in SQL.
Will restoring from SQL on a newly created database have the same effect of restoring the backup in Navision?
Regards…
The answer is no.
If I have the time, then I would surely take the option of restoring the NAV backup instead of the SQL backup.
With the NAV backup you will get a much better indexed and less defragmented database, as all indexes etc. are created from a clean database. With the SQL backup you just copy the “exact” structure of the old database.
But of cause it depends on why you want to restore also.
Hi Erik, thanks for the reply.
Right now, we’re in the process of Upgrading one of our major client from 3.7 HF 11 to 4.0 SP3 HF 6. The database size is around 90GB.
We’re following the Upgrade Toolkit methodology to do this upgrade. So, we tested the 3.7 DB, took a Navision as well as SQL backup. After installing the new 4.0 client, we created a new database. Then, we decided on restoring the Navision backup on the new database. We started the process yesterday morning and still the process is going on. So, now we’re in a dilemma that restoring the SQL backup on the new 4.0 database would’ve been a better and a faster method in doing this upgrade.
Thanx and Regards.
Interesting response from Erik (in that it is the opposite to my opinion - I guess it depends on personal experience). I would always restore from SQL. I have no experience of what Erik mentions but I just go for what is quicker. You can always rebuild the indexes if they do get fragmented. You should probably update your statistics as well as soon as the restore is finished. I would recomend setting the recovery mode to simple as the restore is done in a single transaction which means your data restore and indexing is going to blow your log file out of all proportions. Once your restore is complete you can truncate your log file and change the recovery mode (although I doubt you will want point in time recovery if you are just doing an upgrade).
I always prefer a SQL backup because very often we have some stored procs/triggers/SQL views and other stuff in the NAV database. If I get a SQL backup I know I have got everything. If I get a NAV backup then I have the NAV data and that is it.
I agree with Gaspode. I will prefer SQL restore.
Hello,
I fully agree with Erik.
For restoring 90Gb FBK (Native) You will need about 200-250GB free space and about 30 hours (in my case).
Br,
Igor Beeone
On a day to day basis I wouldn’t recommend anyone running NAV on a SQL Server to use the NAV backups. I would always use the SQL Server backup in my maintenance plans. But using the NAV backups and restore is actually also a good test of the database integritety.
My company has just moved our database to a new SQL Server hosting provider. And in this connection we first used a SQL Server backup, which restored to our first test database. But then we where given the advise to use the NAV backup and restore this instead. Not only did we experience quite a few database errors (caused by a mix up in the different codepages used - lowercase characters in code fields), but we actually got a much faster system.
It took us about 10 hours to restore the 50GB database. Compared to the about 2 hours to restore using the SQL Server backup, then of cause this was a long time, which typically not would be acceptable if the restore was due to a malfunctional and damaged database, with users waiting to get productive again. But here it was a planned move, done at a time where users where told not to work (a Saturday morning/afternoon CET).
The experience we had after having done this task was that the database we have gotten using the NAV backup was remarkable faster than the database we had created using the SQL backup.
My only explaination to this is that when using the NAV backup, then everything is put into the database ordered “correctly” by company and table and with no defragmentation. When using the SQL backup you basically “just” copy the existing (maybe) disorganized database structure.
I might be wrong about the theory, but my experience was that it was a much faster database. And that’s really what matter to me…
Me too, if you are talking about regular backups and restores for the same version database. So for instance to refresh the development database with a recent copy of the production database. In this instance however, he is upgrading from 3.7 to 4.0 SP3, and in that case I have to agree with Erik that it is better to create a new database with the new NAV client and use the NAV backup to restore the database. Not so much ecause of the data but because of the SIFT logic and new SQL Server properties that are not present in the 3.7 client.
This has nothing to do with trusting the SQL Server backup, but with the fact that it is better to go about an upgrade the ‘long and proper’ way than to do a SQL restore and just convert the database. 9 out of 10 cases it works fine, but if there are problems, they are huge. Better be safe than sorry.
Hi…
Thanks a lot for all those replies. I really got a nice insight on restoring what type of databases according to the need of hour.
To give you a fair idea of what’s happening with us, here is the detailed stuff:
One of our major approached us for an upgrade from 3.7 HF 11 to the latest 4.0 SP3 HF6. They have a 85 GB database and growing. They’ve also provided us with a very powerful server just for the upgrade process.
After taking all the customizations from 3.7 to 4.0, we’re trying to do data migration on their full 85GB database on that server using the Upgrade Toolkit Methodology specified by Microsoft. In that process, we’ve created both the 3.7 and 4.0 Navision databases on SQL. When we took the NAV backup of the 85 GB database, it was divided into 12 files each of size around 2.5GB. It took 8+ hours to take the backup on the server. Now we created a new database on SQL using the 4.0 client, and then tried to restore in Navision the 3.7 backup on the new 4.0 database on the same server.
The server has two partitions: One for the OS which is Windows Server 2003 with 20 GB disk space and the other drive has a disk space of 510GB. But the restoration process is taking 2+ days for the 85GB database in Navision. The downtime for the actual data migration which the client has given us is on a weekend from Saturday afternoon to may be early morning of Monday.
We’re caught up in this tight situation and are in a dilemma as to which method is to be followed to do the data migration.
Some thought has been given to the method where we’re being advised to just copy the exact database from 3.7 to 4.0 (Even though there are 4 cases where the data type has changed from 3.7 to 4.0) which eventually would accomplish the task in the given time.
Can anyone suggest some better methods for this problem?
Thanx and Regards. [:(]
Rather than a backup restore, you can try just opening the old database with the new client executable. This will upgrade the database. You then need to run the data migration codeunits, etc. according to the upgrade toolkit.
Your server is also very poorly spec’d for SQL. You need to have the data files and log files on separate volumes but from the spec you have given it sounds like you just have one volume which is going to slow things dramatically. There is a Microsoft server sizing guide whitepaper that provides details of good server specs. I found that the fastest restore I could get was restoring from one volume (it was actually an external USB 2.0 HDD believe it or not) to a server that had seperate data and logs disk arrays. The whitepaper has good details on what to do and I think it recomends using RAID 10 mirroring and striping which can improve performance for the seperate volumes.
When you eventually get to the point of running your data conversion routines if any of the large tables are being updated and those tables have SIFT indexes, you should consider disabling the maintain SIFT Indexes in SQL option for those tables and then re-activating it once the data conversion routines have completed. I have found massive performance gains by doing this - but you have not even got to that stage yet.
85GB is pretty big. I have heard of RAM disks that you can hire which will run like hot snot. You may need to consider this to cutover in a weekend.
Have fun and keep posting to let us know how it goes.
Thanx Gaspode for all those valuable comments.
We’ll surely consider dividing our hard drive into atleast four partitions before doing the restore or upgrade next time.
My question is: Will converting the database from 3.7 to 4.0 have the same effect as restoring a 3.7 database in 4.0?
In the upgrade toolkit I read (3.70 to 5.0) it said that we could do either backup/restore or open with new client. This suggests to me it has the same effect.
Here is a quote from the document…
Create a new 5.0 database twice the size of the old database.
Restore the backup of the old customized database into the new 5.0 database.
Alternatively, open a copy of the existing database with the new 5.0 client, convert the database and expand it to be twice the size of the old database.
Thanks Gaspode.
At times we even lack our basic common sense. We were trying so hard to restore the database, we didn’t even try to think of that alternate method. Sure, we’ll keep the community updated with what’s happening in our Upgrade project.
By the way, the deadline to finish that is in April. Now you know why I’m posting so much everyday.
Seperate physical disk, not logical partitions of the same disk.
That’s not going to work. Multiple partitions on the same volume will still be on the same disks, and it will not increase the performance. You need to have separate physical disks or disk arrays for data files and log files. It is particularly important in this case to have a separate physical disk array that is used ONLY for the log file. I’d try setting up a 4 disk RAID10 for the log (don’t use RAID5 for this one), make sure that is has enough disk space for three times the database size, because it will grow that big when you restore the database.
Sorry Brian I replied without having seen your reply