sqlserver备份数据库--存储过程

--------------------------------------------------------------------------
----------------------------创建存储过程备份数据库------------------------
--------------------------------------------------------------------------


use master
if exists(select * from sysobjects where name='proc_backupdatabase' )
drop proc proc_backupdatabase
go
use master
go
create proc proc_backupdatabase
@bak_path nvarchar(4000)=''       --备份路径;
,@baktype int = null               --备份类型0为全备,1为差异备,2为日志备份
,@type int = null                  --设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@dbnames nvarchar(4000)=''        --需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@overdueDay int = null            --设置过期天数，默认天;
,@compression int =0               --是否采用sql2008的压缩备份,0为否,1为采用压缩
,@backerr nvarchar(10) = 0 output  --返回备份结果    -1失败    1成功
,@backname nvarchar(max) = '' output    --返回备份文件的名称
,@backpathname nvarchar(max) = '' output--返回备份文件的完整路径
as
--sql server 2005/2008备份/删除过期备份T-sql 版本v1.0
/*
author:perfectaction
date :2009.04
desc :适用于sql2005/2008备份，自动生成库文件夹，可以自定义备份类型和备份库名等
      可以自定义备份过期的天数
              删除过期备份功能不会删除最后一次备份，哪怕已经过期
              如果某库不再备份，那么也不会再删除之前过期的备份
       如有错误请指正，谢谢.
*/

set nocount on
--开启xp_cmdshell支持
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override
print char(13)+'------------------------'

--判断是否填写路径
if isnull(@bak_path,'')=''
    begin
        print('error:请指定备份路径')
        set @backerr = -1
        return
    end

--判断是否指定需要备份的库
if isnull(ltrim(@baktype),'')=''
    begin
        print('error:请指定备份类型aa:0为全备,1为差异备,2为日志备份')
        set @backerr = -1
        return
    end
else
    begin
        if @baktype not between 0 and 2
        begin
            print('error:指定备份类型只能为,1,2: 0为全备,1为差异备,2为日志备份')
set @backerr = -1
            return
        end
    end
--判断是否指定需要备份的库
if isnull(ltrim(@type),'')=''
    begin
        print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')
        set @backerr = -1
        return
    end
else
    begin
        if @type not between 0 and 4
        begin
            print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')
set @backerr = -1
            return
        end
    end

--判断指定库或排除库时，是否填写库名
if @type>2
    if @dbnames=''
    begin
        print('error:备份类型为'+ltrim(@type)+'时，需要指定@dbnames参数')
        set @backerr = -1
        return
    end

--判断指定指定过期时间
if isnull(ltrim(@overdueDay),'')=''
begin
    print('error:必须指定备份过期时间,单位为天,0为永不过期')
    set @backerr = -1
    return
end

--判断是否支持压缩
if @compression=1
    if charindex('2008',@@version)=0 or charindex('Enterprise',@@version)=0
    begin
        print('error:压缩备份只支持sql2008企业版')
        set @backerr = -1
        return
    end

--判断是否存在该磁盘
declare @drives table(drive varchar(1),[size] varchar(20))
insert into @drives exec('master.dbo.xp_fixeddrives')
if not exists(select 1 from @drives where drive=left(@bak_path,1))
    begin
        print('error:不存在该磁盘:'+left(@bak_path,1))
        set @backerr = -1
        return
    end
    set @backerr = 1
--格式化参数
select @bak_path=rtrim(ltrim(@bak_path)),@dbnames=rtrim(ltrim(@dbnames))
if right(isnull(@bak_path,''),1)!='\' set @bak_path=@bak_path+'\'
if isnull(@dbnames,'')!='' set @dbnames = ','+@dbnames+','
set @dbnames=replace(@dbnames,' ','')

--定义变量
declare @bak_sql nvarchar(max),@del_sql nvarchar(max),@i int,@maxid int
declare @dirtree_1 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int)
declare @dirtree_2 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int,
dbname varchar(300),baktime datetime,isLastbak int)
declare @createfolder nvarchar(max),@delbackupfile nvarchar(max),@delbak nvarchar(max)

