The following stored procedure will take a supplied file path, create an archive folder within the supplied folder path, and move everything in to that folder to basically clear out the base folder. The Archive folder gets tagged with the date and time of the operation. The stored procedure uses code for the Dir procedure I wrote earlier. In that link there is DDL for the table that is referenced in this sproc. Thanks for reading.
CREATE PROC Archive(@path varchar(2000))
AS
SET NOCOUNT ON
BEGIN
DECLARE @cmd varchar(4000), @today varchar(25), @archive varchar(2000)
EXEC Dir @path, 1
IF NOT EXISTS(SELECT * FROM Directory_Contents WHERE Dir = @path)
BEGIN
PRINT 'No directory was found for ' + @path
GOTO Archive_Error
END
IF NOT EXISTS(SELECT * FROM Directory_Contents WHERE Dir = @path AND Struct_Type = 'FILE')
BEGIN
PRINT 'No files in directory ' + @path + ' to Archive'
GOTO Archive_Error
END
SELECT @today = REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),' ','_'),':','_'),'-','_')
SELECT @archive = + SUBSTRING(@path,1,1+LEN(@path)-CHARINDEX('\',REVERSE(@path))) + 'Archive' + '_' + @today
SELECT @cmd = 'md "'+ @archive + '"'
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'move "' + @path + '" "' + @archive + '\"'
EXEC master..xp_cmdshell @cmd
Archive_Exit:
SET NOCOUNT OFF
RETURN
Archive_Error:
-- Add any additional Error Handling
GOTO Archive_Exit
END
GO