Post reply

Transaction log backup/restore question

  • September 21, 2006 at 4:38 am

    #61826

    Hi all,

    I'm working on improving our home-grown log shipping setup at present and have hit an issue with the log application bit, hoping someone can help.

    Basically if I take a transaction log backup specifying WITH INIT and apply it to the copy of the db on the secondary server it works fine.  If I take one backup WITH INIT and then append another set of transaction logs to the same backup file by specifying WITH NOINIT (i.e. two lots of changes in one file) it also applies to the secondary just fine.  However, if I then take another lot on their own (WITH INIT again), they won't apply to the secondary, I get the message telling me that the log begins at LSN such-and-such and it is too late to apply to this database.

    I know specifying "WITH INIT" replaces the backups in the media set (a file on disk in our case) but leaves the media header intact.  Should I be using "WITH FORMAT" or something like that instead of WITH INIT for the times I want a clean, single transaction log backup set?  Or am I wrong in assuming that "RESTORE LOG [dbname] FROM DISK = 'I:\Standby\dbname_LOG.BAK' WITH STANDBY = 'H:\SQLServer\MSSQL\Data\dbname_undo.ldf'" is sufficient to apply all sets of transaction logs in the named file, i.e. should I be specifying some other arguments?

    I've checked BOL for this and still not getting anywhere, hope someone can help!

    Thanks in advance

    Niall

  • September 21, 2006 at 9:28 am

    #661792

    Niall,

    I am going to implement the custom log shipping and I am planning to use one backup per file at a time. I am planning to have logs being generated by the maintenance plan with date and time appended to the file name and may use VB script to parse the file name. I am currently in the process of selecting a better way of tracking log files and writing scripts.

    Regards,Yelena Varsha

  • September 22, 2006 at 6:10 am

    #661950

    Hi Yelena,

    Thanks for your reply.  I gather from looking at the built in log shipping in SQL Server that it works as you describe, rather than appending logs onto one file it generates a new file each time round.  I think I will probably go down that route for my solution also.

    Thanks again and good luck with your new log shipping system!

    Regards,

    Niall

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply