Need Help in xp_cmdshell - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Need Help in xp_cmdshell

Author

Topic

senthildba
Starting Member

7 Posts

Posted - 2004-05-13 : 03:31:00

I am trying to backup my database files to remote system .My SP is as follows

ALTER PROCEDURE TEST_ALL_SCRIPT
AS
SET QUOTED_IDENTIFIER OFF
declare @dpart varchar(50)
declare @year varchar(4)
declare @mon varchar(3)
declare @dd varchar(4)
declare @vSQLSTR varchar(1000)
declare @dbname varchar(100)
declare @dst_path varchar(100)
declare @str varchar(500)

select @year = datepart(year,getdate())
select @mon = datename(month,getdate())
select @dd = convert(varchar,datepart(dd,getdate()))
select @dpart = @mon+@year+@dd
set @dst_path = 'F:\TESTBACKUP\'
set @dbname = db_name()

set @vSQLSTR = 'Backup Database ' + @dbname + ' To disk = ' + '''' + @dst_path+@dbname+ '_' + @dpart + '.bak' + ''''
exec (@vSQLSTR)

set @str = ' xcopy ' + @dst_path + 'Test_' + @dpart + '.Bak' + ' G:\test\ ' + '/C /Y'
print @str

exec master..xp_cmdshell @str
GO

and when i excute the SP it gives an error stating INVALID DRIVE SPECIFICATION .The G: drive is mapped to my system
Can any body help in this

Advance Thanks
Senthil

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-05-13 : 05:52:05

Is the server running on your system as well - i.e. is the g drive available to the server? Try using the UNC path instead.

Raymond

senthildba
Starting Member

7 Posts

Posted - 2004-05-13 : 06:58:21

Hi
I also tried using UNC path but the result is ACCESS DENIED is error

Regards
Senthil

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-05-13 : 07:01:15

In that case it is probably tha the user running either the SQL Server startup or SQL Agent (if you are running the procedure as a job) that does not have permissions to that share. Assign permissions for the share to the SQL user accounts and try again.

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources