This problem is by product design. U cannot have two backup plans for the same database - either u choose backups or Log shipping, because they essentially mean the same thing with or without restoring a read-only copy of the backup.
When u take a Full backup SQL Server stores the ending LSNs, which becomes the starting LSN of the next Log backup (if DB is in Full Recovery/Bulk-Logged Model). Lets suppose the LSN is 1000. Then u backup the log after 15 minutes and it starts from 1000 LSN and lets suppose that the log backup ending LSN is 2000. In logshipping the full backup occurs only once and all the remaining backups are Log backups - so a sequence of LSNs is maintained throughout.
But u have another Maint Plan that takes a Full Database Backup of the same database again and now the new LSN becomes 3000 (suppose). SO the next Log backup starts at 3000 instead of 2000, which was supposed to be the starting point. SO when the new log backup ships over to the secondary server, the read-only DB expects LSN 2000 but u r shipping LSN 3000 and hence:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 7000000026200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000015100001 can be restored.[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
Remove your second Maint Plan and Logshipping will start working.
Hi
I have a similar problem. Since I have standard edition I have written my own log shipping routines which work fine all week. Every night there is a full backup followed by a transaction log backup. Only the transaction log is shipped. Then during the working day the transaction logs are backed up and shipped every fifteen minutes.
This works fine until the weekend, when there is a database re-organisation and maintenance job. The transaction log created after this is huge, over 200Mb, (normally they are less than 1Mb), and although the steps which ship the log apparently succeed, it appears that the restore to the target server doesn't complete, because the next log to ship fails with the message.
Executed as user: sa. RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) The log in this backup set begins at LSN 294000007385600001, which is too late to apply to the database. An earlier log backup that includes LSN 290000004039000001 can be restored. [SQLSTATE 42000] (Error 4305). The step failed.
The full database backups do not seem to interfere with the log shipping. I ship logs all week with an unshipped full backup every night without error. I have tried shipping database backups, which are about 700Mb, but they seem to suffer the same problem, the job succeeds but the restore does not complete.
I am wondering, is there some sort of timeout or file size limit on the larger restore jobs which prevents them completing?
david
Tryinig to make sense of it all, Gagandeep's solution seems to be the answer.
David, I also experience the logs being shipped but error at the restore stage.
And to be clear with Gangandeep: you cannot have a maintenance plan of full backups with a maintenance plan of log shipping ?
David, you have the same problem, although manually created by yourself. The FULL database backups WILL interfere with your customised log shipping for the same reason that the SQL native log shipping will fail. In your case David, after taking a full backup, you must then ship this full backup over to the other server and restore it - then you can ship & restore your transaction logs each night. BUT, if you take another full backup, you must ship this over and restore it, and then apply its transaction logs.
The same situation arises when using any file system backup tool that uses archive bits, etc. The transactional backups that you take build upon firstly the most recent FULL backup and then the most recent differential backup (neither of you are using these) and then each transaction log backup in sequence after that. You cannot take a full backup, then have some log backups, and then take both a full backup and a log backup and expect the logs to continue building upon eachother without capturing the full backup in the middle as well... Unfortunately it doesn't work like that.
If it did work, then any work done in the DB between the last transaction log backup and the following full backup would be lost as the full backup would record these and the log backup would not see them.
As a workaround, if you really want to have a local full backup of your DB but still do your log shipping, you should just do your initial full backup, then your log backups forever after. For a local full backup, detach your database, back up its files using any file backup utility, then attach the database. Your DB will be offline for the duration of the full backup.
Let us know how you go!
Hi,
I have solved one part of my problem There IS a timeout setting when executing remote stored procedures. On the Properties dialog of the source database server (the one from which the backups are being generated) look for the Connections tab and check the Query Time Out (sec. 0=unlimited) setting.
This setting is the timeout for queries executed on a remote connection (eg: linked servers). The default seems to be 600s (ten minutes), but on my production server, for reasons lost to history, it was set to 60s!
If you are trying to restore a 200K transaction log, this is not a problem. But for a 200Mb log or 600Mb database, the query will time out if it takes more than 10 minutes (or 1 minute on my server). However, it does not seem to generate an error! The job history reports success, but the remote procedure step history does not include the text from the restore operation, and subsequent transaction logs fail because the restore never completed
Changing this query timeout setting to 900s gave the query enough time to execute the remote stored procedure and perform a 600Mb database restore. I will wait and see if the transaction logs continue to restore correctly.
Cheers for your help!
David
Thanks for posting the solution - nice to know how these problems are solved. Didn't occur to me that you were using remote proc execution - even then I wouldn't have thought about the timeout. Something to keep in mind for the future
Hope the rest of it keeps functioning for you too!
I've never played with the proper "log shipping" feature of SQL Enterprise so I am not sure. But the idea of "log shipping" is to have the initial full backup shipped and then you only ship the incremental changes to the database (the log backups). I imagine there might be a way to have it refresh periodically with a full backup - this would be akin to using replication and sending out a new snapshot... If I have some time later I might investigate it...
