-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

/*
##Script to Compress the backups in sql express using 7 zip 
##Author: Akhil 
##Date:09/19/2016
##Rev:1 initial Setup
##Rev:2 fix the date add to negitive 
##Rev:3 Remove xp_delete_file instead use batch command to remove old files.
*/

--parameter
declare @myPath varchar(4000) =  'R:\mssql\userdb';
declare @apath varchar(1000) = '';
declare @aextn varchar(10)= 'zip';
declare @7zexe varchar(1000)= 'C:\Program Files\7-Zip\7z.exe'
declare @cleanupdays int = 4;
declare @cleanupdate datetime;

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
	id int IDENTITY(1,1)
	,subdirectory nvarchar(512)
	--,filename 
	,depth int
	,isfile bit
	, ParentDirectory int
	,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
	VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
	EXEC master.sys.xp_dirtree @myPath,0,1;


UPDATE #DirectoryTree
	SET ParentDirectory = (
		SELECT MAX(Id) FROM #DirectoryTree
		WHERE Depth = d.Depth - 1 AND Id < d.Id	)
FROM #DirectoryTree d;

-- SEE all with full paths
declare @archivefiles cursor ;
declare @bfile sysname;
declare @afile sysname;
declare @conataner sysname;
declare @result int;
declare @cmd varchar(8000);

--Get all the files 

set @archivefiles = cursor for
WITH dirs AS (
	 SELECT
		 Id,subdirectory,depth,isfile,ParentDirectory,flag
		 , CAST (null AS NVARCHAR(MAX)) AS container
		 , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
		 FROM #DirectoryTree
		 WHERE ParentDirectory IS NULL 
	 UNION ALL
	 SELECT
		 d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
		 , dpath as container
		 , dpath +'\'+d.[subdirectory]  
	 FROM #DirectoryTree AS d
	 INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
SELECT dpath,case when @apath is null or len(@apath) = 0 then replace(dpath,'.bak','.'+@aextn) 
 else @apath+replace(subdirectory,'.bak','.'+@aextn)  end as afile
    FROM dirs 
-- Dir style ordering
where isfile = 1 and subdirectory like '%.bak'


print('Archive Process')

open @archivefiles	

fetch next from @archivefiles into @bfile,@afile
while @@FETCH_STATUS = 0
begin 
print (@bfile)
print (@afile)
set @cmd = '""'+@7zexe+'" a "'+@afile+'" "'+@bfile+'"'+' -sdel'+'"'
print('Archive Command'+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)
fetch next from @archivefiles into @bfile,@afile
end

print('Clean up')

set @cmd = '"forfiles -p "'+@myPath+'" -s -m *.'+@aextn+' /D -'+convert(varchar,@cleanupdays)+' /C "cmd /c del @path"'
print('Delete Command '+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)