Issue with Scripting Log Shipping set up RRS feed

  • Question

  • I have been trying to set up log shipping, due to the number of database and on going maintenance i need to scrip this. 

    I have log shipping working have is set it up with SSMS and then generated a script from that so i know all the settings are correct.  

    however the scrip does not do the initial backup and restore which is where I assume I am going wrong. 

    my back up code is running at the start of the script and creating the bak file directly on the second server 

    here is an example of the code 

    BACKUP DATABASE [DBName] to DISK='UNCPathToSocondServer' BACKUP LOG [Database] to DISK='UNCPathToSocondServer')

    I then run the rest of the setup on the Primary server 

    Then do the restore on to the secondary server like so 

    RESTORE DATABASE [DatabaseName] FROM  DISK = 'LocalPathTo . bak file'

    WITH  FILE = 1,   
    MOVE N'LogicalName' TO N'MyDB.mdf', 
    MOVE N'LogicalNameLog' TO N'my.ldf''

    then run the rest of the setup on the secondary server. 

    everything seems to work but in the restore job it tells me that i can not find a valid trn file to restore but they are in the correct folder. 

    Regards Dagz

    Monday, January 13, 2020 5:48 PM

All replies

  • Is there another process which is taking a transaction log backup while you are trying to initialize log shipping? That error of an invalid trn file could either mean you are out of sync (meaning the t-log backup is not in sequence you are trying to restore) or the t-log backup was taken from some other means other than native sql server and it cannot read the file to restore it. 
    • Edited by rvsc48 Monday, January 13, 2020 6:45 PM edit
    Monday, January 13, 2020 6:45 PM
  • Hi Dagz,

    Does the sql server agent accont has permissions on the shared folder? Also did you check the transaction log shipping report to see if the copy process worked as well?

    Monday, January 13, 2020 7:27 PM
  • not doing Transaction logs no, I have a 3rd part backup tool doing full and Diff backups but that should not effect the transaction logs. 

    Regards Dagz

    Tuesday, January 14, 2020 8:35 AM
  • I think it must as this has worked and is working when i have set it up from SSMS 

    the exact message which is not classed as an error is  "Could not find a log backup file that could be applies to the Secondary database

    it also says it Sipped a file that is there. 


    Regards Dagz

    Tuesday, January 14, 2020 8:39 AM
  • Hi Dagz,

    Please check your SQL server service account has the read and write permission to the shared folder. Please check your SQL server agent service account also has the read and write permission to the shared folder. This can make sure jobs can work well.

    Please also check your steps for configuring log shipping from below documents to check if you miss some points.
    Step By Step SQL Server Log Shipping
    Configure Log Shipping (SQL Server)

    Please also check the files are in the correct folder location. Did you have any other log backup to the database that configuring log shipping?
    Best regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 15, 2020 9:32 AM