Backup to a different machine

Question

Post reply

Backup to a different machine

pinky

SSCommitted

Points: 1997
More actions
October 5, 2007 at 10:37 am

#125873

Hi,
I would like to schedule a backup job to craete to the backup file onto the different machine in sql server 2000. is it possible?
I mean, schedule the backup job in Server A and the backup file needs to be created in Server B not in Server A. The jobs should automatically create the backup file on Server B instead of creating in Server A.
Can anyone please let me know if this is possible? if so how do i do that.
Thanks for your help

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

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

sathyareddy Ten Centuries Points: 1158 More actions · Answer 1

Yes it is Possible.

You need to have a shared network drive on serverB. use this drive to store your backup.

Make sure the drive has all access permissions to both servers. especially serverA.

Also make sure you have enough space to store your backup.

Regards,

Sathya.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Do you need to backup to server B due to space constraints and such, or are you just looking to get the backup to the second server for redundancy?

The reason why I ask, is that I always backup tot he local machine first, then copy the backup file to the second server. This gets me three things over just backing up straight to the 2nd server, a) data redundancy because the backup is stored in 2 locations, and b) faster recovery time, when I need to restore I don't have to wait for the most recent backup to be copied across the network, c) faster backups because I'm not waiting on a slow network connection I'm just dumping out the data as fast as I can to the local machine.

I'd suggest backup to the local machine, then using a .cmd file copy the file to the other server. This can all be scripted and run from a SQL Agent Job, but the SQL Agent needs to have the appropriate access to map a network drive to the second machine and needs write access to that drive as well.

To help us help you read this[/url]For better help with performance problems please read this[/url]

pinky SSCommitted Points: 1997 More actions · Answer 3

You need to have a shared network drive on serverB. use this drive to store your backup.

-- how do i create a shared network drive on server B (sorry for asking). Please let me know

I have craeted a shared folder on server B and gave all permissions to server A to access that folder.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

Yes a shared folder is what we were talking about. you create the share on server B and make sure that the SQL Agent Has Write Permissions to the Folder via both NTFS and Share permissions.

Again, you need to backup to the local machine first and then copy the backup over the network to server b. This can be done with a simple dos .cmd or .bat file.

REM this maps the drive, DOS doesn't like URLS Remember...

net use z: \\serverb\sharename /persistent:NO

REM Now copy the file to your location

copy c:\myBackup.bak z:

REM remove the network drive... Always cleanup after yourself

net use z: /DELETE

Step 1 of your Job, run the backup

Step 2 run the Dos Script

You're done.

If you want to get fancy, you could check for the existence of the new backup file on server b with a 3rd step and report an error if it doesn't get there for some reason...

To help us help you read this[/url]For better help with performance problems please read this[/url]

pinky SSCommitted Points: 1997 More actions · Answer 5

Thanks for the reply. Here is what is my situation.

we dont have enough space on server A so i would like to point the backup job to create the backup file on server B directly. is it possible?

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

BACKUP DATABASE testdb

TO DISK='\\ServerB\Backup\ServerA\testdb.bak'

*Make sure the executor (sql agent) has enough permissions on the disk

*Try to restore at least one backup. We had issues with windows 2003 without servicepack & backing up large databases. Applying the servicepack(s) and some memory fixed the issue.

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Jo Pattyn (10/6/2007)
BACKUP DATABASE testdb
TO DISK='\\ServerB\Backup\ServerA\testdb.bak'
*Make sure the executor (sql agent) has enough permissions on the disk
*Try to restore at least one backup. We had issues with windows 2003 without servicepack & backing up large databases. Applying the servicepack(s) and some memory fixed the issue.

This is what I do too. No need for a share, just put the UNC path in your backup. You may not be able to select the unc if you create the backup job through the GUI, but you can go in and edit it afterwards with the path.

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

Be careful with this and monitor it carefully. The backup process doesn't tolerate any delays well and will fail if the network hiccups. You'd really be better off buying more drives for Server A.