Not too long ago, I was visiting a customer. They presented me with a conundrum: How come half of our registrations from our ShopFloor system are not imported to our live Dynamics NAV. When we test the import functionality, everything works just fine.

My first question was: “did you make a testing database recently?”.

Creating testing databases, it is imperative that you stop all scheduled jobs running in the database, as I have blogged about in my previous blog: Automated preparation of Test Companies in Dynamics NAV.

The IT-manager was 100% certain that no testing databases had been created. And every time they DID create testing databases, a procedure was followed as described in my blogpost.

However, I insisted that we tested all the installed database copies for running jobs, and we actually found a database where the procedure had not been followed, so that the scheduled jobs were still running and approx. half of the ShopFloor transactions were in that database instead of the live database.

It turned out that a consultant had created the copy to establish a testing database to implement an upgrade to the system. The consultant had not thought of asking for the procedure to create testing databases.

But that was not the worst.

Creating a testing database is usually done by creating a copy of the live database and then restoring the database with a new name.

So far, so good.

But depending of the database recovery model, SQL server supports multiple types of backups:

Full backup

All data are backed up.

Any differential and transaction log backups are reset.

Differential backup

All data that were processed since the last full backup.

Any transaction log backups are reset.

Transaction Log backup

All transactions processed since:

• The last full backup
• The last differential backup
• The last transaction backup

A backup plan could look like this:

This means that if I make a full backup to another file destination that the normal backup, in the middle of the week, in order to make a testing database, I also break my backup plan, and all my differential backups and transaction log backups after that are worthless.

Copy-Only backups

Therefore, in order to make an extra backup for testing or other purposes, the backup must be made without interfering with the backup plan, and that is wat Copy-Only backups are for.

When creating the backup, all you need to do, is to put a check mark in the Copy-Only Backup field.

Then the normal backup plan will not be affected by the full backup.

In T-SQLK it looks like this:

And in SSMS it looks like this:

In this case they were lucky and nothing happened, but knowing Murphy’s Law, that is exactly the time the hard drives crashes.