It just seems so fragile.
Logshipping is tested and proved since sql server 2000 (and even older) days. Its not fragile.
Look at the errors ...
Last Restored File: \server\folder\db_20160105060002.trn,
Logshipping is trying to restore
Destination: '\server\folder\db_20160105080001.trn'
This means you have a gap in the log sequence. There might be adhoc log backups happening which is breaking the log chain.
Refer to my answer - How does Log shipping knows to keep track.
You can even Restrict users to COPY ONLY log backups, so that adhoc log backups wont break the log chain. Also,
@Spörri made a valid point to disable SQL VSS writer service, so that 3rd party backup tool cannot interact with SQL. Its a pain to find that out, since 3rd party softwares are crazy sometimes !
To find out gaps in your log backups, you can use below query
SELECT
s.database_name,s.backup_finish_date,y.physical_device_name
FROM
msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
(s.database_name = 'databaseNamePrimaryServer')
ORDER BY
s.backup_finish_date DESC;
Another useful query:
-- http://sqlblog.com/blogs/tibor_karaszi/archive/2014/11/03/can-you-restore-from-your-backups-are-you-sure.aspx
-- modified by Kin to include backup start and finish dates
SELECT TOP(100)
database_name
,CASE bs.TYPE
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file '
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,bs.backup_start_date
,bs.backup_finish_date
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id
where database_name = 'master' -- change here for your database
ORDER BY backup_finish_date DESC;
Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?
A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain tracking bitmaps. This means your differential backups will eventually be the size of your database.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server
Best Answer
Great job on troubleshooting, and yes, you are correct. Ola Hallengren's backup scripts need a change as described in this Github issue for his scripts:
https://github.com/olahallengren/sql-server-maintenance-solution/issues/12
Ola hasn't accepted the pull request yet (he's fairly new to Github), so if you want to use my fixed version with the new parameters I describe in that issue, here's my repo:
https://github.com/BrentOzarULTD/sql-server-maintenance-solution
If all that sounds too scary - and I wouldn't blame you - then you'll need to seed the restores yourself manually for each new database.