既能备份到网络中的共享文件夹中，也能备份到本地
USE [AdventureWorks2012]
GO
/****** Object: StoredProcedure [dbo].[pr_BatchBackUPDatabase] Script Date: 2016/1/7 17:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO ------------执行此脚本需要先执行表值函数---------
--use [AdventureWorks2012]
--go
---- =============================================
---- 调用：SELECT Id FROM [Fn_StringSplitToTable]('1,12,36,65,58,56,DF',',')
---- Create date: <Create Date,,>
---- Description: 把用逗号分隔的字符串转换成表格数据
---- =============================================
--CREATE FUNCTION [dbo].[Fn_StringSplitToTable]
--(
-- @StringX varchar(MAX),
-- @Split nvarchar(10)
--)
--RETURNS
--@IdsTable TABLE
--(
-- [Id] nvarchar(200)
--)
--AS
--BEGIN
-- DECLARE @Index int
-- DECLARE @LenIndex int
-- SET @LenIndex=LEN(@Split)
-- SET @Index=CHARINDEX(@Split,@StringX,1)
-- WHILE (@Index>=1)
-- BEGIN
-- IF(LEFT(@StringX,@Index-1) <> '')
-- INSERT INTO @IdsTable SELECT LTRIM(RTRIM(LEFT(@StringX,@Index-1)))
-- SET @StringX=RIGHT(@StringX,LEN(@StringX)-@Index-@LenIndex+1)
-- SET @Index=CHARINDEX(@Split,@StringX,1)
-- END
-- IF(@StringX<>'') INSERT INTO @IdsTable SELECT LTRIM(RTRIM(@StringX)) -- RETURN
--END ----------执行此脚本需要开启xp_cmdshell---------
--USE Master
--GO
--EXEC sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE WITH OVERRIDE;
--GO
--EXEC sp_configure 'xp_cmdshell',1;
--GO
--RECONFIGURE WITH OVERRIDE;
--GO
--==================================================
--<Author> limy
--<Create Date> 2015-12
--数据库备份
--exec pr_BatchBackUPDatabase @BackupDirectoryPath='\\192.168.0.130\DBShare', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='bak',@BackUpType=1,@IsCompress=1
--exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='bak', @BackUpType=1,@IsCompress=1
--exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='trn', @BackUpType=2,@IsCompress=1
--exec pr_BatchBackUPDatabase @BackupDirectoryPath='D:\db', @DataBase='4', @SelectedDataBase='AdventureWorks2012', @SubDirectoryMode=1, @ExtenName='dif', @BackUpType=3,@IsCompress=1
--====================================================
ALTER PROCEDURE [dbo].[pr_BatchBackUPDatabase]
@BackupDirectoryPath NVARCHAR(50)='C:\',
@DataBase TINYINT = 1,--1 所有数据库，2 系统数据库，3 所有用户数据库，4 指定数据库
@SelectedDataBase NVARCHAR(1000)='', --指定的数据库
@SubDirectoryMode BIT = 1,--是否为每个数据库创建子目录
@ExtenName NVARCHAR(50)='bak',--备份文件扩展名(不要.)
--@CheckDB bit = 0, --验证备份完整性
@BackUpType TINYINT = 1, --1 完整备份 2 日志备份， 3 差异备份
@IsCompress BIT = 0 --是否备份压缩
AS
BEGIN --要备份的数据列表
DECLARE @dbname TABLE(
DbName VARCHAR(100) )
--已经存在的数据库目录
DECLARE @DirExistName TABLE(
DbName VARCHAR(100) )
declare @SQL nvarchar(500)
declare @ErrorInfo nvarchar(1000) --数据库的值是否合理
if(@DataBase not in(1,2,3,4))
begin
SET @ErrorInfo = N'1 所有数据库，2 系统数据库，3 所有用户数据库，4 指定数据库 ，请选择要备份的数据库！'
RAISERROR(@ErrorInfo,16,1)
RETURN;
end --是否指定了数据库
if @DataBase=4 and isnull(@SelectedDataBase,'')=''
begin
SET @ErrorInfo = N'要备份的数据库为指定数据库时，请输入数据库，多个数据库中间用[,]隔开！'
RAISERROR(@ErrorInfo,16,2)
RETURN;
end --把要备份的数据库，放到一个表里
--1 所有数据库
if @DataBase=1
begin
insert into @dbname
select name from master..sysdatabases where name <>'tempdb'
end
--2 系统数据库
if @DataBase=2
begin
insert into @dbname
select name from master..sysdatabases where sid = 0x01 and name <>'tempdb'
end
--3 所有用户数据库
if @DataBase=3
begin
insert into @dbname
select name from master..sysdatabases where sid <> 0x01 and name <>'tempdb'
end
--4 指定数据库
if @DataBase=4
begin
insert into @dbname
SELECT Id FROM [Fn_StringSplitToTable](@SelectedDataBase,',')
end --循环表,创建子目录
declare @name nvarchar(100)
declare cur cursor for
select DbName from @dbname
open cur
fetch next from cur into @name
while(@@fetch_status=0)
begin
--完备
declare @strDate varchar(30),@backupFile varchar(200),@currentDate datetime,@backName nvarchar(100)--@Day varchar(20),
set @currentDate=getdate()
set @strDate=format(getdate(),'yyyyMMdd_HHmmss')
set @backName=@name+N'-完整 数据库 备份' set @backupFile = @BackupDirectoryPath+'\'+@name +'_'+@strDate+'.bak' --是否为每个数据库创建子目录
if @SubDirectoryMode =1
begin set @SQL = 'xp_cmdshell ''dir '+@BackupDirectoryPath+' /b /a:d'''
insert into @DirExistName
exec (@SQL)
----调试用-------------------------------------------------------------------------------------------------------------------
--RAISERROR(@SQL,16,2) ;RETURN;-----------------------------------------------------------------------------------
if not exists(select 1 from @DirExistName where DBName=@name)
begin
set @SQL='xp_cmdshell ''mkdir '+@BackupDirectoryPath +'\'+@name+''''
exec ( @SQL )
end set @backupFile = @BackupDirectoryPath+'\'+@name+'\'+@name +'_'+@strDate+'.'+@ExtenName
end IF(@BackUpType=1)--完整备份
BEGIN
--开始备份
if @IsCompress=1
begin
backup database @name to disk=@backupFile WITH NOINIT, NAME = @backName, COMPRESSION
end
else
begin
backup database @name to disk=@backupFile WITH NOINIT, NAME = @backName, NO_COMPRESSION
end
END
IF(@BackUpType=2)--日志备份
BEGIN
--开始备份
if @IsCompress=1
begin
backup log @name to disk=@backupFile WITH NOINIT, NAME = @backName, COMPRESSION
end
else
begin
backup log @name to disk=@backupFile WITH NOINIT, NAME = @backName, NO_COMPRESSION
end
END IF(@BackUpType=3)--差异备份
BEGIN
--开始备份
if @IsCompress=1
begin
backup database @name to disk=@backupFile WITH differential ,NOINIT, NAME = @backName, COMPRESSION
end
else
begin
backup database @name to disk=@backupFile WITH differential,NOINIT, NAME = @backName, NO_COMPRESSION
end
END fetch next from cur into @name
end
close cur
deallocate cur print N'备份完成。';
return; END