Log shipping and regular backups – some questions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log shipping and regular backups – some questions

I have setup log shipping using the excellent article from this site.<br /><br />I am wondering if it would be wise to modify the job so that instead of creating two transaction logs (one for log shipping and one for regular backup use) every 15 minutes, I could create just the one and copy rather than move the log to the log shipping server…<br /><br />Ditto the full backups each night.<br /><br />Also – why does the logshipping article create BAK files for the TL logs – shouldn’t they be TRN files? What’s the difference and why wouldn’t you want individual TRN files to give you more control over the restore – which leads me to…..<br /><br />I am also of the opinion that you could stagger the restore of the TL logs so that you restore them say 15 minutes after they have been backed up so that if the DB fails you have a chance to stop the last log being applied…at least that way you have some control over whether the transaction that were created at the time of failure should be restored. Sensible?<br /><br />Thanks [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />
Once the database is set for FULL recovery model then any kind of recovery actions to be performed in case of failure will be taken care by SQL itself in addition to the log restores for that database. There is no difference between .BAK or .TRN extension and no performance difference too. Its a naming convention used to represent .BAK as backup and .TRN as transaction log backup. If the log shipping is taking good care of transaction logs then there is no requirement to enable or run another BACKUP LOG statement and which may lead to confusion during restore operation to the point of failure. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.