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?

  • You've answered your own question. I'd go and buy a cheap USB drive and back up to there, if you're having space issues on the server and cannot install a new drive. Disk space is cheap. – user3914 Sep 27 '10 at 21:06
  • Why would I buy disk space when I have an abundance on another server, which is raided and on the backup stack? – MrEdmundo Sep 28 '10 at 7:45

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.

  • Cheers Wayne I'll have a look. – MrEdmundo Sep 28 '10 at 7:45
  • Just out of interest, how did you get the name of the file to copy? – MrEdmundo Sep 28 '10 at 7:54
  • I use a standard file location and then use some batch scripting to figure out the newest file in that location. On other scripts, I just use a standard name for the file when dumping it. – Wayne Arthurton Sep 28 '10 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.