/*-----------------------------

	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:

-----------------------------*/

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

   --Find&list all database

   /*

    select *

    from master.sys.databases

    order by database_id;

   */

end

ALTER PROC [dbo].[backupDB]

(

  --We'd better add bracket even if no parameters

  @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