备份与回复数据库的积累进程,完整备份和距离备份数据库的SQL脚本

干活中须要成立SQL Job对数据库举办为期备份,现把脚本记录如下。

create proc dbbf_test
as
declare
@dbname varchar(20),
@cmd1 nvarchar(120),
@cmd2 varchar(120),
@cmd3 varchar(120),
@i int,
@filename varchar(80),
@path varchar(80)
set
@dbname=’TEST’–\\那是数据库名,使用时只须求把它改成你所要备份的多寡库名就能够,那一个必须修改\\–
—-删除当前几日期前1伍-前10天内的数据库备份,可根要求活动修改—-

备份与回复数据库的蕴藏进度

  1. 1体化备份:

    — FULL
    declare @filename varchar(1024),

         @file_dev    varchar(300)
    

    declare @path varchar(1024)
    set @path = N’F:\Backup\Plan2\’;

    declare @extension_name varchar(16)
    set @extension_name = N’bak’;

    set @filename = convert(varchar(1024), getdate(), 120)
    set @filename = replace(@filename, ‘:’, ”)
    set @filename = replace(@filename, ‘-‘, ”)
    set @filename = replace(@filename, ‘ ‘, ”)

    set @filename = @filename + ‘_’ + convert (varchar(3), datepart(ms, getdate())) + N’.’ + @extension_name

    — start backup, COMPRESSION is the parameter
    set @file_dev = @path + ‘SmartDevFull‘ + @filename
    backup database [SmartDev] to disk = @file_dev
    with noformat, init, name = N’SmartDev-Database full backup’, COMPRESSION

    — delete the old backup file 1 days ago
    declare @olddate datetime
    select @olddate=getdate()-1

    — execute delete
    select @path
    select @extension_name
    select @olddate
    execute master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1
    go

