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 'master\model\msdb\tempdb',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\rar.exe" a -ep '+@RarFullFileName+' '+@BakFullFileName

         exec xp_cmdshell @sql

    */

    set @DynamicSQL='cd /d D:\Program Files\7-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:\bakDB','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