robocopy utility 1

[masds]

I followed the steps denny wrote in the FAQ, add a robocopy job in SQL Server, when I manually start the job it works fine, but when schedule it, it doesn't work, can anybody give me clue? The command in the batch file:

robo.bat:

dir /B /S /A-D e:\san\temp > dellist.txt
for /f "tokens=1" %%a in (dellist.txt) do del "%%a" /F /Q
robocopy d:\san\backups e:\san\temp /MOV /MINAGE:2

then I put a line in the job:

cmd /c robo

 

[JayKusch]

you need to use xp_cmdshell

exec xp_cmdshell 'c:\cmd /c robo'

Thanks

J. Kusch

 

[mrdenny]

Make sure that the job is scheduled to run under an account with Sys Admin rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[masds]

xp_cmdshell 'cmd /c robo' then choose T-SQL won't work eithor, I checked the logins and the windows login user that running the SQLAgent has sysadmin permission. Click 'start job' works and I guess the job is running under this windows user. I have no clue why it's not working here!

 

[JayKusch]

don't use CMD ...

exec xp_cmdshell 'c:\BatchDir\robo /c'

BatchDir will be the folder path to where robo.bat resides.

Thanks

J. Kusch

 

[masds]

It still doesn't work, works when click start job in EM.

 

[masds]

In the job, I created 2 steps:

step1: Remove all the files in the temp directory
and then move all the files oder than 2 days
from \backups to \temp.

step2: Backup database to the backup directory

If I separate the job into 2, each of them will works fine when schedule it. The problem is that I need to bind these two jobs together because the second job depends on the first one, can anybody give me a solution here? thanks

 

[mrdenny]

Have the first job start the second job via sp_start_job.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DrSql]

When you use robocopy make sure you should be able to run the script externally.
then

declare @command char(255)
set @command='C:\Dir\robocoy.bat'
exec xp_cmdshell @command


your bat file should look like this

ROBOCOPY \\from_server\share\ \\to_server\share /MIR /COPYAT /SEC
exit

Good luck


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[masds]

I tried following command in QA:

master..xp_cmdshell 'cmd /c robo'

this just copy a file to another folder, but it runs forever, so I guess it just stuck there and won't return any error, I want kill the spid in EM, but I can't, it's still there after I delete it

 

[mrdenny]

You have to refresh the Current Activities window to get it to show you that the spid is dead. Use the sp_who2 command in Query Analyzer to find out if the spid is actually dead.

You shouldn't need to call cmd from within cmdshell. Change the SQL Statement to this.

exec master.dbo.xp_cmdshell 'C:\Dir\robocoy.bat'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[masds]

I finally found the problem, when I used robocopy with a parameter: /MINAGE, the idea is robocopy move the backups files that are 2 days old to another drive, so I wrote code like:

robocopy backup_path\ temp_path\ /MINAGE:2

and run the above line everytime before backing up the database, this fails because the parameter 2 means 2 * 24 hours, so the robocopy can't catch the bak file taken 2 days ago because the interval is :

2 * 24 hours - times spend on move files - backup time

So it doesn't work as expected. I change the code to:

robocopy backup_path\ temp_path\ /MINAGE:1

then it works fine, hope this helps someone in using robocopy!