If you don't include the transaction log, then it's not really log shipping, is it?
Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).
So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.
If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.
You can read more information about log shipping here:
http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx
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;
Best Answer
It has happened in our case wherein we had to restart our secondary server or log shipping monitoring server as some patches were getting applied and we don't have clustering on them. Our log shipping job is configured to take backup at primary server every 15 minutes, copying and restore also at every 15 minutes however there is gap of 5 minutes in between each of them considering network latency. Only impact we had was one of the job which was scheduled during that window got skipped however no impact on log shipping as lsn number caught up itself on the next copy and restore.
Based on above experience, I don't expect any issue to happen in your log shipping even if you don't disable any jobs as the backups will not be taken if primary is down , since remaining two jobs are on secondary server. Log sequence number will catch up on next backup.
I hope this helps.