Sql-server – Log Shipping and Daily Backup

If something is still setup on the secondary server you need to run sp_delete_log_shipping_secondary_database there.

You can clear the log shipping configuration on the primary by executing sp_delete_log_shipping_primary_secondary

USE master;
GO
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'
GO

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;