备份数据库SQL Server 2008下实测

下面的存储过程适用：

1.一次想备份多个数据库。

2.只需要一步操作，在有存储过程的条件下。

3.可以根据自己的需要修改存储过程。

 

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

 数据非常重要，本脚本为学习交流用，正式数据库还请制定详细备份解决方案，欢迎大家提出改进意见。