Transfer Backup

Question

Transfer Backup

Nero-1119276

Say Hey Kid

Points: 687
More actions
October 26, 2009 at 5:03 am

#385647

Hi all,
I have set up job that will take backup after 3hr.
Now i need to transfer that backup to another CPU by automated process.
Is there any method or process by which i can do this?
Please help!!!!
Thanks,
Neerav

Viewing 15 posts - 1 through 15 (of 26 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Another CPU? I'm not sure I understand what you're asking. More details please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

run CMD command like "COPY <from location> <to location>"

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

I guess another CPU menas another computer 😉

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Nero-1119276 Say Hey Kid Points: 687 More actions · Answer 4

Dear Gilamonster,

Another CPU means another system.

We are preparing one system to hold all sql server backup

but it will be done automated,as when i use xcopy to destination from source it is giving me invalid drive specification.

Is there any method in sql server by which we can transfer our backup?

Thanks,

Neerav

Animal Magic SSChampion Points: 13964 More actions · Answer 5

We use a program called synctoy to do this. its free and works like a dream. i would suggest though that if xcopy isnt working you either dont have access rights to the drive your copying to or your UNC path is incorrect

http://www.microsoft.com/Downloads/details.aspx?familyid=C26EFA36-98E0-4EE9-A7C5-98D0592D8C52&displaylang=en

Nero-1119276 Say Hey Kid Points: 687 More actions · Answer 6

sorry for providing incomplete information.My another system is in differenent geograhical location

Animal Magic SSChampion Points: 13964 More actions · Answer 7

Nero-1119276 (10/26/2009)
sorry for providing incomplete information.My another system is in differenent geograhical location

What issues are you worried about? backup size/network link speed? No link between the two? Insufficient access rights?

irena.bulatovic Mr or Mrs. 500 Points: 513 More actions · Answer 8

declare @logid as int

declare @backup_file as varchar(200)

declare @komanda as varchar(200)

--Select media_set_id of last backup

set @logid=(select max(media_set_id) from

backupmediafamily

where physical_device_name like

'Your backup device name%')

--Select exact backup device name if you create backup with maintenance plan and have different name for every backup

set @backupfile=(select physical_device_name from backupmediafamily where media_set_id=@logid)

set @komanda = 'copy'+' '+@backup_file+' Other location'

exec xp_cmdshell @komanda

create job step like this to copy your backup file to other location

mcvilbar Say Hey Kid Points: 701 More actions · Answer 9

You can xp_cmdshell for that in order to copy to another server in SQL. 🙂

mcvilbar Say Hey Kid Points: 701 More actions · Answer 10

You can use xp_cmdshell for that in order to copy to another server in SQL. 🙂

Animal Magic SSChampion Points: 13964 More actions · Answer 11

mcvilbar (10/27/2009)
You can xp_cmdshell for that in order to copy to another server in SQL. 🙂

you can, however

a) its a security risk and most systems have it disabled

b) The OP has already mentioned that an xcopy from a command window doesnt work, therefore the xp_xmdshell will also fail (assuming its run under the same security context)

I think we just need to wait and see if it is a security issue or just a syntax issue with the copy command the OP is using

Alex.Fishkin Newbie Points: 2 More actions · Answer 12

Try this:

declare @cmd varchar(256)

declare @backupfilename varchar (256)

set @backupfilename = 'database_backup.bak' -- set to the name of your backup file

set @cmd = 'Copy /Y C:\Backups' -- set to where your backup is located

set @cmd = @cmd + @backupfilename + ' \\servername\directory\' -- code to copy to new location

print @cmd

exec xp_cmdshell @cmd -- execute shell command to copy file

Alex.Fishkin Newbie Points: 2 More actions · Answer 13

Just make sure the user name under which you perform this task, has permissions to copy and paste on both servers, or simply that this user name has local admin rights on both computers. Also, in SQL Server Surface Area Configuration tool, on database server, you have to enable xp_cmdshell in Surface Area Configuration for Features module.

Nero-1119276 Say Hey Kid Points: 687 More actions · Answer 14

hi all,

It is giving me invalid drive specification in 'other location'

set @komanda = 'copy'+' '+@backup_file+' Other location'

exec xp_cmdshell @komanda

when i run this in cmd prompt it gets copied

What might b wrong?

Thanks,

Neerav