Navision SQL Server Option - SQL Backup vs Navision Backup

roland · 2006-05-11

Dear all,

I've a client who is on MBS Navision 4 on SQL Server, I just want to know is it possbile to make a succesful SQL backup when there are some users still doing data entry. And what about making a Navision backup when there are some users still doing data entry? Will the backups be complete, or will the tables which the users are accessing be ignored by the backup?

Thanks in advance for any answers to this noob question ;P

PoweRoy · 2006-05-11

hmm tricky question

if users are changing records that table is locked. Locked table means no acces, maybe no acces for the backup to work?

Maybe try to avoid that and schedule a backup during the night

kriki · 2006-05-11

You can use both backups during data-entry without problems.
The Navision backup WILL NOT backup changes done after starting the backup.
It is possible that the SQL-backup backups also changes done during the backup if the transaction is finished before the end of the backup. But I am not sure about this last thing.

kriki · 2006-05-11

BlackTiger wrote:

SQL backup will lock tables during process.

Use "HotCopy" for "hot" backup.

PS: Actually it's VERY BAD idea to backup data during data entry. Even HotCopy can produce broken backup.

I just tried a SQL backup meanwhile something was writing all the time in the DB and I noticed that the process was not blocked.
It is true that it is better to make a backup when no one is working (especially when running heavy batches) to not hinder performance unnecessary. So the best thing is : at night a full backup and during the day transaction-log backups.

ara3n · 2006-05-11

On SQL, if you want to backup while users are using the system, is to do a differential back. One of my clients is doing every 15 min the differential backup. It's a 30 gig db with 100 users on it.

ara3n · 2006-05-11

Differential Backup: A differential backup creates a copy of all the changes that have taken place in the database since the last full backup. The command is the same for a full database backup, with the addition of one clause:

BACKUP DATABASE [database_name] to disk = 'file_name' with differential

Now you can implement the following backup scheme: a full backup every Saturday, a differential backup every night, and a transaction log backup every hour. Once this scheme is in place, if you go down on Thursday evening, all you need to do is apply the full backup, the last differential backup (which would have been Wednesday evening), and all the transaction logs since—for a total of only 26 files.

This 10-Minute Solution covers the basics of the BACKUP command and the four types of backups that you can create. Before implementing a backup plan, perform additional research on these features by reading up on the BACKUP command in the Books Online manual. Additionally, you should remember to test your backup plan thoroughly by performing test restores on a regular basis.

kine · 2006-05-15

1) SQL backup does not lock tables! If yes, it will be impossible to do backup of most systems onthe world... ;-)
2) During backup, all data are going into transaction log, after backup, all changes are remade into database
3) If you are using Navision backup on SQL, you can have problems - deadlocks - with already running transactions from users. It is not 100% sure that beckup will be sucessfull, because Navision is locking table in sequence (Navision is not able to do "snapshot" of the DB, may be on MS SQL 2005 yes...)

ara3n · 2006-05-15

don't spread rumors about Nav v 5 kine.

Just kidding.

bbrown · 2006-05-15

A SQL backup is not dependent on table locks. It will result in a database snapshot as of the start of the backup. Inconsistent backups are not an isssue. It is recommended that full database backups not be run during times of high system usage.

When users are making changes to the SQL database, those changes (insert/modify/delete) are kept in memory and written to the transaction log. When the server issues a checkpoint, these changes are flushed to disk.

DenSter · 2006-05-16

BlackTiger wrote:

No SNAPSHOT support in Nav5.

I think we can safely just ignore this message, as it is not based on anything official from MS. I don't think they know themselves what will be supported in NAV 5.

Navision SQL Server Option - SQL Backup vs Navision Backup

Comments

Categories