既能备份到网络中的共享文件夹中，也能备份到本地
1 USE [AdventureWorks2012] 2 GO 3 /****** Object: StoredProcedure [dbo].[pr_BatchBackUPDatabase] Script Date: 2016/1/7 17:56:01 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 9 10 ------------执行此脚本需要先执行表值函数--------- 11 --use [AdventureWorks2012] 12 --go 13 ---- ============================================= 14 ---- 调用：SELECT Id FROM [Fn_StringSplitToTable]('1,12,36,65,58,56,DF',',') 15 ---- Create date: <Create Date,,> 16 ---- Description: 把用逗号分隔的字符串转换成表格数据 17 ---- ============================================= 18 --CREATE FUNCTION [dbo].[Fn_StringSplitToTable] 19 --( 20 -- @StringX varchar(MAX), 21 -- @Split nvarchar(10) 22 --) 23 --RETURNS 24 --@IdsTable TABLE 25 --( 26 -- [Id] nvarchar(200) 27 --) 28 --AS 29 --BEGIN 30 -- DECLARE @Index int 31 -- DECLARE @LenIndex int 32 -- SET @LenIndex=LEN(@Split) 33 -- SET @Index=CHARINDEX(@Split,@StringX,1) 34 -- WHILE (@Index>=1) 35 -- BEGIN 36 -- IF(LEFT(@StringX,@Index-1) <> '') 37 -- INSERT INTO @IdsTable SELECT LTRIM(RTRIM(LEFT(@StringX,@Index-1))) 38 -- SET @StringX=RIGHT(@StringX,LEN(@StringX)-@Index-@LenIndex+1) 39 -- SET @Index=CHARINDEX(@Split,@StringX,1) 40 -- END 41 -- IF(@StringX<>'') INSERT INTO @IdsTable SELECT LTRIM(RTRIM(@StringX)) 42 43 -- RETURN 44 --END 45 46 ----------执行此脚本需要开启xp_cmdshell--------- 47 --USE Master 48 --GO 49 --EXEC sp_configure 'show advanced options', 1; 50 --GO 51 --RECONFIGURE WITH OVERRIDE; 52 --GO 53 --EXEC sp_configure 'xp_cmdshell',1; 54 --GO 55 --RECONFIGURE WITH OVERRIDE; 56 --GO 57 --================================================== 58 --<Author> limy 59 --<Create Date> 2015-12 60 --数据库备份 61 --exec pr_BatchBackUPDatabase @BackupDirectoryPath='\\192.168.0.130\DBShare', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='bak',@BackUpType=1,@IsCompress=1 62 --exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='bak', @BackUpType=1,@IsCompress=1 63 --exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='trn', @BackUpType=2,@IsCompress=1 64 --exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='dif', @BackUpType=3,@IsCompress=1 65 --==================================================== 66 ALTER PROCEDURE [dbo].[pr_BatchBackUPDatabase] 67 @BackupDirectoryPath NVARCHAR(50)='C:\', 68 @DataBase TINYINT = 1,--1 所有数据库，2 系统数据库，3 所有用户数据库，4 指定数据库 69 @SelectedDataBase NVARCHAR(1000)='', --指定的数据库 70 @SubDirectoryMode BIT = 1,--是否为每个数据库创建子目录 71 @ExtenName NVARCHAR(50)='bak',--备份文件扩展名(不要.) 72 --@CheckDB bit = 0, --验证备份完整性 73 @BackUpType TINYINT = 1, --1 完整备份 2 日志备份， 3 差异备份 74 @IsCompress BIT = 0 --是否备份压缩 75 AS 76 BEGIN 77 78 --要备份的数据列表 79 DECLARE @dbname TABLE( 80 DbName VARCHAR(100) ) 81 --已经存在的数据库目录 82 DECLARE @DirExistName TABLE( 83 DbName VARCHAR(100) ) 84 declare @SQL nvarchar(500) 85 declare @ErrorInfo nvarchar(1000) 86 87 --数据库的值是否合理 88 if(@DataBase not in(1,2,3,4)) 89 begin 90 SET @ErrorInfo = N'1 所有数据库，2 系统数据库，3 所有用户数据库，4 指定数据库 ，请选择要备份的数据库！' 91 RAISERROR(@ErrorInfo,16,1) 92 RETURN; 93 end 94 95 --是否指定了数据库 96 if @DataBase=4 and isnull(@SelectedDataBase,'')='' 97 begin 98 SET @ErrorInfo = N'要备份的数据库为指定数据库时，请输入数据库，多个数据库中间用[,]隔开！' 99 RAISERROR(@ErrorInfo,16,2) 100 RETURN; 101 end 102 103 --把要备份的数据库，放到一个表里 104 --1 所有数据库 105 if @DataBase=1 106 begin 107 insert into @dbname 108 select name from master..sysdatabases where name <>'tempdb' 109 end 110 --2 系统数据库 111 if @DataBase=2 112 begin 113 insert into @dbname 114 select name from master..sysdatabases where sid = 0x01 and name <>'tempdb' 115 end 116 --3 所有用户数据库 117 if @DataBase=3 118 begin 119 insert into @dbname 120 select name from master..sysdatabases where sid <> 0x01 and name <>'tempdb' 121 end 122 --4 指定数据库 123 if @DataBase=4 124 begin 125 insert into @dbname 126 SELECT Id FROM [Fn_StringSplitToTable](@SelectedDataBase,',') 127 end 128 129 130 131 --循环表,创建子目录 132 declare @name nvarchar(100) 133 declare cur cursor for 134 select DbName from @dbname 135 open cur 136 fetch next from cur into @name 137 while(@@fetch_status=0) 138 begin 139 --完备 140 declare @strDate varchar(30),@backupFile varchar(200),@currentDate datetime,@backName nvarchar(100)--@Day varchar(20), 141 set @currentDate=getdate() 142 set @strDate=format(getdate(),'yyyyMMdd_HHmmss') 143 set @backName=@name+N'-完整 数据库 备份' 144 145 set @backupFile = @BackupDirectoryPath+'\'+@name +'_'+@strDate+'.bak' 146 147 --是否为每个数据库创建子目录 148 if @SubDirectoryMode =1 149 begin 150 151 set @SQL = 'xp_cmdshell ''dir '+@BackupDirectoryPath+' /b /a:d''' 152 insert into @DirExistName 153 exec (@SQL) 154 ----调试用------------------------------------------------------------------------------------------------------------------- 155 --RAISERROR(@SQL,16,2) ;RETURN;----------------------------------------------------------------------------------- 156 if not exists(select 1 from @DirExistName where DBName=@name) 157 begin 158 set @SQL='xp_cmdshell ''mkdir '+@BackupDirectoryPath +'\'+@name+'''' 159 exec ( @SQL ) 160 end 161 162 set @backupFile = @BackupDirectoryPath+'\'+@name+'\'+@name +'_'+@strDate+'.'+@ExtenName 163 end 164 165 IF(@BackUpType=1)--完整备份 166 BEGIN 167 --开始备份 168 if @IsCompress=1 169 begin 170 backup database @name to disk=@backupFile WITH NOINIT, NAME = @backName, COMPRESSION 171 end 172 else 173 begin 174 backup database @name to disk=@backupFile WITH NOINIT, NAME = @backName, NO_COMPRESSION 175 end 176 END 177 IF(@BackUpType=2)--日志备份 178 BEGIN 179 --开始备份 180 if @IsCompress=1 181 begin 182 backup log @name to disk=@backupFile WITH NOINIT, NAME = @backName, COMPRESSION 183 end 184 else 185 begin 186 backup log @name to disk=@backupFile WITH NOINIT, NAME = @backName, NO_COMPRESSION 187 end 188 END 189 190 IF(@BackUpType=3)--差异备份 191 BEGIN 192 --开始备份 193 if @IsCompress=1 194 begin 195 backup database @name to disk=@backupFile WITH differential ,NOINIT, NAME = @backName, COMPRESSION 196 end 197 else 198 begin 199 backup database @name to disk=@backupFile WITH differential,NOINIT, NAME = @backName, NO_COMPRESSION 200 end 201 END 202 203 204 205 fetch next from cur into @name 206 end 207 close cur 208 deallocate cur 209 210 print N'备份完成。'; 211 return; 212 213 END