Perhaps someone can suggest a tweak, change or alternative to the custom log shipping process I have setup. A custom solution was needed because we only have Standard Edition that does not have backup compression and we need to move quite a large amount of data to a remove site.
One of the issues is that when I try to restore the full database from primary to secondary (if the log shipping process has been interrupted for some reason), the secondary is often stuck with the message 'restoring' and I cannot apply log backups after that.
The current process using SSIS is:
Zip backup with 7z
Copy zip to secondary
Restore backup to secondary
For a database that is in the region of 120 gb (compresses to just a few gb) this process takes about 10.5 hours, and most often gets the database stuck with restoring message mentioned earlier.
When restoring a transaction log the size runs at about 1 - 4 gb depending on the time of day and often fails.
The whole process if not at all fault tolerant and 'falls over' with the slightest problem.
Is there anything else I should take into account, or does log shipping generally require a fair amount of manual intervention?
Any suggestion and info from the experts out there would be great!:-D