October 5, 2007 at 10:37 am
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
October 5, 2007 at 10:42 am
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.
October 5, 2007 at 11:00 am
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.
October 5, 2007 at 2:08 pm
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.
October 5, 2007 at 2:21 pm
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...
October 5, 2007 at 11:54 pm
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?
October 6, 2007 at 11:17 am
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.
October 7, 2007 at 9:46 am
Jo Pattyn (10/6/2007)
BACKUP DATABASE testdbTO 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.
October 7, 2007 at 8:54 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply