Compress Database backups on SQL Express Edition

As we know Microsoft does not support compression for SQL Server Express but time to time we use express edition database engine when all you need is small database that might not even grow more than 1 gig or 5 gigs.

Prep:

we need 7z zip program its an opensource software will help us compress the files, download the latest version 64 bit (http://www.7-zip.org/)

Enable xp_cmdShell ( we are going to use xp_cmdshell to execute the batch command to compress or delete files)

-- 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  

TSQL Script( Where Magic happens)

How does script work:  it first looks at mypath and lists all .bak files and folders it loops each of these files using a cursor and execute the 7zip command to compress the .bak to .zip and cleanuphrs parameter will clean the backup zip files older than specified number of hours.

/*
##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)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s