Copying files between servers

I'm trying to copy files between 2 servers on a local network from within a
SQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an access
denied message returned.

I'm able to successfully do the copy from within a command window so think
the problem has something to do with using the default SQL Server account
but as yet I don't know how to resolve.

Any help/suggestions would be much appreciated.Am guessng that you are running MS-SQL using the local SYSTEM account.
System does not have access to network devices.

Your two options are to create another account and configure MS-SQL and
agent to use that account. You may beable to get away with just
configuring agent for that but depends on how you are doing the
command.

Or to go into policy editor and allowing the system account to have
netowrk priviledges. This is a major security hole and should not be
done.

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I n
eed to copy this backup set over to a dev box for refresh every night. So af
ter the backup is done, i have another job which run to copy this backup set
. And the job fails !! the
weird part is that i can run it from query analyser ..works fine... if i run
that job a few times it will start copying(in one of the tries)...I am real
ly lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername
\sharename\refreshstage\LSPRD_DB_COPY.DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. th
e Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYouIt would help if you gave some clue as to what the error messages were.
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||The Job returns as Successfull. But the Copy is not in the traget location.
Infact it Delete's the last backup (copied) from the target location. But It
does not copy the fresh backup.
"Andrew J. Kelly" wrote:

> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> I need to copy this backup set over to a dev box for refresh every night.
So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> \\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> the Other one fails is 15 gb.. Disk space in not the issue..
>
>|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
night.[vbcol=seagreen]
night. So[vbcol=seagreen]
backup[vbcol=seagreen]
night..[vbcol=seagreen]

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I need to copy this backup set over to a dev box for refresh every night. So after the backup is done, i have another job which run to copy this backup set. And the job fails !! the weird part is that i can run it from query analyser ..works fine... if i run that job a few times it will start copying(in one of the tries)...I am really lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. the Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYouIt would help if you gave some clue as to what the error messages were.
--
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||The Job returns as Successfull. But the Copy is not in the traget location. Infact it Delete's the last backup (copied) from the target location. But It does not copy the fresh backup.
"Andrew J. Kelly" wrote:
> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> > Folks,
> > This is a strange one. I have one full backup which is done every night.
> I need to copy this backup set over to a dev box for refresh every night. So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> >
> > The command which run the copy is run under sa... the command is
> >
> > exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
> \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> > GO
> >
> > Xcopy and copy results are the same... AND
> >
> > I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
> the Other one fails is 15 gb.. Disk space in not the issue..
> >
> > Any help would be great -- ThankYou
>
>|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.
> "Andrew J. Kelly" wrote:
> > It would help if you gave some clue as to what the error messages were.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Girish" <Girish@.discussions.microsoft.com> wrote in message
> > news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> > > Folks,
> > > This is a strange one. I have one full backup which is done every
night.
> > I need to copy this backup set over to a dev box for refresh every
night. So
> > after the backup is done, i have another job which run to copy this
backup
> > set. And the job fails !! the weird part is that i can run it from query
> > analyser ..works fine... if i run that job a few times it will start
> > copying(in one of the tries)...I am really lost here .. Please help.
> > >
> > > The command which run the copy is run under sa... the command is
> > >
> > > exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
> > \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> > > GO
> > >
> > > Xcopy and copy results are the same... AND
> > >
> > > I am copying 2 backup sets... ONE works just fine is 5 GB.. every
night..
> > the Other one fails is 15 gb.. Disk space in not the issue..
> > >
> > > Any help would be great -- ThankYou
> >
> >
> >

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I need to copy this backup set over to a dev box for refresh every night. So after the backup is done, i have another job which run to copy this backup set. And the job fails !! the
weird part is that i can run it from query analyser ..works fine... if i run that job a few times it will start copying(in one of the tries)...I am really lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. the Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYou
It would help if you gave some clue as to what the error messages were.
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou
|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou
|||The Job returns as Successfull. But the Copy is not in the traget location. Infact it Delete's the last backup (copied) from the target location. But It does not copy the fresh backup.
"Andrew J. Kelly" wrote:

> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> I need to copy this backup set over to a dev box for refresh every night. So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> \\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> the Other one fails is 15 gb.. Disk space in not the issue..
>
>
|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
night.[vbcol=seagreen]
night. So[vbcol=seagreen]
backup[vbcol=seagreen]
night..[vbcol=seagreen]