--获取需要备份的库名--------------------start
declare @t table(id int identity(1,1) primary key,name nvarchar(max))
declare @sql nvarchar(max)
set @sql = 'select name from sys.databases where state=0 and name!=''tempdb'' '
    + case when @baktype=2 then ' and recovery_model!=3 ' else '' end
    + case @type when 0 then 'and 1=1'
        when 1 then 'and database_id<=4'
        when 2 then 'and database_id>4'
        when 3 then 'and charindex('',''+Name+'','','''+@dbnames+''')>0'
        when 4 then 'and charindex('',''+Name+'','','''+@dbnames+''')=0 and database_id>4'
        else '1>2' end
insert into @t exec(@sql)
--获取需要备份的库名---------------------end

--获取需要创建的文件夹------------------start
insert into @dirtree_1 exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')
select @createfolder=isnull(@createfolder,'')+'exec master.dbo.xp_cmdshell ''md '+@bak_path+''+name+''',no_output '+char(13)
from @t as a left join @dirtree_1 as b on a.name=b.subdirectory and b.files=0 and depth=1 where b.id is null
--获取需要创建的文件夹-------------------end


--生成处理过期备份的sql语句-------------start
if @overdueDay>0
begin
    insert into @dirtree_2(subdirectory,depth,files) exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')
    if @baktype =0
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Full_bak_',subdirectory)=0
    if @baktype =1
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Diff_bak_',subdirectory)=0
    if @baktype=2
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Log_bak_',subdirectory)=0
    if exists(select 1 from @dirtree_2)
    delete from @dirtree_2 where isdate(
            left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +
            substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2)
            )=0
    if exists(select 1 from @dirtree_2)
    update @dirtree_2 set dbname = case when @baktype=0 then left(subdirectory,charindex('_Full_bak_',subdirectory)-1)
        when @baktype=1 then left(subdirectory,charindex('_Diff_bak_',subdirectory)-1)
        when @baktype=2 then left(subdirectory,charindex('_Log_bak_',subdirectory)-1)
        else '' end   
        ,baktime=left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +
            substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2)
    from @dirtree_2 as a
    delete @dirtree_2 from @dirtree_2 as a left join @t as b on b.name=a.dbname where b.id is null
    update @dirtree_2 set isLastbak= case when (select max(baktime) from @dirtree_2 where dbname=a.dbname)=baktime
    then 1 else 0 end from @dirtree_2 as a
    select @delbak=isnull(@delbak,'')+'exec master.dbo.xp_cmdshell ''del '+@bak_path+''+dbname+'\'
    +subdirectory+''',no_output '+char(13) from @dirtree_2 where isLastbak=0 and datediff(day,baktime,getdate())>@overdueDay
end
--生成处理过期备份的sql语句--------------end



begin try
    print(@createfolder) --创建备份所需文件夹
    exec(@createfolder)   --创建备份所需文件夹
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
    return
end catch


select @i=1 ,@maxid=max(id) from @t
while @i<=@maxid
begin
--backup database [CommerceDB] to disk='D:\CommerceDB\CommerceDB_Full_bak_compression_20111203_144145.bak' with compression, noformat
    select @bak_sql=''+char(13)+'backup '+ case when @baktype=2 then 'log ' else 'database ' end
            +quotename(Name)+' to disk='''+@bak_path + Name+'\'+
            Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_'
            when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+
            replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+
            case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end +''''
            + case when @compression=1 or @baktype=1 then ' with ' else '' end
            + case when @compression=1 then 'compression,' else '' end
            + case when @baktype=1 then 'differential,' else '' end
            + case when @compression=1 or @baktype=1 then ' noformat' else '' end
    from @t where id=@i
    set @i=@i+1
    begin try
        print(@bak_sql)--循环执行备份
        exec(@bak_sql) --循环执行备份
    end try
    begin catch
        print 'err:'+ltrim(error_number())
        print 'err:'+error_message()
    end catch
end



--获取备份文件的名字
select @i=1 ,@maxid=max(id) from @t
while @i<=@maxid
begin
--backup database [CommerceDB] to disk='D:\CommerceDB\CommerceDB_Full_bak_compression_20111203_144145.bak' with compression, noformat
    select @backname=''+
            Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_'
            when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+
            replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+
            case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end
    from @t where id=@i
    set @i=@i+1
    begin try
        print(@backname)--循环执行备份
    end try
    begin catch
        print 'err:'+ltrim(error_number())
        print 'err:'+error_message()
    end catch
end

--获取备份文件的完整路径
select @i=1 ,@maxid=max(id) from @t
while @i<=@maxid
begin
--backup database [CommerceDB] to disk='D:\CommerceDB\CommerceDB_Full_bak_compression_20111203_144145.bak' with compression, noformat
    select @backpathname=''+@bak_path + Name+'\'+
            Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_'
            when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+
            replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+
            case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end
    from @t where id=@i
    set @i=@i+1
    begin try
        print(@backpathname)--循环执行备份
    end try
    begin catch
        print 'err:'+ltrim(error_number())
        print 'err:'+error_message()
    end catch
end




begin try
    print(@delbak)   --删除超期的备份
    exec(@delbak)    --删除超期的备份
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
end catch



--关闭xp_cmdshell支持
--exec sp_configure 'show advanced options', 1
--reconfigure with override
--exec sp_configure 'xp_cmdshell', 1
--reconfigure with override
--exec sp_configure 'show advanced options', 0
--reconfigure with override





--------------------------------调用示例--------------------------------------------


/*
--------------------------------在java中调用含输出参数(output)的存储过程以及获得outout的值--------------------------------------------
CallableStatement   cstmt   =   conn.preparedCall( "   {   call   proc_name(?,?,?,?,?,?)   } ");
cstmt.registerOutParameter   (1,   OracleTypes.CURSOR);//output   parameter
cstmt.setString(2, " ");
cstmt.setString(3, " ");
cstmt.setString(4, " ");
cstmt.setString(5, " ");
cstmt.setString(6, " ");
cstmt.execute();
ResultSet   rs   =   (ResultSet)cstmt.getObject(1);
*/

调用示例：

--备份用户库(全备)--建议一周一次
use master
go


exec proc_backupdatabase
--备份路径;
@bak_path ='D:'      
--备份类型0为全备,1为差异备,2为日志备份
,@baktype = 0
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;        
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效      
,@dbnames ='commercedb'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =1





--备份用户库(差异备)--建议一天一次
exec proc_backupdatabase
--备份路径;
@bak_path ='D:\temp\dbbak'      
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 1
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;        
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效      
,@dbnames ='db1,db2'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0


--备份用户库(日志备)--建议1小时一次
exec proc_backupdatabase
--备份路径;
@bak_path ='D:\temp\dbbak'      
--备份类型为全备,1为差异备,2为日志备份
,@baktype = 2
--设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;        
,@type = 3
--需要备份或排除的数据库，用,隔开，当@type=3或4时生效      
,@dbnames ='commercedb'
--设置过期天数，默认天;
,@overdueDay = 15
--是否采用sql2008的压缩备份,0为否,1为采用压缩
,@compression =0






--------------------------------------------------------------------------
-----------------------创建存储过程关闭指定数据库的链接-------------------
--------------------------------------------------------------------------


use master
if exists(select * from sysobjects where name='KillSpByDbName' )
drop proc KillSpByDbName
go 
use master
go
create proc KillSpByDbName(@dbname varchar(20))  
as  
begin  
declare @sql nvarchar(500),@temp varchar(1000) 
declare @spid int  
set @sql='declare getspid cursor for  
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'  
exec (@sql)  
open getspid  
fetch next from getspid into @spid  
while @@fetch_status <>-1  
begin  
  set @temp='kill '+rtrim(@spid) 
  exec(@temp) 
fetch next from getspid into @spid  
end  
close getspid  
deallocate getspid  
end  



--举例使用，关闭数据库下的所有连接操作 
Use master  
Exec KillSpByDbName 'commercedb' 

--------------------------------------------------------------------------
-------------------------------------还原数据库---------------------------
--------------------------------------------------------------------------


use master
if exists(select * from sysobjects where name='bakDatabase' )
drop proc bakDatabase
go 
use master
go
create proc bakDatabase(@dbname varchar(20),@dbpath varchar(100)) 
as  
begin
RESTORE DATABASE @dbname FROM DISK =@dbpath WITH  REPLACE
end   


USE master

GO

--Full Database restore 完整数据库备份还原

RESTORE DATABASE CommerceDB

FROM DISK = 'D:\CommerceDB\CommerceDB_Full_bak_compression_20111106_164858.bak'

WITH  REPLACE

-- Differential 差异备份还原

RESTORE DATABASE TrainingDB

FROM DISK='C:\Apress\Recipes\TrainingDB_DifExample.diff'

WITH NORECOVERY

-- Transaction log 事务日志备份还原

RESTORE LOG TrainingDB

FROM DISK='C:\Apress\Recipes\TrainingDB_DifExample_tlog.trn'

WITH RECOVERY


/*最后一次还原数据库必须置为NORECOVERY模式，

而且任何你希望还原的事务日志必须在附加了差异备份之后完成，

在最后一次还原操作时，指定 RECOVERY选项使数据库处于可用状态。*/