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为全备,2为日志备份')         set @backerr = -1         return     end else     begin         if @baktype not between 0 and 2         begin             print('error:指定备份类型只能为,1,2: 0为全备,2为日志备份') set @backerr = -1             return         end     end --判断是否指定需要备份的库 if isnull(ltrim(@type),'')=''     begin         print('error:请指定需要备份的库,4为排除指定库')         set @backerr = -1         return     end else     begin         if @type not between 0 and 4         begin             print('error:请指定需要备份的库,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,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,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('','''+@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+''',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+''',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,13,2) +':'+substring(right(subdirectory,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,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,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),'')+             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,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),'')+             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为全备,@baktype = 0 --设置需要备份的库,4为排除指定库;        ,@type = 3 --需要备份或排除的数据库，用,隔开，当@type=3或4时生效      ,@dbnames ='commercedb' --设置过期天数，默认天;,@overdueDay = 15 --是否采用sql2008的压缩备份,@compression =1 --备份用户库(差异备)--建议一天一次 exec proc_backupdatabase --备份路径; @bak_path ='D:\temp\dbbak'       --备份类型为全备,@baktype = 1 --设置需要备份的库,@dbnames ='db1,db2' --设置过期天数，默认天;,@compression =0 --备份用户库(日志备)--建议1小时一次 exec proc_backupdatabase --备份路径; @bak_path ='D:\temp\dbbak'       --备份类型为全备,@baktype = 2 --设置需要备份的库,@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选项使数据库处于可用状态。*/ 总结

以上是内存溢出为你收集整理的sqlserver备份数据库--存储过程全部内容，希望文章能够帮你解决sqlserver备份数据库--存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。