1 CREATE proc [dbo].[usp_autoBackupDB]
2 @dbname sysname=null --要备份的数据库名，不指定即为全部备份
3 ,@path nvarchar(128)='d:\' --备份目录路径
4 ,@backup_type varchar(16)='database' --备份类型，可以为database,log
5 ,@backup_sysdb int=0 --是否备份系统数据库，0为不备份，1为备份
6 as
7 set nocount on;
8
9 declare @dbcnt int =0
10 ,@sql varchar(2000)=''
11 ,@except_db varchar(1000)=case @backup_sysdb
12 when 0 then ''''+'master'+''''+','+'''' +'msdb'+''''+','+''''+'tempdb'+''''+','+''''+'model'+''''
13 when 1 then '' end;
14 declare @db_list table(id int identity(1,1) not null,name sysname);
15 declare @backup_err_list table(id int identity(1,1) not null,name sysname);
16 if right(@path,1)<>'\'
17 set @path=@path+'\'
18
19 if @dbname is null or @dbname in ('all','*')
20 begin
21 --将所有数据库名存到一张临时表上
22 set @sql='select name from sys.databases where name not in ('+@except_db+');'
23 insert into @db_list(name) exec(@sql);
24
25 --得到一共有多少个数据库
26 select @dbcnt=count(1) from @db_list;
27 --开始循环
28 while @dbcnt>0
29 begin
30 --从临时表中获得最后一个数据库的名字
31 select @dbname=name from @db_list where id=@dbcnt;
32 set @sql='backup '+@backup_type+' '+@dbname+' to disk='+''''+@path+@backup_type+'_'+@dbname+'.'+convert(varchar(8),getdate(),112)+'.'+DATENAME(HH,GETDATE())+''''
33 --开始循环备份
34 exec (@sql);
35 if @@ERROR<>0
36 insert into @backup_err_list(name) values(@dbname);
37 set @dbcnt=@dbcnt-1
38 end
39 end
40 else
41 begin
42 set @sql='backup '+@backup_type+' '+@dbname+' to disk='+''''+@path+@backup_type+'_'+@dbname+'.'+convert(varchar(8),getdate(),112)+'.'+DATENAME(HH,GETDATE())+''''
43 --仅备份一次
44 exec (@sql);
45 if @@ERROR<>0
46 insert into @backup_err_list(name) values(@dbname);
47
48 end
49 if exists(select * from @backup_err_list)
50 select ID ,name as 'backup_err_dbname' from @backup_err_list;
51 else print 'backup success';
52
53 set nocount off;
54
55 GO