set @i=10
while @i<15
begin 
set @cmd1  =’if exist
E:\DATABACKUP\’+@dbname+convert(varchar(10),DATEADD(day,-@i,getdate()),112)
+’*’ +’ del 
‘+’E:\DATABACKUP\’+@dbname+convert(varchar(10),DATEADD(day,-@i,getdate()),112)
+’*’
exec master..xp_cmdshell @cmd壹—-剔除拾天在此之前的数据库备份   
set @i=@i+1
end
—-备份数据库—-

  1.   
  2. 那种办法认为完成起来也不易,笔者是应用那种措施来完结数据库备份还原的成效的。 
      
  3. 亟需提议上边这么些囤积进程是在网络搜寻到的。感激代码的提供者。在此转发 
      
  4.   
  5. 应用T-SQL语句,完毕数据库的备份与还原的功能    
  6.   
  7. 展现了SQL Server中的三个知识点:    
  8.   
  9. 壹. 拿走SQL Server服务器上的暗许目录    
  10.   
  11. 2. 备份SQL语句的利用    
  12.   
  13. 三. 回复SQL语句的施用,同时惦念了要挟苏醒时关闭别的用户进度的拍卖 
      
  14.   
  15. 肆. 功课创立SQL语句的应用    
  16.   
  17.   
  18.   
  19. /*1.–获得数据库的文件目录   
  20.  
  21. @dbname 钦赐要博得目录的数据库名   
  22. 假使钦命的数额不存在,重回安装SQL时设置的默许数据目录 
     
  23. 1经内定NULL,则赶回暗中同意的SQL备份目录名 
     
  24. */    
  25.   
  26. /*–调用示例   
  27. select 数据库文件目录=dbo.f_getdbpath(‘tempdb’) 
     
  28. ,[997755.com澳门葡京 ,默许SQL SE猎豹CS⑥VEEscort数据目录]=dbo.f_getdbpath(”) 
     
  29. ,[私下认可SQL SEPRADOVE瑞鹰备份目录]=dbo.f_getdbpath(null) 
     
  30. –*/    
  31. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdbpath]’) and xtype in (N’FN’, N’IF’, N’TF’))    
  32. drop function [dbo].[f_getdbpath]    
  33. GO    
  34.   
  35. create function f_getdbpath(@dbname sysname)    
  36. returns nvarchar(260)    
  37. as    
  38. begin    
  39. declare @re nvarchar(260)    
  40. if @dbname is null or db_id(@dbname) is null    
  41. select @re=rtrim(reverse(filename)) from master..sysdatabases where name=’master’    
  42. else    
  43. select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname    
  44.   
  45. if @dbname is null    
  46. set @re=reverse(substring(@re,charindex(‘\’,@re)+5,260))+’BACKUP’    
  47. else    
  48. set @re=reverse(substring(@re,charindex(‘\’,@re),260))    
  49. return(@re)    
  50. end    
  51. go    
  52.   
  53.   
  54. /*二.–备份数据库   
  55.  
  56. */    
  57.   
  58. /*–调用示例   
  59. 备份与回复数据库的积累进程,完整备份和距离备份数据库的SQL脚本。 
  60. –备份当前数据库   
  61. exec p_backupdb @bkpath=’c:\’,@bkfname=’db_\DATE\_db.bak’ 
     
  62.  
  63. –差距备份当前数据库   
  64. exec p_backupdb @bkpath=’c:\’,@bkfname=’db_\DATE\_df.bak’,@bktype=’DF’ 
     
  65.  
  66. –备份当前数据库日志   
  67. exec p_backupdb @bkpath=’c:\’,@bkfname=’db_\DATE\_log.bak’,@bktype=’LOG’ 
     
  68.  
  69. –*/    
  70.   
  71. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)    
  72. drop procedure [dbo].[p_backupdb]    
  73. GO    
  74.   
  75. create proc p_backupdb    
  76. @dbname sysname=”, –要备份的数据库名称,不钦点则备份当前数据库 
      
  77. @bkpath nvarchar(260)=”, –备份文件的存放目录,不钦赐则利用SQL暗中同意的备份目录 
      
  78. @bkfname nvarchar(260)=”, –备份文件名,文件名中得以用\DBNAME\代表数量库名,\DATE\表示日期,\TIME\意味着时间 
      
  79. @bktype nvarchar(十)=’DB’, –备份类型:’DB’备份数据库,’DF’ 差距备份,’LOG’ 日志备份    
  80. @appendfile bit=一 –追加/覆盖备份文件    
  81. as    
  82. declare @sql varchar(8000)    
  83. if isnull(@dbname,”)=” set @dbname=db_name()    
  84. if isnull(@bkpath,”)=” set @bkpath=dbo.f_getdbpath(null)    
  85. if isnull(@bkfname,”)=” set @bkfname=’\DBNAME\_\DATE\_\TIME\.BAK’    
  86. set @bkfname=replace(replace(replace(@bkfname,’\DBNAME\’,@dbname)    
  87. ,’\DATE\’,convert(varchar,getdate(),112))    
  88. ,’\TIME\’,replace(convert(varchar,getdate(),108),’:’,”))    
  89. set @sql=’backup ‘+case @bktype when ‘LOG’ then ‘log ‘ else ‘database ‘ end +@dbname    
  90. +’ to disk=”’+@bkpath+@bkfname    
  91. +”’ with ‘+case @bktype when ‘DF’ then ‘DIFFERENTIAL,’ else ” end 
      
  92. +case @appendfile when 1 then ‘NOINIT’ else ‘INIT’ end    
  93. print @sql    
  94. exec(@sql)    
  95. go    
  96.   
  97.   
  98.   
  99. /*三.–复苏数据库   
  100.  
  101. */    
  102.   
  103. /*–调用示例   
  104. –完整过来数据库   
  105. exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’ 
     
  106.  
  107. –差别备份复苏   
  108. exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’DBNOR’ 
     
  109. exec p_backupdb @bkfile=’c:\db_20031015_df.bak’,@dbname=’db’,@retype=’DF’ 
     
  110.  
  111. –日志备份苏醒   
  112. exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’DBNOR’ 
     
  113. exec p_backupdb @bkfile=’c:\db_20031015_log.bak’,@dbname=’db’,@retype=’LOG’ 
     
  114.  
  115. –*/    
  116.   
  117. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)    
  118. drop procedure [dbo].[p_RestoreDb]    
  119. GO    
  120.   
  121. create proc p_RestoreDb    
  122. @bkfile nvarchar(一千), –定义要苏醒的备份文件名    
  123. @dbname sysname=”, –定义苏醒后的数额库名,默许为备份的文件名 
      
  124. @dbpath nvarchar(260)=”, –恢复生机后的数据仓库储存放目录,不点名则为SQL的私下认可数据目录 
      
  125. @retype nvarchar(十)=’DB’, –恢复生机项目:’DB’完事复苏数据库,’DBNO中华V’ 为距离恢复生机,日志苏醒举办总体过来,’DF’ 差距备份的复原,’LOG’ 日志恢复生机    
  126. @filenumber int=一, –苏醒的文书号    
  127. @overexist bit=一, –是不是覆盖已经存在的数据库,仅@retype为    
  128. @killuser bit=1 –是不是关闭用户选用进度,仅@overexist=壹时有效    
  129. as    
  130. declare @sql varchar(8000)    
  131.   
  132. –拿到恢复生机后的数据库名    
  133. if isnull(@dbname,”)=”    
  134. select @sql=reverse(@bkfile)    
  135. ,@sql=case when charindex(‘.’,@sql)=0 then @sql 
      
  136. else substring(@sql,charindex(‘.’,@sql)+1,1000) end    
  137. ,@sql=case when charindex(‘\’,@sql)=0 then @sql 
      
  138. else left(@sql,charindex(‘\’,@sql)-1) end 
      
  139. ,@dbname=reverse(@sql)    
  140.   
  141. –获得上升后的数据仓库储存放目录    
  142. if isnull(@dbpath,”)=” set @dbpath=dbo.f_getdbpath(”)    
  143.   
  144. –生成数据库苏醒语句    
  145. set @sql=’restore ‘+case @retype when ‘LOG’ then ‘log ‘ else ‘database ‘ end+@dbname    
  146. +’ from disk=”’+@bkfile+””    
  147. +’ with file=’+cast(@filenumber as varchar)    
  148. +case when @overexist=1 and @retype in(‘DB’,’DBNOR’) then ‘,replace’ else ” end 
      
  149. +case @retype when ‘DBNOR’ then ‘,NORECOVERY’ else ‘,RECOVERY’ end    
  150. print @sql    
  151. –增添移动逻辑文件的管理    
  152. if @retype=’DB’ or @retype=’DBNOR’    
  153. begin    
  154. –从备份文件中获得逻辑文件名    
  155. declare @lfn nvarchar(128),@tp char(1),@i int 
      
  156.   
  157. –成立一时半刻表,保存获取的音信    
  158. create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))    
  159. –从备份文件中获取音信    
  160. insert into #tb exec(‘restore filelistonly from disk=”’+@bkfile+””)    
  161. declare #f cursor for select ln,tp from #tb    
  162. open #f    
  163. fetch next from #f into @lfn,@tp    
  164. set @i=0    
  165. while @@fetch_status=0    
  166. begin    
  167. select @sql=@sql+’,move ”’+@lfn+”’ to ”’+@dbpath+@dbname+cast(@i as varchar)    
  168. +case @tp when ‘D’ then ‘.mdf”’ else ‘.ldf”’ end    
  169. ,@i=@i+1    
  170. fetch next from #f into @lfn,@tp    
  171. end    
  172. close #f    
  173. deallocate #f    
  174. end    
  175.   
  176. –关闭用户进度管理    
  177. if @overexist=1 and @killuser=1 
      
  178. begin    
  179. declare @spid varchar(20)    
  180. declare #spid cursor for    
  181. select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)    
  182. open #spid    
  183. fetch next from #spid into @spid 
      
  184. while @@fetch_status=0    
  185. begin    
  186. exec(‘kill ‘+@spid)    
  187. fetch next from #spid into @spid 
      
  188. end    
  189. close #spid    
  190. deallocate #spid    
  191. end    
  192.   
  193. –苏醒数据库    
  194. exec(@sql)    
  195.   
  196. go    
  197.   
  198. /*四.–创设作业   
  199.  
  200. */    
  201.   
  202. /*–调用示例   
  203.  
  204. –每月试行的学业   
  205. exec p_createjob @jobname=’mm’,@sql=’select * from syscolumns’,@freqtype=’month’ 
     
  206.  
  207. –每一周施行的功课   
  208. exec p_createjob @jobname=’ww’,@sql=’select * from syscolumns’,@freqtype=’week’ 
     
  209.  
  210. –每天实行的学业   
  211. exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’ 
     
  212.  
  213. –每天试行的功课,每一天隔四钟头重复的作业 
     
  214. exec p_createjob @jobname=’b’,@sql=’select * from syscolumns’,@fsinterval=4 
     
  215.  
  216. –*/    
  217. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)    
  218. drop procedure [dbo].[p_createjob]    
  219. GO    
  220.   
  221. create proc p_createjob    
  222. @jobname varchar(100), –作业名称    
  223. @sql varchar(七千), –要实行的授命    
  224. @dbname sysname=”, –默觉稳妥下的数据库名    
  225. @freqtype varchar(陆)=’day’, –时间周期,month 月,week 周,day 日    
  226. @fsinterval int=壹, –相对于每日的重复次数    
  227. @time int=一九千0 –初始实践时间,对于再度试行的学业,将从0点到二三:陆拾贰分    
  228. as    
  229. if isnull(@dbname,”)=” set @dbname=db_name()    
  230.   
  231. –创制作业    
  232. exec msdb..sp_add_job @job_name=@jobname    
  233.   
  234. –成立作业步骤    
  235. exec msdb..sp_add_jobstep @job_name=@jobname,    
  236. @step_name = ‘数据管理’,    
  237. @subsystem = ‘TSQL’,    
  238. @database_name=@dbname,    
  239. @command = @sql,    
  240. @retry_attempts = ⑤, –重试次数    
  241. @retry_interval = 伍 –重试间隔    
  242.   
  243. –创造调解    
  244. declare @ftype int,@fstype int,@ffactor int    
  245. select @ftype=case @freqtype when ‘day’ then 4    
  246. when ‘week’ then 8    
  247. when ‘month’ then 16 end    
  248. ,@fstype=case @fsinterval when 1 then 0 else 8 end 
      
  249. if @fsinterval<>1 set @time=0    
  250. set @ffactor=case @freqtype when ‘day’ then 0 else 1 end 
      
  251.   
  252. EXEC msdb..sp_add_jobschedule @job_name=@jobname,    
  253. @name = ‘时间安插’,    
  254. @freq_type=@ftype , –每天,8 每周,16 每月    
  255. @freq_interval=1, –重复推行次数    
  256. @freq_subday_type=@fstype, –是还是不是再次推行    
  257. @freq_subday_interval=@fsinterval, –重复周期    
  258. @freq_recurrence_factor=@ffactor,    
  259. @active_start_time=@time –下午17:00:00分执行    
  260.   
  261. go    
  262.   
  263. /*–应用案例–备份方案:   
  264. 整体备份(每种星期陆1回)+差别备份(每一日备份贰遍)+日志备份(每2钟头备份一遍) 
     
  265.  
  266. 调用上面的仓储进程来贯彻   
  267. –*/    
  268.   
  269. declare @sql varchar(8000)    
  270. –完整备份(种种礼拜一一回)    
  271. set @sql=’exec p_backupdb @dbname=”要备份的数据库名”’    
  272. exec p_createjob @jobname=’每一周备份’,@sql,@freqtype=’week’    
  273.   
  274. –差距备份(每一天备份一回)    
  275. set @sql=’exec p_backupdb @dbname=”要备份的数据库名”,@bktype=’DF” 
      
  276. exec p_createjob @jobname=’天天差距备份’,@sql,@freqtype=’day’    
  277.   
  278. –日志备份(每二小时备份二次)    
  279. set @sql=’exec p_backupdb @dbname=”要备份的数据库名”,@bktype=’LOG” 
      
  280. exec p_createjob @jobname=’每贰钟头日志备份’,@sql,@freqtype=’day’,@fsinterval=贰 
      
  281.   
  282. /*–应用案例2   
  283.  
  284. 生产数据核心库:PRODUCE   
  285.  
  286. 备份方案如下:   
  287. 1.设置多少个作业,分别对PRODUCE库进行每一天备份,周周备份,每月备份 
     
  288. 贰.新建四个新库,分别命名字为:每一天备份,周周备份,每月备份 
     
  289. 三.创造七个作业,分别把四个备份库还原到上述的多个新库。 
     
  290.  
  291. 目标:当用户在produce库中有任何的数量丢失时,均能够从上面的多个备份库中程导弹入相应的TABLE数据。 
     
  292. –*/    
  293.   
  294. declare @sql varchar(8000)    
  295.   
  296. –一.创制每月备份和生成月备份数据库的学业,每月每壹天中午1六:37分张开:    
  297. set @sql=’    
  298. declare @path nvarchar(260),@fname nvarchar(100)    
  299. set @fname=”PRODUCE_”+convert(varchar(10),getdate(),112)+”_m.bak”    
  300. set @path=dbo.f_getdbpath(null)+@fname    
  301.   
  302. –备份    
  303. exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname    
  304.   
  305. –根据备份生成每月新库    
  306. exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_月” 
      
  307.   
  308. –为周数据库恢复准备基础数据库    
  309. exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_周”,@retype=”DBNOR”    
  310.   
  311. –为日数据库复苏筹算基础数据库    
  312. exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_日”,@retype=”DBNOR”    
  313. ‘    
  314. exec p_createjob @jobname=’每月备份’,@sql,@freqtype=’month’,@time=16五千 
      
  315.   
  316. –二.创设周周差别备份和浮动周备份数据库的功课,每一周一晚上壹7:00分举行:    
  317. set @sql=’    
  318. declare @path nvarchar(260),@fname nvarchar(100)    
  319. set @fname=”PRODUCE_”+convert(varchar(10),getdate(),112)+”_w.bak”    
  320. set @path=dbo.f_getdbpath(null)+@fname    
  321.   
  322. –差别备份    
  323. exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname,@bktype=”DF”    
  324.   
  325. –差距苏醒周数据库    
  326. exec p_backupdb @bkfile=@path,@dbname=”PRODUCE_周”,@retype=”DF”    
  327. ‘    
  328. exec p_createjob @jobname=’每一周差距备份’,@sql,@freqtype=’week’,@time=一九〇〇00 
      
  329.   
  330. –三.成立天天日志备份和生成日备份数据库的课业,每星期日清晨一七:一四分进行:    
  331. set @sql=’    
  332. declare @path nvarchar(260),@fname nvarchar(100)    
  333. set @fname=”PRODUCE_”+convert(varchar(10),getdate(),112)+”_l.bak”    
  334. set @path=dbo.f_getdbpath(null)+@fname    
  335.   
  336. –日志备份    
  337. exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname,@bktype=”LOG”    
  338.   
  339. –日志恢复生机日数据库    
  340. exec p_backupdb @bkfile=@path,@dbname=”PRODUCE_日”,@retype=”LOG”    
  341. ‘    
  342. exec p_createjob @jobname=’周周差距备份’,@sql,@freqtype=’day’,@time=171500  

    那种办法认为实现起来也不利,作者是选择那种措施来成功数据库备份还原的功用的。
    需求建议下边这么些囤积进程是在互连网检索到的。谢谢代码的提供者。在此转载利用T-SQL语句,达成数据库的备份与还原的效力呈现了SQL Server中的多少个知识点:
    壹. 得到SQL Server服务器上的暗中同意目录
    二. 备份SQL语句的利用
    三. 过来SQL语句的采取,同时思量了挟持复苏时关闭别的用户进度的拍卖
    4. 功课创造SQL语句的使用
    /1.–获得数据库的文件目录
    @dbname 钦赐要赢得目录的数据库名
    若是钦定的多寡不存在,再次来到安装SQL时设置的默许数据目录
    要是钦定NULL,则赶回暗中同意的SQL备份目录名
    /
    /–调用示例
    select 数据库文件目录=dbo.f_getdbpath(‘tempdb’)
    ,[暗中同意SQL SE昂CoraVE索罗德数据目录]=dbo.f_getdbpath(”)
    ,[暗中同意SQL SE兰德大切诺基VE科雷傲备份目录]=dbo.f_getdbpath(null)
    /
    if exists (select from dbo.sysobjects where id = object_id(N'[dbo].[f_getdbpath]’) and xtype in (N’FN’, N’IF’, N’TF’))
    drop function [dbo].[f_getdbpath]
    GO
    create function f_getdbpath(@dbname sysname)
    returns nvarchar(260)
    as
    begin
    declare @re nvarchar(260)
    if @dbname is null or db_id(@dbname) is null
    select @re=rtrim(reverse(filename)) from master..sysdatabases where name=’master’
    else
    select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname
    if @dbname is null
    set @re=reverse(substring(@re,charindex(‘\’,@re)+5,260))+’BACKUP’
    else
    set @re=reverse(substring(@re,charindex(‘\’,@re),260))
    return(@re)
    end
    go
    /
    贰.–备份数据库
    /
    /
    –调用示例
    –备份当前数据库
    exec pbackupdb @bkpath=’c:\’,@bkfname=’db\DATE_db.bak’
    –差别备份当前数据库
    exec pbackupdb @bkpath=’c:\’,@bkfname=’db\DATE_df.bak’,@bktype=’DF’
    –备份当前数据库日志
    exec pbackupdb @bkpath=’c:\’,@bkfname=’db\DATE_log.bak’,@bktype=’LOG’
    /
    if exists (select
    from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
    drop procedure [dbo].[p_backupdb]
    GO
    create proc p_backupdb
    @dbname sysname=”, –要备份的数据库名称,不点名则备份当前数据库
    @bkpath nvarchar(260)=”, –备份文件的寄放目录,不钦点则应用SQL暗中同意的备份目录
    @bkfname nvarchar(260)=”, –备份文件名,文件名中能够用\DBNAME\代表数量库名,\DATE\代表日期,\TIME\代表时间
    @bktype nvarchar(10)=’DB’, –备份类型:’DB’备份数据库,’DF’ 差距备份,’LOG’ 日志备份
    @appendfile bit=一 –追加/覆盖备份文件
    as
    declare @sql varchar(九千)
    if isnull(@dbname,”)=” set @dbname=db_name()
    if isnull(@bkpath,”)=” set @bkpath=dbo.fgetdbpath(null)
    if isnull(@bkfname,”)=” set @bkfname=’\DBNAME\
    \DATE_\TIME.BAK’
    set @bkfname=replace(replace(replace(@bkfname,’\DBNAME\’,@dbname)
    ,’\DATE\’,convert(varchar,getdate(),112))
    ,’\TIME\’,replace(convert(varchar,getdate(),108),’:’,”))
    set @sql=’backup ‘+case @bktype when ‘LOG’ then ‘log ‘ else ‘database ‘ end +@dbname
    +’ to disk=”’+@bkpath+@bkfname
    +”’ with ‘+case @bktype when ‘DF’ then ‘DIFFERENTIAL,’ else ” end
    +case @appendfile when 1 then ‘NOINIT’ else ‘INIT’ end
    print @sql
    exec(@sql)
    go
    /三.–复苏数据库/
    /–调用示例
    –完整过来数据库
    exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’
    –差距备份苏醒exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’DBNOR’
    exec p_backupdb @bkfile=’c:\db_20031015_df.bak’,@dbname=’db’,@retype=’DF’
    –日志备份恢复exec p_RestoreDb @bkfile=’c:\db_20031015_db.bak’,@dbname=’db’,@retype=’DBNOR’
    exec p_backupdb @bkfile=’c:\db_20031015_log.bak’,@dbname=’db’,@retype=’LOG’
    /
    if exists (select from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
    drop procedure [dbo].[p_RestoreDb]
    GO
    create proc p_RestoreDb
    @bkfile nvarchar(1000), –定义要过来的备份文件名
    @dbname sysname=”, –定义复苏后的数码库名,默以为备份的文书名
    @dbpath nvarchar(260)=”, –复苏后的数据仓库储存放目录,不钦赐则为SQL的私下认可数据目录
    @retype nvarchar(拾)=’DB’, –复苏项目:’DB’完事恢复生机数据库,’DBNO哈弗’ 为距离复苏,日志恢复生机实行总体过来,’DF’ 差距备份的回复,’LOG’ 日志苏醒@filenumber int=1, –苏醒的文件号
    @overexist bit=一, –是不是覆盖已经存在的数据库,仅@retype为
    @killuser bit=1 –是还是不是关闭用户使用进程,仅@overexist=一时卓有成效as
    declare @sql varchar(九千)
    –得到苏醒后的数据库名
    if isnull(@dbname,”)=”
    select @sql=reverse(@bkfile)
    ,@sql=case when charindex(‘.’,@sql)=0 then @sql
    else substring(@sql,charindex(‘.’,@sql)+一,1000) end
    ,@sql=case when charindex(‘\’,@sql)=0 then @sql
    else left(@sql,charindex(‘\’,@sql)-一) end
    ,@dbname=reverse(@sql)
    –得到回复后的数据仓库储存放目录
    if isnull(@dbpath,”)=” set @dbpath=dbo.f_getdbpath(”)
    –生成数据库恢复生机语句
    set @sql=’restore ‘+case @retype when ‘LOG’ then ‘log ‘ else ‘database ‘ end+@dbname
    +’ from disk=”’+@bkfile+””
    +’ with file=’+cast(@filenumber as varchar)
    +case when @overexist=1 and @retype in(‘DB’,’DBNOLacrosse’) then ‘,replace’ else ” end
    +case @retype when ‘DBNO大切诺基’ then ‘,NORECOVE中华VY’ else ‘,RECOVEMuranoY’ end
    print @sql
    –增添移动逻辑文件的拍卖
    if @retype=’DB’ or @retype=’DBNO揽胜’
    begin
    –从备份文件中拿走逻辑文件名
    declare @lfn nvarchar(128),@tp char(一),@i int
    –成立一时半刻表,保存获取的音信create table #tb(ln nvarchar(12八),pn nvarchar(260),tp char(1),fgn nvarchar(12八),sz numeric(20,0),Msz numeric(20,0))
    –从备份文件中获取音信insert into #tb exec(‘restore filelistonly from disk=”’+@bkfile+””)
    declare #f cursor for select ln,tp from #tb
    open #f
    fetch next from #f into @lfn,@tp
    set @i=0
    while @@fetch_status=0
    begin
    select @sql=@sql+’,move ”’+@lfn+”’ to ”’+@dbpath+@dbname+cast(@i as varchar)
    +case @tp when ‘D’ then ‘.mdf”’ else ‘.ldf”’ end
    ,@i=@i+1fetch next from #f into @lfn,@tp
    end
    close #f
    deallocate #f
    end
    –关闭用户进度管理if @overexist=一 and @killuser=1begin
    declare @spid varchar(20)
    declare #spid cursor for
    select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
    open #spid
    fetch next from #spid into @spid
    while @@fetch_status=0
    begin
    exec(‘kill ‘+@spid)
    fetch next from #spid into @spid
    end
    close #spid
    deallocate #spid
    end
    –复苏数据库
    exec(@sql)
    go
    /
    四.–创造作业
    /
    /
    –调用示例
    –每月实施的学业
    exec p_createjob @jobname=’mm’,@sql=’select from syscolumns’,@freqtype=’month’
    –每一周推行的作业
    exec p_createjob @jobname=’ww’,@sql=’select
    from syscolumns’,@freqtype=’week’
    –天天实行的功课
    exec p_createjob @jobname=’a’,@sql=’select from syscolumns’
    –天天实践的学业,每日隔四钟头重复的课业
    exec p_createjob @jobname=’b’,@sql=’select
    from syscolumns’,@fsinterval=4
    /
    if exists (select
    from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
    drop procedure [dbo].[p_createjob]
    GO
    create proc p_createjob
    @jobname varchar(100), –作业名称
    @sql varchar(7000), –要施行的通令
    @dbname sysname=”, –默感到近来的数据库名
    @freqtype varchar(6)=’day’, –时间周期,month 月,week 周,day 日
    @fsinterval int=1, –相对于每一天的重新次数
    @time int=1九千0 –发轫奉行时间,对于再一次实施的学业,将从0点到二3:5九分as
    if isnull(@dbname,”)=” set @dbname=db_name()
    –创造作业
    exec msdb..sp_add_job @job_name=@jobname
    –创造作业步骤
    exec msdb..sp_add_jobstep @job_name=@jobname,
    @step_name = ‘数据管理’,
    @subsystem = ‘TSQL’,
    @database_name=@dbname,
    @command = @sql,
    @retry_attempts = 5, –重试次数
    @retry_interval = 5 –重试间隔
    –创造调节declare @ftype int,@fstype int,@ffactor int
    select @ftype=case @freqtype when ‘day’ then 肆when ‘week’ then 八when ‘month’ then 1六 end
    ,@fstype=case @fsinterval when 一 then 0 else 8 end
    if @fsinterval<>1 set @time=0
    set @ffactor=case @freqtype when ‘day’ then 0 else 1 end
    EXEC msdb..sp_add_jobschedule @job_name=@jobname,
    @name = ‘时间安排’,
    @freq_type=@ftype , –每天,8 每周,16 每月
    @freq_interval=1, –重复试行次数
    @freq_subday_type=@fstype, –是不是再一次实践@freq_subday_interval=@fsinterval, –重复周期
    @freq_recurrence_factor=@ffactor,
    @active_start_time=@time –下午17:00:00分执行
    go
    /–应用案例–备份方案:
    完整备份(每一个星期日三回)+差距备份(每一日备份三遍)+日志备份(每二钟头备份3遍)
    调用地点的积攒进度来达成–
    /
    declare @sql varchar(柒仟)
    –完整备份(每一种周伍3遍)
    set @sql=’exec p_backupdb @dbname=”要备份的数据库名”’
    exec p_createjob @jobname=’每一周备份’,@sql,@freqtype=’week’
    –差别备份(每一天备份3次)
    set @sql=’exec p_backupdb @dbname=”要备份的数据库名”,@bktype=’DF”
    exec p_createjob @jobname=’每日差距备份’,@sql,@freqtype=’day’
    –日志备份(每二时辰备份一回)
    set @sql=’exec p_backupdb @dbname=”要备份的数据库名”,@bktype=’LOG”
    exec pcreatejob @jobname=’每二钟头日志备份’,@sql,@freqtype=’day’,@fsinterval=2
    /–应用案例二生育数量大旨库:PRODUCE
    备份方案如下:
    一.安装七个作业,分别对PRODUCE库实行每一天备份,周周备份,每月备份
    贰.新建八个新库,分别命名为:天天备份,周周备份,每月备份
    三.创设四个作业,分别把八个备份库还原到以上的多少个新库。
    目标:当用户在produce库中有任何的多少丢失时,均能够从上边包车型地铁四个备份库中程导弹入相应的TABLE数据。
    /
    declare @sql varchar(7000)
    –1.创设每月备份和生成月备份数据库的作业,每月每日晚上1陆:三十几分实行:
    set @sql=’
    declare @path nvarchar(260),@fname nvarchar(100)
    set @fname=”PRODUCE
    ”+convert(varchar(10),getdate(),112)+”_m.bak”
    set @path=dbo.f_getdbpath(null)+@fname
    –备份
    exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname
    –依据备份生成每月新库
    exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_月”
    –为周数据库复苏希图基础数据库
    exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_周”,@retype=”DBNOENCORE”
    –为日数据库恢复生机希图基础数据库
    exec p_RestoreDb @bkfile=@path,@dbname=”PRODUCE_日”,@retype=”DBNOR”

    exec pcreatejob @jobname=’每月备份’,@sql,@freqtype=’month’,@time=16四千–二.创设每一周差别备份和转移周备份数据库的学业,每礼拜5上午一7:00分进行:
    set @sql=’
    declare @path nvarchar(260),@fname nvarchar(100)
    set @fname=”PRODUCE
    ”+convert(varchar(10),getdate(),112)+”_w.bak”
    set @path=dbo.f_getdbpath(null)+@fname
    –差距备份
    exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname,@bktype=”DF”
    –差别复苏周数据库
    exec p_backupdb @bkfile=@path,@dbname=”PRODUCE_周”,@retype=”DF”

    exec pcreatejob @jobname=’每一周差别备份’,@sql,@freqtype=’week’,@time=一柒仟0
    –3.起家每一日日志备份和生成日备份数据库的课业,周周2早晨一柒:拾伍分进行:
    set @sql=’
    declare @path nvarchar(260),@fname nvarchar(100)
    set @fname=”PRODUCE
    ”+convert(varchar(10),getdate(),112)+”_l.bak”
    set @path=dbo.f_getdbpath(null)+@fname
    –日志备份
    exec p_backupdb @dbname=”PRODUCE”,@bkfname=@fname,@bktype=”LOG”
    –日志复苏日数据库
    exec p_backupdb @bkfile=@path,@dbname=”PRODUCE_日”,@retype=”LOG”

    exec p_createjob @jobname=’每一周差别备份’,@sql,@freqtype=’day’,@time=171500

 

exec master..xp_cmdshell ‘if not exist E:\DATABACKUP (md
E:\DATABACKUP)’–若是E盘根目录未有DATABACKUP文件夹,则新建此文件夹
SELECT @filename=@dbname+replace(replace(replace(CONVERT(varchar(16),
getdate(), 120 ),’-‘,”),’ ‘,’-‘),’:’,”) 
SET @path=’E:\DATABACKUP\’+@filename
BACKUP DATABASE @dbname TO DISK=@path

  1. 差异备份:

    — Differential
    declare @filename varchar(1024),

         @file_dev    varchar(300)
    

    declare @path varchar(1024)
    set @path = N’F:\Backup\Plan2\’;

    declare @extension_name varchar(16)
    set @extension_name = N’bak’;

    set @filename = convert(varchar(1024), getdate(), 120)
    set @filename = replace(@filename, ‘:’, ”)
    set @filename = replace(@filename, ‘-‘, ”)
    set @filename = replace(@filename, ‘ ‘, ”)

    set @filename = @filename + ‘_’ + convert (varchar(3), datepart(ms, getdate())) + N’.’ + @extension_name

    — start backup, COMPRESSION is the parameter
    set @file_dev = @path + ‘SmartDevDifferential‘ + @filename
    backup database [SmartDev] to disk = @file_dev
    with differential, noformat, init, name = N’SmartDev-Database Differential backup’

    go

—-压缩数据库备份,删除原备份文件

 

set @cmd2=’C:\PROGRA~1\WinRAR\winrar.exe a  -ibck
E:\DATABACKUP\’+@filename+’.rar E:\DATABACKUP\’+@filename
exec master..xp_cmdshell @cmd二—-压缩数据库
set @cmd3=’DEL E:\DATABACKUP\’+@filename
exec master..xp_cmdshell @cmd叁—-删除原备份文件(只保留压缩包)

以上两段能够分别创设多个SQL
Job,比方完整备份的作业能够定在每周二凌清晨运动行,实行成功后删掉从前的备份文件(代码中有删除的言辞),差别备份的功课定在周一到礼拜陆每一天凌晨运作。

go

 

 

–一.退换本进程中目标数据库名成你要备分的数据库名
二.复制此行(可含此行)以上内空到查询分析器中实施就能够
–叁.数据库备份文件存放在E:\DATABACKUP文件夹中,文件名称为数据库名3人年两位月两位日-两位时两位分.rar

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website