-----SQLBackupAll7z.SQL

----- Version: 2.1 - 2011-04-08
----- Moded to use Wzzip command line
----- by JEL

----- Usage:  

-- to run a scheduled job from task manager...
-- use something like: sqlcmd -i C:\SqlJobs\backup.sql -o C:\Logs\output.txt 
-- or OSQL -E -i C:\store\DatabaseBackup\SQLBackupAll.sql -o C:\store\DatabaseBackup\SQLBackupAll.log 
-- Don't forget to mention the instance name via the -S ".\SomeInstance"


-- or copy wzzip.exe to @CFG_BACKUP_PATH
-- and of course the script.

-- Modify @CFG_BACKUP_PATH = <Backup Store Path> - no long filename/directory please  
-- Modify @CFG_DAYS_DELETE = Days to keep backups  
-- Enable 'xp_cmdshell' (SQL 2005/EXPRESS or higher)    

----- Configuration Variables  

DECLARE @CFG_BACKUP_PATH NVARCHAR(256)  
DECLARE @CFG_DAYS_DELETE INT    
SET @CFG_BACKUP_PATH = 'C:\Store\DatabaseBackup'  
SET @CFG_DAYS_DELETE = 30    

DECLARE @Today DATETIME  
DECLARE @TodayName CHAR(8)  
SET @Today = GETDATE()  
SET @TodayName = CONVERT(CHAR(8), @Today, 112)    

DECLARE @id INT  
DECLARE @name VARCHAR(50)  
DECLARE @path VARCHAR(256)  
DECLARE @cmd VARCHAR(256)    

----- Create Temporarity Directory  

DECLARE @TempDir VARCHAR(256)  

SET @TempDir = @CFG_BACKUP_PATH + '\' + CONVERT(VARCHAR(256), NEWID())  
SET @cmd = 'md ' + @TempDir  
EXEC xp_cmdshell @cmd, no_output    

----- List of current databases, only 'ONLINE' databases to be backup  

DECLARE @dbList TABLE ( dbno INT IDENTITY, dbname NVARCHAR(256) )    

INSERT  INTO @dbList ( dbname )          
SELECT  name       
 FROM    master.dbo.sysdatabases 
  WHERE   ( name NOT IN ( 'tempdb' ) ) 
   AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
      

------ Starting backup, one by one  

SELECT  @id = dbno, @name = dbname  
 FROM    @dbList  
  WHERE   dbno = 1  WHILE @@ROWCOUNT = 1      

BEGIN
 PRINT N'++ Backup: ' + @name          
 SET @path = @TempDir + '\' + @name + '.bak'   
 BACKUP DATABASE @name TO DISK = @path 
 SELECT  @id = dbno, @name = dbname
  FROM    @dbList 
   WHERE   dbno = @id + 1
END
    
PRINT N'++ Compressing: ' + @TempDir    

----- Delete output file if existed  

SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP'  
EXEC xp_cmdshell @cmd, no_output    

DECLARE @Count INT  
DECLARE @StartTime DATETIME  
SET @StartTime = GETDATE()  

----- Compress, -mx1 = Set Compression Ratio to 1 (very low)  

-- SET @cmd = @CFG_BACKUP_PATH + '\7za.exe a -bd -y -tzip -mx1 ' 
-- SET @cmd = @CFG_BACKUP_PATH + '\wzzip.exe -a -yb ' 

SET @cmd = '7z.exe a -bd -y -tzip -mx1 ' 
SET @cmd = @cmd + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP ' + @TempDir + '\*.bak'  

EXEC xp_cmdshell @cmd, no_output    

SET @Count = DATEDIFF(second, @StartTime, GETDATE())  

PRINT N'++ Compression Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'  

SET @Count = DATEDIFF(second, @Today, GETDATE())  

PRINT N'++ Total Execution Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'    

---- Delete temporarity directory  

SET @cmd = 'rd /s /q ' + @TempDir  EXEC xp_cmdshell @cmd, no_output    

---- Delete previous backup versions  

DECLARE @OlderDateName CHAR(8)  
SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112)    

----- List all .ZIP files  

CREATE TABLE #delList( subdirectory VARCHAR(256), depth INT, [file] BIT )  

INSERT  INTO #delList EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1  

DELETE  #delList  
 WHERE   RIGHT(subdirectory, 4) <> '.ZIP'

SELECT  @Count = COUNT(1)  
 FROM    #delList  

PRINT N'++ Number of Backups: ' + CONVERT(NVARCHAR, @Count)    

SELECT TOP 1  @name = subdirectory  
 FROM    #delList  
  WHERE LEN(subdirectory) = 12   
   AND RIGHT(subdirectory, 4) = '.ZIP'          
   AND REPLACE(subdirectory, '.ZIP','') < @OlderDateName    
 WHILE ( @@ROWCOUNT = 1 )       

BEGIN 
 PRINT N'++ Delete Older Backup: ' + @name SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @name          
 EXEC xp_cmdshell @cmd, no_output     
 DELETE  #delList          
  WHERE   subdirectory = @name            

 SELECT TOP 1           @name = subdirectory 
  FROM    #delList
   WHERE   LEN(subdirectory) = 12  
    AND RIGHT(subdirectory, 4) = '.ZIP'
    AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName      
END    

DROP TABLE #delList    
PRINT N'++ Done.'  
PRINT ''  
PRINT ''  
PRINT ''  