Log Shipping SQL Server 2012

Question

I'm a developer at a small shop that doesn't have a DBA and i'm trying to get log shipping with sql server 2012 working. I'm trying to off load reporting from the transaction system to a new data warehouse and will use this db as a staging area.

I ran the log shipping wizard and the primary backup and file copy jobs works every time. The secondary restore job seems to randomly fail.

The primary server only has the one transaction log job. Differential backup is disabled (not sure if that matters) but does a have a full backup.

The secondary server is a fresh install with no maintenance plans, backups or active users.

Is there a way to force the backup back in sync, or always ensure it stays in sync?

It just seems so fragile. Please advise.

Redacted log below:

*Starting transaction log copy. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieving copy settings. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieved copy settings. 
Primary Server: '', 
Primary Database: 'db', Backup Source Directory: '\\server\folder', 
Backup Destination Directory: '\\server\folder', 
Last Copied File: '\\server\folder\db_20160105070002.trn'
Starting transaction log restore. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieving restore settings. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Copying log backup files. 
Primary Server: 'server', Primary Database: 'db', 
Backup Source Directory: '\\server\folder', 
Backup Destination Directory: '\\server\folder'
Retrieved common restore settings. 
Primary Server: 'server', 
Primary Database: 'db', 
Backup Destination Directory: '\\server\folder', 
File Retention Period: 14400 minute(s)
Retrieved database restore settings. 
Secondary Database: 'db', 
Restore Delay: 10, 
Restore All: True, 
Restore Mode: Standby, 
Disconnect Users: True, 
Last Restored File: \\server\folder\db_20160105060002.trn, 
Block Size: Not Specified, 
Buffer Count: Not Specified, 
Max Transfer Size: Not Specified
Disconnecting users. 
Secondary DB: 'db'
Copying log backup file to temporary work file.
 Source: '\\server\folder\db_20160105080001.trn', 
Destination: '\\server\folder\db_20160105080001.wrk'
Renamed temporary work file. 
Source: '\\server\folder\db_20160105080001.wrk',
Destination: '\\server\folder\db_20160105080001.trn'
Checking to see if any previously copied log backup files that are required by the restore operation are missing. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
The copy operation was successful. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7', 
Number of log backup files copied: 1
An error occurred restoring the database access mode. (Alter failed for Database 'db'. )
The file '\\server\folder\db_20160105070002.trn' is too recent to apply to the secondary database 'db'. 
(The log in this backup set begins at LSN 52498000002221000001, which is too recent to apply to the database. An earlier log backup that includes LSN 52498000002197900001 can be restored.
RESTORE LOG is terminating abnormally.)
Searching for an older log backup file. 
Secondary Database: 'db'
Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105060002.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105050001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105040001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105030001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105020000.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105010001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105000001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104230001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104220001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104210001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104200001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104190004.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104180000.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104170002.trn'

Could not find a log backup file that could be applied to secondary database 'db'.
Deleting old log backup files. Primary Database: 'db'

The restore operation completed with errors. Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'*

---

UPDATE: Running below query's theres some odd transaction log backup (maybe)

The NUL is whats in the table. No idea why its not NULL

This is Backup Finish Time, Device, Type

2016-01-08 02:00:01.000 D:\Folder\DB_20160108090001.trn Log

2016-01-08 01:00:01.000 D:\Folder\DB_20160108080001.trn Log

2016-01-08 00:00:00.000 D:\Folder\DB_20160108070000.trn Log

2016-01-07 23:46:41.000 NUL Log

2016-01-07 23:41:07.000 {51C661F9-2DC2-4424-913F-B9CFADA69FEE}1 Database

2016-01-07 23:00:01.000 D:\Folder\DB_20160108060001.trn Log

Answer 1

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;