Copy files from one server to other using Tsql

Question

Post reply

Copy files from one server to other using Tsql

Sql Student-446896

Default port

Points: 1425
More actions
November 8, 2007 at 7:38 am

#119375

Hi,
I want to zip and copy a folder from a server location (//Server1\C:/Interface/Output) and put it another server folder and also delete the copied folder from the source location (Server2ew folder)
Is there a Stored Procedure that i can write to do this work and i can schedule the job every week?
Thanks,
Rama

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

As long as you don't mind using xp_cmdshell, that can easily be done.

The Redneck DBA

Sql Student-446896 Default port Points: 1425 More actions · Answer 2

Could you help me using the xp_cmdshell command? I have learnt about it, but not well verse in writing a procedure to perform the operation.

Thanks,

rama

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 3

Sure. It's really easy.

I just fill up a variable, say @CMD with the same command text I would use if I were executing from the command prompt, and just execute it.

For example if you want to copy a file from one place to another you could do something like this

DECLARE @CMD VARCHAR(512)

DECLARE @SOURCE_PATH VARCHAR(512)

DECLARE @DESTINATION_PATH VARCHAR(512)

SET @SOURCE_PATH = 'C:\SOURCE\filename.txt'

SET @DESTINATION_PATH = 'C:\DESTINATIONewfilename.txt'

SET @CMD = 'COPY /Y ' + @SOURCE_PATH + ' ' + @DESTINATION_PATH

EXECUTE master.dbo.xp_cmdshell @CMD

-----

Then if you want to delete it you could do something like

SET @CMD = 'DEL ' + @SOURCE_PATH

EXECUTE master.dbo.xp_cmdshell @CMD

Or I suppose you could just use MOVE instead.

As for zipping, it'll be a little different depending on what zip program you are using, but it's just a matter of sticking the command into that @CMD variable and running with it.

The Redneck DBA

Sql Student-446896 Default port Points: 1425 More actions · Answer 4

Oh..this is quite a simple set of code.

I sure learnt something new 🙂

I will work on the code you sent and get the idea of it.

Thanks a bunch !!

Rama

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 5

DECLARE @Cmd AS VARCHAR (50)

DECLARE @Result AS INTEGER

SET @Cmd = 'C:\DIR /P' --build appropriate command line

EXECUTE @Result = Master.dbo.xp_cmdshell @Cmd

IF (@Result = 0) BEGIN

-- Do something

END ELSE BEGIN

-- Do something else

If using SQL 2005, you could wrap the EXECUTE statement in a BEGIN TRY..BEGIN CATCH block.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

You mentioned running this as a job every week. If that's the case, you're better off doing this with command-line options/commands such as xcopy... You typically don't want to use xp_cmdshell unless you have to. There's no reason to put the load for copying/zipping files on the SQL Server process.

K. Brian Kelley
@kbriankelley