SQL Server 2008 备份改进版

1.Add compressing function with 7-Zip

2.With tool win.rar code so you can change it if you want

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDBBatch]    Script Date: 09/27/2013 22:25:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------
    Description:
        1.Backup multiple Database
        2.If you want to backup database except 'mastermodelmsdb	empdb',you can input '%' of the second parameter
        3.parameter 1 is the path where you want to back,parameter 2 is the database name you want to backup
        3.This procedure backup database with fuzzy database name
        
        
    Author:jiangxiaoqiang
    Date:2013-09-27
    Modify history:
    2013-09-27    jiangxiaoqiang    add compressing function,the compressing tool 7-Zip.
-----------------------------*/




ALTER procedure [dbo].[sp_BackupDBBatch]
    @backup_path varchar(200),
    @backup_name varchar(200)
as
begin
    declare @DynamicSQL varchar(256)
    set @DynamicSQL='if not exist '+@backup_path+'\%Date:~0,4%%Date:~5,2%%Date:~8,2% mkdir '+@backup_path+'\%Date:~0,4%%Date:~5,2%%Date:~8,2%'
    exec xp_cmdshell @DynamicSQL
    
    declare @backup_path_detail varchar(200)
    set @backup_path_detail=@backup_path+''+CONVERT(char(8),GETDATE(),112)
    --declare variable
    DECLARE @dbname nvarchar(200)
    --SET @backup_path='D:DatabaseBackup'--you can define by yourself
    /*
     declare cursor syntax
     DECLARE cursor_name CURSOR 
     [LOCAL | GLOBAL] 
     [FORWARD_ONLY | SCROLL] 
     [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] 
     [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] 
     [TYPE_WARNING] 
     FOR select_statement 
     [FOR UPDATE [OF column_name [,...n]]] 
    */
    DECLARE db_info CURSOR --declare cursor
    LOCAL
    STATIC
    READ_ONLY
    FORWARD_ONLY
    FOR --add other condition by select
    SELECT name
    FROM master.sys.databases with(NOLOCK)
    WHERE database_id>4 and name like '%'+@backup_name+'%'--maser,model,msdb,tempdb
     --here you can add the condition like:name like'NJPEC%'
    OPEN db_info --open cursor
    FETCH NEXT FROM db_info INTO @dbname
   
    WHILE @@FETCH_STATUS=0
    BEGIN
     EXEC master.dbo.backupDB @dbname,@backup_path_detail
     FETCH NEXT FROM db_info INTO @dbname
    END
    CLOSE db_info
    DEALLOCATE db_info
---------------------------END of backup database--------------------------------
   --Find&list all database
   /*
    select * 
    from master.sys.databases
    order by database_id;
   */
   
   /*
        This code to compress backup file to specify dictionary.
        My tool is 7zip,and you can change the code with your
        specifical compress tool.Read the help manual.    
        
        There is another tool code,you can reference:
        select @sql='"C:Program Files (x86)WinRAR
ar.exe" a -ep '+@RarFullFileName+' '+@BakFullFileName
        exec xp_cmdshell @sql        
   */
   
   set @DynamicSQL='cd /d D:Program Files7-Zip&&7z a '+@backup_path_detail+''+CONVERT(char(8),GETDATE(),112)+'.zip '+@backup_path_detail+'*.bak -mx9'
   exec xp_cmdshell @DynamicSQL   
   -------------------------------end of compressing file----------------------------------------
end


--exec sp_BackupDBBatch 'F:akDB','Arc'

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[backupDB]    Script Date: 09/27/2013 23:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[backupDB] 
(
  --We'd better add bracket even if no parameters,it is up to you.
  @DatabaseName nvarchar(200), 
  @Path nvarchar(200)
)
AS
BEGIN
 DECLARE @FileName varchar(200),@sql varchar(1000)
 --RIGHT function:return the @Path last X word
 --CONVERT function:CONVERT(data_type(length),data_to_be_converted,style)
 --112 representative yymmdd format
 --108 representative hh:mm:ss format
  SET @FileName=@Path+(CASE WHEN RIGHT(@Path,1)<>'' THEN '' ELSE '' END)
           +@DatabaseName+'_'+CONVERT(char(8),GETDATE(),112)+'_' 
           +replace(convert(char(8),getdate(),108),':','')+'.bak'
  --to explain the ''':the ' before to and the last ' was a couple.
  --the backup folder must be exists,and N must be uppercase.
  --what's the 'N' meaning?It means the varible using code with unicode.
  SET @sql='backup database '+@DatabaseName+' to disk =N'''+@FileName+''''
  EXEC(@sql)
 END

This code have many comment,if someone make it progress,thank you!