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.


Popular posts from this blog

Duplicate Folders Freeware - Not Duplicate Files

SSRS Compile Errors

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly