tag:blogger.com,1999:blog-8466427570171426704.post6968108884466767367..comments2023-09-28T05:10:14.156-04:00Comments on SQL Server: Log shipping 'Could not find a log backup file that could be applied to secondary database'Unknownnoreply@blogger.comBlogger13125tag:blogger.com,1999:blog-8466427570171426704.post-34700421407036993102016-01-28T19:53:14.037-05:002016-01-28T19:53:14.037-05:00Only reconfiguring the log shipping jobs has helpe...Only reconfiguring the log shipping jobs has helped.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-65013984085461796522014-03-14T21:09:06.657-04:002014-03-14T21:09:06.657-04:00Just drop by to say thank you very much. You know ...Just drop by to say thank you very much. You know your article help the newbie like me a lot and save my day. <br />Thank you.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-70253427120754172902013-06-02T11:25:07.964-04:002013-06-02T11:25:07.964-04:00That message means that there was a log backup tak...That message means that there was a log backup taken before the one that you are trying to apply. Check the backup history using following query and change the backup_start_date to a time before the full backup was taken. It should tell you the exact sequence of log backups taken after the full backup. Get all those log backup files and restore them.<br /><br />SELECT s.database_name, <br />m.physical_device_name, <br />CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,<br />s.backup_start_date, backup_finish_date,<br />CASE s.[type] <br />WHEN 'D' THEN 'Full'<br />WHEN 'I' THEN 'Differential'<br />WHEN 'L' THEN 'Transaction Log'<br />END as BackupType, <br />s.server_name, <br />s.recovery_model<br />FROM msdb.dbo.backupset s <br />inner join msdb.dbo.backupmediafamily m<br />ON s.media_set_id = m.media_set_id<br />WHERE s.database_name = 'yourdbname' and backup_start_date > '2013-05-31'<br />ORDER BY backup_finish_dateAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-89595730818647105232013-05-31T09:32:25.645-04:002013-05-31T09:32:25.645-04:00my situation is not entirely the same. the new cl...my situation is not entirely the same. the new cloud server was in the process of being configured. in the meantime, ourorganization received the initial backup and .trn backup from log shipping. i defined the database on new cloud server. successfully, restored with standby the initial backup and first log file. i tried the next log file and got this <br /><br />Msg 4305, Level 16, State 1, Line 1<br />The log in this backup set begins at LSN 321055000000823500001, which is too recent to apply to the database. An earlier log backup that includes LSN 320945000019176800001 can be restored.<br />Msg 3013, Level 16, State 1, Line 1<br />RESTORE LOG is terminating abnormally.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-381313560000535482013-04-05T09:43:17.244-04:002013-04-05T09:43:17.244-04:00Firstly if you deleted any log backup files from p...Firstly if you deleted any log backup files from primary which were not yet copied to secondary then there is no other option but to re-initialize the secondary using a new full backup of the primary. <br /><br />Second, if log shipping was ever running properly then a full backup of the primary must have been taken. There is no way you can set up log shipping without taking a full backup primary.<br />Even if we assume that a full backup was taken before, then that is still not a good option. Because you have to take a full backup and all subsequent differential backups if you have to resync using the differential backups. Which means you will require that full backup and all diff backup after that.<br /><br />So the only way out is to take a new full backup, copy it to an external hard drive and ship it overnight to the secondary location. This can be used to restore and resycn the secondary.<br /><br />Once you lose the log backups on primary there is no other solution.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-7395955975126422612013-04-05T07:28:56.663-04:002013-04-05T07:28:56.663-04:00My situation :
There is a critical database at Pr...My situation : <br />There is a critical database at Primary datacenter and it is log-shipped this DB to 2 secondary servers which are geographically far away site and are connected to a MPLS dedicated Link.<br />38 .trn files were accidently deleted that were not copied at secondary site, hence restore processes is halted at secondary site but copy process is working fine and is copying rest of the files after that incident.<br /><br />Size of DB is : 380 GB and N/W BW is 20Mbps. so it is not possible to backup DB at primary, then copy at secondary and restore and then reconfigure the logshipping maintenance plan. Secondly no previous full backup was taken(I don't know the reason) on which differential backup could be taken and then copied at secondary and resore.<br /><br />This is a new problem for me , please give suggestion how to overcome it<br />thanks in advance<br /><br />P.S. : can deleting existing and creating a new maintenance plan in existing scenario helps?? <br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-13902894102873306162011-12-15T11:35:38.518-05:002011-12-15T11:35:38.518-05:00Looks like it needs a log backup file to restore b...Looks like it needs a log backup file to restore but that file has not yet been copied or has already been deleted from the primary. Please give me the output for the following two queries from secondary. Put a WHERE clause for that database name.<br /><br />SELECT * FROM [msdb].[dbo].[log_shipping_secondary]<br /><br /><br />SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]<br /><br />Next tell me the retention time for backups on Primary and secondary. Run the following query on Primary<br /><br />SELECT [primary_database] ,[backup_directory]<br /> ,[backup_retention_period] ,[last_backup_file]<br /> ,[last_backup_date]<br /> FROM [msdb].[dbo].[log_shipping_primary_databases] where Primary_database='Yourdbname'<br /><br />Next run the following query on secondary and give me the retention time for that database.<br /><br />SELECT [primary_database] ,[backup_source_directory]<br /> ,[backup_destination_directory] ,[file_retention_period]<br /> ,[last_copied_file]<br /> ,[last_copied_date]<br /> FROM [msdb].[dbo].[log_shipping_secondary] where Primary_Database='yourdbname'<br /><br />Lastly look at the backup directory on both primary and secondary and give me the file name and time of the earliest file.<br /><br />Now check what is the earliest file in the backup directory of the secondary server. If it is earlier than your full backup then we are good. But if you see that all backup files on secondary are after the full backup was taken then it means the backups files got deleted from primary before they could be copied.Kaykayhttps://www.blogger.com/profile/11055412207143922687noreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-12209304959513019762011-12-15T11:22:08.364-05:002011-12-15T11:22:08.364-05:00Ok I spoke too soon, it started up again, the erro...Ok I spoke too soon, it started up again, the error is as follows: "Error: Could not find a log backup file that could be applied to secondary database". When I compared the logs on both servers the copying and export is identical. I am starting to think SQL log shipping is severally broken.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-76709500902478255692011-12-14T11:48:39.847-05:002011-12-14T11:48:39.847-05:00I appreciate your help with this and did follow th...I appreciate your help with this and did follow through those steps and both the priamry server and the secondary server had the same logs on both of them. Unfortunately I could not spend any more time troubleshooting it and had to blow away the secondary DB and start from Scratch. Thanks you !Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-12551905024905963202011-12-14T09:35:11.821-05:002011-12-14T09:35:11.821-05:00Sometimes the copy job will complete successfully ...Sometimes the copy job will complete successfully but won't copy any file. <br />So check the time and name of the file that was last copied using the following query on the secondary server.<br />SELECT * FROM [msdb].[dbo].[log_shipping_secondary]<br /><br />Next check the job steps and paste the error here. There has to be some reason given in the job for the restore failure. I guess it's been more than a day since the log shipping has gone out of sync. So may be the backup file required for immediately has been deleted from the primary. <br /><br />Check each step in the restore job starting from the first one. <br /><br />First it tells us that it found the name of the last file that was restored. Next it will give you the first file name found in the directory. At this point it will try to restore it. If it is unsuccessful then it will say why. The most common reason is that this backup is too early. Which means that it missed restoring one of the previous backups which was been deleted already. In such situation just take a new full backup and restore it on secondary.Kaykayhttps://www.blogger.com/profile/11055412207143922687noreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-36207607511798167672011-12-14T08:15:49.547-05:002011-12-14T08:15:49.547-05:00The backup and copy from the primary to the second...The backup and copy from the primary to the secondary are showing completely succesful. On the Secondary server I am getting a no valid backup is found, but the data is the same in both the originating server and the secondary server.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-67306670199132252672011-12-13T21:36:24.321-05:002011-12-13T21:36:24.321-05:00You need to find the reason or error which is caus...You need to find the reason or error which is causing the failure. <br />On the primary there must be a job that starts with LS_Backup. Check the job history and look at the job steps and let me know what the error is.<br />On the secondary you will find two jobs starting with LS_Copy and LS_Restore. Check the job step history and find the errors.<br /><br />Check what is failing. Is it backup or copy or restore? Then check the reason. If you want more help let me know.Kaykayhttps://www.blogger.com/profile/11055412207143922687noreply@blogger.comtag:blogger.com,1999:blog-8466427570171426704.post-29595904791292560492011-12-13T18:19:45.596-05:002011-12-13T18:19:45.596-05:00So what do you do when both the Primary Server and...So what do you do when both the Primary Server and the Secondary Server match as far as files but SQL transaction log shipping is still not working?Anonymousnoreply@blogger.com