none
Tran Log backup to network backup device fails RRS feed

  • Question

  • Transaction logs for two databases on new SQL 2017 server are configured to backup to network-based backup devices using full UNC paths.  Job succeeds the first time, then fails after that with errors 3041 and 3266.  Full backups to same network location complete successfully.  Tran log backups to backup device pointed to local drive work fine.  It seems if permissions were the issue, the job would fail the first time.  Ideas?
    Friday, December 6, 2019 3:37 PM

All replies

  • Do you can post complete error messages from error log?
    Friday, December 6, 2019 5:11 PM
  • This is what appears in the SQL Server Log:

    12/06/2019 09:30:02,Backup,Unknown,BACKUP failed to complete the command BACKUP LOG SIR_Images. Check the backup application log for detailed messages.
    12/06/2019 09:30:02,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
    12/06/2019 09:30:02,spid83,Unknown,The backup data at the end of "Tranlog-SIR_Images-corpfs1(\\corpfs1\SQL_Backups\Tranlog-SIR_Images-corpfs1.bak)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media<c/> use RESTORE HEADERONLY. To determine the usability of the backup sets<c/> run RESTORE VERIFYONLY. If all of the backup sets are incomplete<c/> reformat the media using BACKUP WITH FORMAT<c/> which destroys all the backup sets.
    12/06/2019 09:30:02,spid83,Unknown,Error: 3266<c/> Severity: 16<c/> State: 1.
    12/06/2019 09:30:02,spid83,Unknown,The backup data at the end of "Tranlog-SIR_Images-corpfs1(\\corpfs1\SQL_Backups\Tranlog-SIR_Images-corpfs1.bak)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media<c/> use RESTORE HEADERONLY. To determine the usability of the backup sets<c/> run RESTORE VERIFYONLY. If all of the backup sets are incomplete<c/> reformat the media using BACKUP WITH FORMAT<c/> which destroys all the backup sets.
    12/06/2019 09:30:02,spid83,Unknown,Error: 3266<c/> Severity: 16<c/> State: 1.
    12/06/2019 09:30:01,Backup,Unknown,Log was backed up. Database: ReportServer<c/> creation date(time): 2019/10/17(17:00:34)<c/> first LSN: 41:19794:1<c/> last LSN: 41:19798:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'Tranlog-ReportServer3-corpfs1'}). This is an informational message only. No user action is required.

    And here are the entries from the Job History log:

    12/06/2019 09:30:00,Transaction Log Backups.Subplan_1,Error,,CORPSQL3,Transaction Log Backups.Subplan_1,,,The job failed.  The Job was invoked by Schedule 13 (Transaction Log Backups.Subplan_1).  The last step to run was step 1 (Subplan_1).,00:00:02,0,0,Sherri Meachum,,,0
    12/06/2019 09:30:01,Transaction Log Backups.Subplan_1,Error,1,CORPSQL3,Transaction Log Backups.Subplan_1,Subplan_1,,Executed as user: SCHUPAN\mssql. Microsoft (R) SQL Server Execute Package Utility  Version 14.0.1000.169 for 64-bit  Copyright (C) 2017 Microsoft. All rights reserved.    Started:  9:30:01 AM  Progress: 2019-12-06 09:30:01.42     Source: {0CDF7E7D-F8E3-4B9E-84FE-2FF4358319CA}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2019-12-06 09:30:01.87     Source: ReportServer Tranlog Backup      Executing query "BACKUP LOG [ReportServer] TO  [Tranlog-ReportServe...".: 100% complete  End Progress  Error: 2019-12-06 09:30:02.01     Code: 0xC002F210     Source: SIR Images Tranlog Backup Execute SQL Task     Description: Executing the query "BACKUP LOG [SIR_Images] TO  [Tranlog-SIR_Images-co..." failed with the following error: "The backup data at the end of "Tranlog-SIR_Images-corpfs1(\\corpfs1\SQL_Backups\Tranlog-SIR_Images-corpfs1.

    Friday, December 6, 2019 6:36 PM
  • My conclusion of that output is that the log backup fails already the first time, only silently. That is, the backup is written but the contents is garbled for some reason. Since you attempt to reuse the log device, the garbled is detected the second time.

    You say that full backups to the same location works. How do you know? Have you tested to restore on of those backups?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 6, 2019 10:30 PM
  • What is database size?Which type backup you are performing?

    Try take compress backup which will save your space.

    if database is small then take local backup and then copy on destination server using ROBOCOPY.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Saturday, December 7, 2019 3:03 AM
  • Hi Meach77,

    Message 3041 is a generic report about the backup failure. To understand the cause of the error and resolve it, you must use the SQL Server Error log entries that occur before the 3041 event entry. Typically, 3041 is preceded by a detailed error message that states the real reason for the backup failure.

    For error 3041 and error 3266, please follow below documents to troubleshooting. Please refer to How to Fix SQL Server Error 3041 Error message 3266 when you perform a database backup to disk.

    Hope this could help you.

    Best regards,
    Cathy 

    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

    Monday, December 9, 2019 8:25 AM
  • Is their a third party tool taking the backup or you are taking it via TSQL or maintenance plan ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, December 9, 2019 11:48 AM
    Moderator
  • Thank you for your reply.  The full backups are able to overwrite each night, while the transaction log backups are not able to overwrite. 
    Monday, December 9, 2019 9:26 PM
  • This is occurring with two different databases, but the transaction log backups are both small.  Capacity to write the files is not an issue.
    Monday, December 9, 2019 9:27 PM
  • It is through a Maintenance Plan.  
    Monday, December 9, 2019 9:28 PM
  • Thank you for your reply.  The full text of the error log is posted above.  There wasn't anything that preceeded error 3041.  I'm familiar with the document you pointed me to. 

    We are not running a third-party application to perform the backups.  They are run through a Maintenance Plan. 

    The backup path exists and the backup file is created successfully the first time the job runs.

    There are no other processes accessing the transaction log backup file.

    The SQL service login account has full access to the destination directory.

    There is plenty of space to write the backup files.

    We did recently migrate one of the databases from a MS SQL 2012 server to the current MS SQL 2017 server, but the other database that is having tranlog backup issues is the natively created ReportServer database.  While the document mentions that this may be an issue, it doesn't say what, if anything, can be done about it.

    For what it's worth, I have the tranlog backups running successfully to a local disk location.  It's only when I try to point the backup device to any other location that I run into problems.  My SQL 2012 server doesn't have these issues.

    Monday, December 9, 2019 9:43 PM
  • Thank you for your reply.  The full backups are able to overwrite each night, while the transaction log backups are not able to overwrite. 

    But are you able to restore the backups? Are you able to restore the single transaction log you can write to the network drive?

    This is a far more important question than whether you can write a second file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 9, 2019 10:36 PM
  • Was the files created the first time? If they were created, what if you delete it first and try again. Perhaps it fails because the file exists and it isn't a valid log backup file?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, December 10, 2019 10:18 AM
    Moderator
  • The files are successfully created the first time the job runs.  If I delete the existing tranlog.bak file, it will re-create it, but it only works once or twice after that and I'd have to delete the file again.

    As long as the destination for the backup device is a local drive, not a network share, the backup jobs run just fine.  It's only when I try to backup the tranlogs to a network share that they fail.  I seem to recall reading about problems backing up to a network share a long time ago, but I can't find any solutions for that now.

    Wednesday, December 11, 2019 8:26 PM