How to 'update' the already existing NAV database in the SQL Server?

Hi,

In my company ( where I work ), NAV Technical Consultants (like me) have to travel to our Customers location on a regular basis. In case we’re not able to solve an issue on site, we request for the backup of their database from the System Administrator, bring the same to office, restore the database on a SQL Server ( if many people want to work) or on a standalone machine and then work on it. All of the customers are running SQL Server as their Navision backend server.

Everytime we get a new database from our Customer, we invariably have to create a NEW database on the server or on the standalone machine and then restore the new backup. This will create a new database for every new backup we restore.

My question here is this:

Can we restore the new backup of a Customer on an already existing database on our SQL Server?
Is there any way to update the database with new objects and other modifications using a backup?

Thanx.

Vikram

I assume you are getting a SQL backup and taking back to your office?!? If so, you should be able to just do a SQL Server restore over the existing SQL Server database (effectively just refreshing the data and objects - so be careful if you dont want to overwrite the objects - maybe export them to a fob file).

If you’re unsure about SQL Server database restores, refer to;

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E5BAE

Hope this helps.

No you can’t do that. Every customer has their own version of the database, so they will have different objects. The best way to do this is to create a new database for each customer. Of course you can delete an old database when you don’t need it anymore.

Hi…

The common practice we follow here is, we create a new database from Navision using the SQL Option. Then we restore the Navision Backup which we get from our client.

Every time we get a new Navision Backup, we create a new DB in SQL using the SQL Optioin for Navision and then we restore the DB. This creates two different databases of a Customer on the same server for two different Backups.

From Navision, can’t we restore the backup of a Customer on an already existing DB? Here the company name would remain the same. We want to have ONE database per customer and then, without deleting the databases everytime, want to restore the latest backup of that customer in to the database.

Hi Vikram,

What you are trying to do is the equivilent in Native database of resorting Company only. In your environment, there are two reasons not to do this.

  1. The object are a minor part of the database size, so it wont add much to the database restore time and wont affect size.
  2. There is the danger of you getting the objects out of sync, so really you want to import the objects everytime anyway.

What you should do, is simply delte the existing datbaase form the SQL server, then restore the new backup using the same Database name as the one you deleted.

Hi David,

Thank you for your reply.

We’re following the same procedure as you have mentioned. I was just curious to know whether there was some way using which we can just update the changes in the database ( like objects and the data added in time ) from the last time we restored the database for a customer.

Any such procudure if it existed would have been of great help as it would have save a lot of time during restoring subsequent backups.

You also need a new faster server, that will speed up the restores.

There is NO difference in the time that it takes whether you restore into a new database or into an existing one.

Your initial question was I thought about two different customers. Now it seems you are talking about a new version of the same customer. You can obviously do this, but it would involve deleting the old company, importing the latest objects and then restoring the backup. It would actually be quicker to simply delete the ‘old version’ and create a new one than to update the existing one.

SQL Server does provide the option to restore differential backups. Perhaps this is what you are looking for? I don’t know of anyone doing this for your purposes, though. You would had all of the differential backups between the time you restored your database (originally) and the time you decided to start working with the customer again.

I wonder what happens (or more likely, doesn’t happen) when you try to restore a differential backup but you are missing an intermediate differential backup.

Any thoughts Daniel?

A differential backup includes all changes since the last full backup. So losing an intermediate differential is a non-issue.

Lets say you do a full backup each night at midnight and then a differential every 3 hours (3 AM, 6 AM, 9 AM, 12 PM, 3 PM, 6 PM, 9 PM). The system fails at 9:15 PM. All you need restore is the 12 Midnight full backup and then the 9 PM differential backup. This recovers you to 9 PM. If you have transaction logs past that you can recover further.

However differential restores (or anything SQL except full) will only work if changes have not been made to the destination database

not disputing babrown, replying to Girish.

You’re not understanding how differential backups work, they don’t work like transaction log backups. Differential backups is a backup of all transactions since the last full backup. If you did a full back two days ago, and you did a diff backup yesterday, you’d get everything from two days ago until yesterday. You do another diff backup today, you get everything from two days ago until today. So the second differential includes the transactions that are in the first differential backup. You don’t use all differential backups to restore a database, only the most recent one. The way to restore a database is 1-the most recent full backup, 2-the most recent differential backup since that full backup, and 3-all transaction log backups since the most recent differential backup.

The easiest way to recreate a database, especially if it’s just for development purposes, is to just delete the old one, and restore a full backup into a new one.