August 7, 2012 at 5:14 pm
My log shipping is working fine but something I am unsure of.
If I do a manual log backup of a DB (to clear a log file, or to enable a log file shrinkage etc) and then copy that file to the secondary log shipping file folder then will the log restore job collect and restore this log file?
The ad-hoc log backup is in sequence and is a valid backup file but would have a different file name that I give it. i.e DBNAME_adhoc_20120808.trn).
I know the log shipping job stamps the .trn files in a certain format (DBNAME_20120807190200) but I am unsure if my ad-hoc backup will not get collected because of this , or if the log shipping restore job looks for the file attributes rather than the file name time stamp?
If this can't be done then log shipping would break if I do a manual t-sql log backup because the log shipping restore job could not restore the next log file in sequence even thought the file does actually exist in the correct location.
Or would I have manually restore this file onto the secondary database and then the log shipping job would look for the next file that would have been created by the log shipping process.?
thanks
August 9, 2012 at 6:20 am
taking manual log backup would break LSN chain and log shipping would not restore that backup, as a result log shipping would break.
For tackling transaction log space issues on databases configured for logshipping, there is no need to take manual backup, just start the log backup job, shrink the log file after job completes, often you need to take log backup twice for releasing space completely, no problem, run the job again.
Copy job and restore jobs would copy this backup file and restore on secondary database automatically.
August 9, 2012 at 6:23 am
You can use the copy_only option to take Log backup without breaking the LSN chain.
I believe the adhoc backup will have continuing LSN and placing that in log shipping folder will work .Waiting for experts comment.
August 9, 2012 at 10:06 pm
uncle_boris (8/7/2012)
Or would I have manually restore this file onto the secondary database and then the log shipping job would look for the next file that would have been created by the log shipping process.?
Yes that will solve your problem. Taking a manual backup will not 'break' log shipping. The log ship restore job will fail because it will try to restore the next transaction log backup that was initiated by the log-ship process. However, if you copy the backup you took across to your secondary server and restore it manually, log shipping will continue quite happily.
As MI_DBA mentions, though, just execute the Log backup job if you need to truncate the transaction log (I need to do this from time to time).
Executing a log backup with copy_only would be fairly pointless since it would not truncate the transaction log which, I think, is the point of the exercise in your case 🙂
August 9, 2012 at 10:25 pm
yes, that is correct. A process blew the log file out and the database was big so I could not just change it to Simple model or truncate the log as I did not want to do a full back up after this.
I think the easiest way in the future would be to just manually run the log shipping job once (or twice if needed) to clear the logs so they file can be shrunk.
thank you for the replies.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply