Query giving error path not found

Hi,
Can any 1 tell why this query is giving error when it is coming to
last to database i.e test and model. It is giving error as path could
not be found. For 1st 2 database also path is same but that time it is
working fine. It will be a great help if u will run and see what is
the error.
ALTER proc [dbo].[backup_proc5] @.para2 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.para1 varchar(100)
declare @.str1 varchar(200),@.sql varchar(8000)
select @.str='Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=substring(@.str,0,charindex(',',@.str))
if (@.var1='')
begin
break
end
select @.para1=@.var1
SET @.sql= 'mkdir "'+@.para2+'\'+@.para1+'" '
exec master.dbo.xp_cmdshell @.sql
declare @.datetime nvarchar(20)
SELECT @.datetime = @.para1+'_db_'+CONVERT(VARCHAR, GETDATE(),111)
set @.str1='backup database '+ @.para1 +' to disk ='''+ @.para2 + '\' +
@.para1 + '\' + @.datetime +'.bak'''
exec(@.str1)
print @.str1
select @.str=right(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
select @.para1=@.str
SELECT @.datetime = @.para1+'_db_'+CONVERT(VARCHAR, GETDATE(),111)
set @.str1='backup database '+ @.para1 +' to disk ='''+ @.para2 + '\' +
@.para1 + '\' + @.datetime +'.bak'''
exec(@.str1)
You are using relative paths and assuming that xp_cmdshell and BACKUP use
the same default directory. On my server, xp_cmdshell uses the
C:\Windows\System32 directory by default but this may be different in your
environment. BACKUP will use the SQL Server default backup folder.
I suggest you fully-qualify the backup path to avoid this issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:b9dc3c61-ea2e-4607-b165-e0dd18e0f243@.s12g2000prg.googlegroups.com...
> Hi,
> Can any 1 tell why this query is giving error when it is coming to
> last to database i.e test and model. It is giving error as path could
> not be found. For 1st 2 database also path is same but that time it is
> working fine. It will be a great help if u will run and see what is
> the error.
> ALTER proc [dbo].[backup_proc5] @.para2 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.para1 varchar(100)
> declare @.str1 varchar(200),@.sql varchar(8000)
> select @.str='Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=substring(@.str,0,charindex(',',@.str))
> if (@.var1='')
> begin
> break
> end
> select @.para1=@.var1
> SET @.sql= 'mkdir "'+@.para2+'\'+@.para1+'" '
> exec master.dbo.xp_cmdshell @.sql
> declare @.datetime nvarchar(20)
> SELECT @.datetime = @.para1+'_db_'+CONVERT(VARCHAR, GETDATE(),111)
> set @.str1='backup database '+ @.para1 +' to disk ='''+ @.para2 + '\' +
> @.para1 + '\' + @.datetime +'.bak'''
> exec(@.str1)
> print @.str1
> select @.str=right(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> select @.para1=@.str
> SELECT @.datetime = @.para1+'_db_'+CONVERT(VARCHAR, GETDATE(),111)
> set @.str1='backup database '+ @.para1 +' to disk ='''+ @.para2 + '\' +
> @.para1 + '\' + @.datetime +'.bak'''
> exec(@.str1)