Backup SQL 2000 to network drive

Question

Backup SQL 2000 to network drive

Junior_DBA

SSCrazy

Points: 2002
More actions
August 29, 2008 at 2:22 pm

#125151

Hi Guys,
Please can anybody give me step by step procedure, I am really new with SQL..and I am getting confused how to backup to network.
Please?
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

What I've had to do is create the backup on a local drive, then copy it to the network as a separate action.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

tosscrosby SSCoach Points: 18045 More actions · Answer 2

While it's not recommended, you can backup to a UNC path. Be sure the account running the SQL Agent has rights to the share. GSquared's approach is the preferred method of doing your SQL backups and pushing them to a network share. Less risk of corrupt backups.

-- You can't be late until you show up.

Junior_DBA SSCrazy Points: 2002 More actions · Answer 3

Yes, it sounds good, I would try that :), however sometimes I am told to do it in a remote basis, and I don't know how to use UNC...I am using domain account, but still without seeing that remote server.

Would you mind giving me some idea how can I do it, using EM, please?

Thank you.

tosscrosby SSCoach Points: 18045 More actions · Answer 4

Use \\computername\sharename in your backup file location. If you don't know the serve or share name, you're basically out of luck. You cannot map a remote drive as SQL will not see it.

-- You can't be late until you show up.

Junior_DBA SSCrazy Points: 2002 More actions · Answer 5

it gave me an error: DMO(ODBC SQLState:42000)

cannot open backup device '\\server\file'

device error or off-line

Any idea, please?

Thank you.

Atif Hall of Fame Points: 3578 More actions · Answer 6

As terry said: Be sure the account running the SQL Agent has rights to the share.

DBDigger Microsoft Data Platform Consultancy.

Animal Magic SSChampion Points: 13964 More actions · Answer 7

The way i do it is backup to a local drive and then use an application called SyncToy (http://www.microsoft.com/downloads/details.aspx?FamilyId=E0FC1154-C975-4814-9649-CCE41AF06EB7&displaylang=en)

to move the backups to another server.

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 8

The reason that everyone recommends backing up locally is that the backup process is similar to UDP on the network. It is optimized for speed and sends pages out as quickly as possible. If there is a network hiccup, and they happen all the time, the backup files. File copies across the network do a few retries to get the work done, but the SQL Backup will not.

Back up locally. If someone asks why, tell them that.

Run the backup locally, then use SynchToy or a script to move it to the remote location. You can even delete the local backup after the copy is complete if you need the space.