I have a job that takes database backup files and compresses them to .7z files using seven zips command line utility and at the moment it takes about 8 hours to run through all the .bak files because it is doing one at a time. This is running on has about 16 cores and the 7z process only seems to be using 1 core so I would like to be able to run multiply instances of the xp_cmdshell command to have it compress several files at a time. Is there any way to execute a list of commands in T SQL on MSSQL Server 2005?
I have post my script below.
This is a link to the program I am using to zip the files. [http://downloads.sourceforge.net/sevenzip/7za920.zip][1]
-- YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
-- II zip all files in a folder II
-- VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
-- Zip and then Delete all files from the backup folder
-- List all files in a directory - T-SQL parse string for date and filename
DECLARE @PathName VARCHAR(256) ,
@CMD VARCHAR(512)
CREATE TABLE #CommandShell ( Line VARCHAR(512))
-- To use the xp_cmdshell option it has to be enabled. you can use the script bellow to enable it.
--
-- -- run exec sp_configure to see if the option exists in this list and you can check if it is enabled.
-- EXEC sp_configure
-- go
-- -- if you don't see xp_cmdshell in the list then you will have to enable advanced options first
-- -- before you can enable the xp_cmdshell option.
-- EXEC sp_configure 'show advanced options', 1;
-- go
-- reconfigure
-- go
-- -- if xp_cmdshell is in the list then you should just need to run this script.
-- exec sp_configure 'xp_cmdshell', 1
-- go
-- reconfigure
SET @PathName = 'D:\FILES\Backups\'
SET @CMD = 'DIR ' + @PathName + ' /TC'
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD
-- Delete lines not containing filename
DELETE
FROM #CommandShell
WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null
-- SQL reverse string function - charindex string function
SELECT ROW_NUMBER() OVER (ORDER BY REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) )) AS ROW_NUM,
FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
CreateDate = LEFT(Line,10)
INTO #tempFileList
FROM #CommandShell
WHERE REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ) LIKE '%.bak'
ORDER BY FileName
DROP TABLE #CommandShell
DECLARE @FileMaxRownum INT
SET @FileMaxRownum = (SELECT MAX(ROW_NUM) FROM #tempFileList)
DECLARE @FileIter INT
SET @FileIter = (SELECT MIN(ROW_NUM) FROM #tempFileList)
WHILE @FileIter <= @FileMaxRownum
BEGIN
DECLARE @DelFile varchar(200)
--@@
DECLARE @cmd2 VARCHAR(1000)
SET @cmd2 = null
DECLARE @db_bkp_files_dir varchar(100)
SET @db_bkp_files_dir = null
DECLARE @archive_destination_dir varchar(100)
SET @archive_destination_dir = null
DECLARE @archive_name varchar(100)
SET @archive_name = null
DECLARE @7z_path varchar(100)
SET @7z_path = null
set @archive_destination_dir = @PathName --destination dir
set @7z_path = 'D:\FILES'
set @db_bkp_files_dir = right(@PathName,1) --db backup files origin
SELECT TOP(1) @archive_name = FileName FROM #tempFileList WHERE ROW_NUM = @FileIter
SET @cmd2 = @7z_path + '\7za a -t7z -mx5 -ms=off ' + @archive_destination_dir + @archive_name + '.7z ' + @archive_destination_dir + @archive_name
print @cmd2
EXEC xp_cmdshell @cmd2
--@@
SELECT TOP(1) @DelFile = 'del ' + @PathName + FileName FROM #tempFileList WHERE ROW_NUM = @FileIter
EXEC xp_cmdshell @DelFile
SET @FileIter = @FileIter + 1
END
DROP TABLE #tempFileList
I don't think the issue is with SQL Server. If you wanted to run multiple threads of execution at the same time, you would do so with scheduled jobs in SQL Server agent... but, this is just a workaround for the compression algorithm in 7-Zip that you're using.
Instead of using the -t7z switch, try using -tbzip2, which is the algorithm detailed in the blog post above.
SET @cmd2 = @7z_path + '\7za a -tbzip2 -mx5 -ms=off '
+ @archive_destination_dir
+ @archive_name + '.7z '
+ @archive_destination_dir
+ @archive_name