Transfer Backup

Question

Transfer Backup

Viewing 12 posts - 16 through 26 (of 26 total)

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

Animal Magic SSChampion Points: 13964 More actions · Answer 1

as mentioned earlier it will either be permissions or your UNC path is wrong. try typing your path into a start - run command and see if the folder opens your expecting

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

Nero-1119276

Say Hey Kid

Points: 687

October 28, 2009 at 4:57 am

#1071630

Yes folder is opening as expected

Animal Magic SSChampion Points: 13964 More actions · Answer 3

ok, its not permissions then.

paste your copy syntax here that your using

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

declare @logid as int

declare @backup_file as varchar(200)

declare @neerav as varchar(200)

--Select media_set_id of last backup

set @logid=(select max(media_set_id) from

msdb..backupmediafamily

where physical_device_name like

'D:\Testdatabase%')

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

set @backup_file=(select physical_device_name from msdb..backupmediafamily where media_set_id=@logid)

set @neerav = 'copy'+' '+@backup_file+' '+'Z:'

exec xp_cmdshell @neerav

where z: is map network drive

Animal Magic SSChampion Points: 13964 More actions · Answer 5

try using the proper UNC path and not a mapped drive

Animal Magic SSChampion Points: 13964 More actions · Answer 6

Nero-1119276 (10/28/2009)
declare @logid as int
declare @backup_file as varchar(200)
declare @neerav as varchar(200)
--Select media_set_id of last backup
set @logid=(select max(media_set_id) from
msdb..backupmediafamily
where physical_device_name like
'D:\Testdatabase%')
--Select exact backup device name if you create backup with maintenance plan and have different name for every backup
set @backup_file=(select physical_device_name from msdb..backupmediafamily where media_set_id=@logid)
set @neerav = 'copy'+' '+@backup_file+' '+'Z:'
exec xp_cmdshell @neerav
where z: is map network drive

take the colon off your Z mapping.

set @neerav = 'copy'+' '+@backup_file+' '+'Z'

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

Now access is denied but when i try with command prompt allows me to copy

Animal Magic SSChampion Points: 13964 More actions · Answer 8

access is denied to cmdshell or the drive location? are you still using the mapped drive or the UNC path?

ps, note that xp_cmdshell will run under the sql server service account permissions so you have to

a) make sure that user has access to the remote location as well

b) if your still using the Z mapping you have to make sure the sql server service account sees the Z drive when you logon to the server as them

you can set proxy accounts but lets stay away from that for now

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

when you copy file you are accessing remote location with credentials you are logged in with and when you use cmdshell you are accessing remote location with account that is starting sql server agent so this account need to have permissions on remote location

Animal Magic SSChampion Points: 13964 More actions · Answer 10

irena.bulatovic (10/28/2009)
when you copy file you are accessing remote location with credentials you are logged in with and when you use cmdshell you are accessing remote location with account that is starting sql server agent so this account need to have permissions on remote location

its not the agent, its the sql server service.

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

Animal Magic (10/28/2009)
irena.bulatovic (10/28/2009)
when you copy file you are accessing remote location with credentials you are logged in with and when you use cmdshell you are accessing remote location with account that is starting sql server agent so this account need to have permissions on remote location
its not the agent, its the sql server service.

Are you sure. I have sql 2000 and i don't know for 2005,2008 but i have different accounts for sql server and sql server agent and agent is running jobs so that account should have rights

Animal Magic SSChampion Points: 13964 More actions · Answer 12

irena.bulatovic (10/28/2009)
Animal Magic (10/28/2009)
irena.bulatovic (10/28/2009)
when you copy file you are accessing remote location with credentials you are logged in with and when you use cmdshell you are accessing remote location with account that is starting sql server agent so this account need to have permissions on remote location
its not the agent, its the sql server service.
Are you sure. I have sql 2000 and i don't know for 2005,2008 but i have different accounts for sql server and sql server agent and agent is running jobs so that account should have rights

ah ok, if its from within a sql job then yes it would use the agent service account. if you just run it in SSMS/EM then it will use the sql service account