asked
Skipped log backup file log shipping
Dear Guys, I am sort of novice as a DBA. I just started learning about log shipping. I did a test on my own computer that I have created a database called 'Test', then I restored to a new database 'Test2'. FYI, I used with norecovery while doing the restoring the database. While restoring process is on going. I create a log shipping, Database Test as primary database and Test2 as secondary database. Test2 is set to Stand By Mode. Then I tried to run the related jobs manually and it looks successfully done. But,there is one thing seems to be strange. I got Skipped log backup file log shipping. Secondary DB: Test2. Could not find a log backup file that could be applied to secondary database Test2. Any help would be very appreciated. Thanks.
If you miss applying a transaction log backup and another is taken it can't be applied as the Log Sequence Numbers are not in order - a gap has been created in the transaction log chain. This can happen if you have competing transaction log backups occurring. The most common of these is a Maintenance Plan doing TLog backups to one location and a Log Shipping job also doing TLog backups but to another location. To perform Full Recovery either of these needs a complete, uninterrupted set of backups. To correct this ensure you have only once authoritative creator of TLog backups going forward and, unfortunately, re-initialize your secondary from a new full backup.
Hi Greg, Many thanks for the prompt response. A couple hours ago, I did the same steps as I did previously. And the result is now completely correct. I found no skipped log backup file shipping. I have no idea what the root cause is. Probably what you explained could be the main problem. Again, many thanks, Greg. Regards, Hery
If you find yourself in the sort of situation that @blackhawk-17 describes in your production environment, then it might be advisable to either: - only have one backup job and regularly copy the files for the appropriate backups from one server to the other, or - use a more comprehensive backup system such as [Litespeed for SQL Server from Quest][1], or [Red Gate's SQL Backup][2] - these will allow for multiple backup targets so that you can have some databases being backed up into multiple locations.
(At least, I know the Quest one does, because I use it for that at work. Perhaps someone else can comment on whether Red Gate's product does this also.) [1]:
http://www.quest.com/litespeed-for-sql-server/ [2]:
http://www.red-gate.com/products/dba/sql-backup/
SQL Backup from RedGate has a built-in Log Shipping process if you choose to use it and they also have a labs tool (IE free but unsupported) called Log Shipping Monitor that lets you check log backup/restore performance and how up to date the whole process is. Get it for free at
http://www.red-gate.com/labs/free-tools/. It is wholly agnostic on how Log Shipping is configured so you dont need any other tools to be able to use it. I use SQL Backup for backup/restore processes but have manually configured Log Shipping and use Log Shipping Monitor to keep an eye on it all. Disclosure - I'm a Friend of RedGate so am more likely to offer a positive opinion of their tools than any others :)
Hi, have you seen the record in secondary database after making changes in primary database?? . i have facing that issue in live environment .for time consuming I have created temp table and insert the record .ran the log backup in primary server then ran the Lscopy and lsrestore in secondary database.i could see the record now in secondary database ,what i have worked in primary database is this right way to check the function?
10 People are following this question.
