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:
All data are backed up.
Any differential and transaction log backups are reset.
All data that were processed since the last full backup.
Any transaction log backups are reset.
Transaction Log backup
All transactions processed since:
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.
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
The only reason, that I can think of is performance making and restoring the backup. Otherwise, Yes you are right compressed backups is a good idea.
One of the other things I see, is people do not check the database option for compressed backups, so when they make backups they are always much larger than they need to be. Is there any good reason not to take advantage of this now that it is included in standard editions of SQL Server?
Yes that is the safe solution, but in a running live environment, that is not possible
What we always did in my previous projects were to first detach the database, then copy it, then reattach both, the copy as the new test. And the run a script/codeunit similar to your 2015 blog post.