copy file

Question

Post reply

copy file

VastSQL

SSC-Dedicated

Points: 36246
More actions
February 1, 2008 at 5:05 am

#120236

Hi All
Please help to solve this issue.
I want to copy backup file from one machine to another,i want to achieve this by using sql query i tried xp_cmdshell copy command but am getting access denied.I created a windows login in both machines and same sql login in the server.
Thanks in Advance

Viewing 6 posts - 1 through 6 (of 6 total)

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

Johan Bijnens SSC Guru Points: 135521 More actions · Answer 1

if you want to use xp_cmdshell, you'll have to open it at sqlserver instance level ! (which I do not advise !)

We use a SQLAgent job to perform the backups.

The last step in these jobs is an xcopy to the safe zone.

e.g.

execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID ,

@step_id = 9, @cmdexec_success_code = 0, @on_success_action = 1,

@on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0,

@retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 0,

@step_name = N'CopyToBackupSrv', @subsystem = N'CmdExec',

@command = N'XCOPY C:\MSSQL\BACKUP\*.* \\mysafezoneserver\bu2tapeDay$\sqlserverisntancename\Database\ /c /k /h /v /y'

The SQLAgent service account has been given auth to write to this directory and

it has the authority to use the cmdexe subsystem. (jobowner = 'sa')

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

VastSQL SSC-Dedicated Points: 36246 More actions · Answer 2

The machine to which i have to take backup is not in the same domain nor it have any domain.When i connect to that machine from the RUN am able to access that.