Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: Sql Backup Device using UNC Path

    I'm trying to set a backup job to back up my databases to a server other than the server where Sql server is installed. The only options I can see are the local drives on my sql server when I try to set a location or backup device. If I want my backups to go to another server , How do I do that. Currently I'm trying to use a UNC path like \\ServerName\Backups. When I try to use a UNC path I get an invalid directory error.

    I'm fairly new to SQL Server as you can tell!

    Can anyone help me? (I'm using sql server 2000)
    Thanks
    GEM

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    You really should not backup to a remote location. if the network goes down during the backup, you have no backup, and then you could end up screwed.

    better is to backup to a local drive, and only then copy the .bak to share.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Sorry jez ... lots of places backup to file share these days ... large organizations build shares housed on SAN for holding backups from several instances / servers, then sweep the share to tape.

    gem ... same syntax as a regular backup just use the share instead of the drive letter ... i.e.

    Code:
     
    BACKUP DATABASE foobar to DISK = '\\FileShare\folder\dbname.bak' with <options>

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, but it should be understood that if the network goes down while the backup is being created (not uncommon) then your backup will be hosed, no?

    to me it's safer to backup to a local drive then copy the backup to the share.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I agree that that the local backup is the safest way ... but internal disk drive size is limited ... and VLDB don't always fit on local drives ... so we write to SAN storage then sweep to tape ... making the best of a bad situation 'cause sometimes you have to keep the data forever (!@#$ SOX)!!!


    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    Thanks for both of you for reply. I'll try the UNC path again, that the way my network administrator wants it

    I have another question I will put in another post. If either of you can help there I would appreciate it.

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by jezemine
    ok, but it should be understood that if the network goes down while the backup is being created (not uncommon) then your backup will be hosed, no?
    Of course. Unless you have a fault tolerant network. Two NICs, connected to different switches, and teaming for fault tolerance and performance on top
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    the backup fails due to insufficient permissions. if u follow the errorlog file of this u will see a "access denied" reported. the sql server service starting account to be changed to a domain account. more here
    http://support.microsoft.com/kb/207187

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •