Saturday, March 10, 2007

RoboCopy to manage files SQL server

Sometimes you would have to manage files using t-sql jobs/packages in sql server, then xp_cmdshell with robocopy can serve the purpose.

I like xp_cmdshell a lot as it can be used to execute various dos Command Line Commands.
Grant Permissions carefully to the users who can run xp_cmdshell as it has the power to get the system details,network details and much more.

Robocopy, or "Robust File Copy" is a tool from Microsoft and is available for licensed windows users in the resource kit(also included in windows vista by default).

One very nice cool feature of this tool is its ability to copy the permissions on the folder(/copyall).

For more info on the parameters of robocopy

Lets jump into the sql ..

DECLARE @SrcFolder varchar(300)
DECLARE @DesFolder varchar(300)
DECLARE @Cmd varchar(1000)

SELECT @SrcFolder = 'C:\tst'
SELECT @DesFolder = 'C:\tst1'
SELECT @Cmd = 'robocopy ' + @SrcFolder +' ' +'"'+ @DesFolder +'"'+ ' ' + '/copyall /b /xx /mir /nc /ns /ndl /nfl /ts /fp'

EXEC master.dbo.xp_cmdshell @Cmd

To run the above xp_cmdshell command , domain account with proper( read /write )permissions on the source and destination folders is required.

No comments:

Post a Comment

IIS Manager Crashes on Load/Start Windows 7, IIS 7.5

IIS Manager Crashes on Start  Windows 7, IIS 7.5, Power Shell 5.1. Here is the error I got in the event viewer. IISMANAGER_CRASH IIS Ma...