Home / server / Questions / 185123
In Process
MrEdmundo
Asked: 2010-09-28 08:52:43 +0800 CST

Copy Transaction Log backup from Primary to non secondary server

  • 772

I have a SQL Server 2005 log shipping set up, with a primary server and two secondary servers. The process goes like this:

  • Primary backs up to independent network drive (not on any of database servers)
  • Secondary(s) copy from network drive to local drive
  • Secondary(s) restore locally

This has been working well for sometime, however, recently first thing in the morning the larger databases are raising exceptions when backing up the first transaction log of the day.

The exception follows this theme:

BackupDiskFile::RequestDurableMedia: failure on backup device '**_20100927050120.trn'. Operating system error 64(The specified network name is no longer available.)

I've researched this error and any others and it seems that directly backing up to a network drive isn't advised (even though supported) as the network can cause unreliable backups.

That's fine, I'll heed that advice and backup the logs up locally first and then copy them to my independent drive. The reason I want an independent network drive is because I want the backups somewhere off a database server and also to keep them for 3-5 days and the Primary server does not have space for this.

What I would like to know is, what is the best way to go about copying these files from the primary server to the independent drive, so the secondary servers can then copy them to their local drives?

sql-server backup transaction-log log-shipping

1 Answers

  1. I have a SQL Agent task that runs the following:

    C:\Windows\System32\XCOPY.EXE /Y D:\Backups\file "\\target\share\file"

    I use this for a stage -> production task, but you could use it to copy to any drive.

    XCOPY will fail with an error message that SQL Agent can trap and report on.

    • 1