How to check consistency of NAV on SQL database

Johannes_Nielsen

Hi

Running NAV 5.0 on MS SQL 2008

I'm trying to figure out how to ensure that my backup routines are up to scratch.

As I've been told, the NAV database can be corrupt without SQL server knowing about it. I a worst case scenario this could mean one would be performing backups that are not restoreable.
One should do a restore test, for sure, but I'm not sure running it every day is nessasary...

But how Should you check for inconsistency of a NAV on SQL?

• By running the Test database from File>Database...
• By running some SQL utility
• By doing a FBK backup and restoring this (rebuild all keys)

This thread states that doineg FBK backups on SQL is best avoided
http://www.mibuso.com/forum/viewtopic.php?f=23&t=26350&hilit=FBK+not

So I'm confused :-k

Any insights are welcome :P

ara3n

If you want to make sure SQL backup is good.

Restore it on another server, and do a NAV backup. You don't need to restore the nav backup.


I have not seen a sql db getting corrupted on it's own.

What I have seen is hardware failure that corrupted sql db.
Or modifying data directly through sql. E.g. inserting lowercase on code field

stryk

To check the database integrity:

1. You should ensure that the database property "Page Verify" is set to "CHECKSUM", which is some kind of on-the-fly check up
2. Run a DBCC CHECKDB periodically (e.g. via TSQL or Maintenance Plan). This checks the physical integrity of the SQL Server database.
3. Optionally you could run the NAV internal "Database Test" to verify the logical integrity

Generally skip the old FBK native backup stuff with SQL Server - takes too long, causes too much workload and finally tells shit about the real SQL integrity (excuse my French) ...

A SQL Backup is a 1:1 copy of the database (more or less, actually all occupied Pages), thus potential problems are included.

Johannes_Nielsen

Thanks for the replies guys

stryk wrote:

To check the database integrity:

2. Run a DBCC CHECKDB periodically (e.g. via TSQL or Maintenance Plan). This checks the physical integrity of the SQL Server database.
3. Optionally you could run the NAV internal "Database Test" to verify the logical integrity

A SQL Backup is a 1:1 copy of the database (more or less, actually all occupied Pages), thus potential problems are included.

Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?

Johannes_Nielsen

ara3n wrote:

What I have seen is hardware failure that corrupted sql db.
Or modifying data directly through sql. E.g. inserting lowercase on code field

That correspons with the stories of network problems or long time running reports and hardware errors, I've heard of, could cause corruption of the datebase.

stryk

Johannes Nielsen wrote:

Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?

No. Actually I think the NAV internal DB TEst is rather pointless. The physical integrity is what really matters - DBCC CHECKDB.
The logical stuff in NAV - Table Relations etc. - has IMHO pretty low priority ...

Johannes_Nielsen

stryk wrote:

Johannes Nielsen wrote:

Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?

No. Actually I think the NAV internal DB TEst is rather pointless. The physical integrity is what really matters - DBCC CHECKDB.
The logical stuff in NAV - Table Relations etc. - has IMHO pretty low priority ...

Okay, so I guess I might have been misinformed about NAV being corrupted, without SQL being so.

Thanks for your insights guys!