Have you destroyed your SQL Backup plan today?

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 blog post.

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 full backup 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 or
  • The last differential backup or
  • The last transaction backup

A backup plan could look like this:

This means that if I make a full backup to another file destination than the normal backup in the middle of the week, in order to make a testing database, I also break my backup plan.

This means that all my differential backups and transaction log backups after that are worthless until the next full backup.

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 what 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-SQL 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 when the hard drives would crash