T-SQL语句实现数据库的备份与还原的功能_第1页
T-SQL语句实现数据库的备份与还原的功能_第2页
T-SQL语句实现数据库的备份与还原的功能_第3页
T-SQL语句实现数据库的备份与还原的功能_第4页
T-SQL语句实现数据库的备份与还原的功能_第5页
已阅读5页,还剩1页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、利用T-SQL语句,实现数据库的备份与还原的功能 体现了SQL Server中的四个知识点: 1 获取SQL Server服务器上的默认目录 2 备份SQL语句的使用 3 恢复SQL语句的使用,同时考虑了强制恢复时关闭其他用户进程的处理 4 作业创建SQL语句的使用 /*1.-得到数据库的文件目录 dbname 指定要取得目录的数据库名 如果指定的数据不存在,返回安装SQL时设置的默认数据目录 如果指定NULL,则返回默认的SQL备份目录名 */ /*-调用示例 select 数据库文件目录=dbo.f_getdbpath('tempdb' ,默认SQL SERVER数据目录=

2、dbo.f_getdbpath('' ,默认SQL SERVER备份目录=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

3、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

4、,charindex('',re+5,260+'BACKUP' else set re=reverse(substring(re,charindex('',re,260 return(re end go /*2.-备份数据库 */ /*-调用示例 -备份当前数据库 exec p_backupdb bkpath='c:',bkfname='db_DATE_db.bak' -差异备份当前数据库 exec p_backupdb bkpath='c:',bkfname='db_DATE_df.bak

5、',bktype='DF' -备份当前数据库日志 exec p_backupdb 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 G

6、O create proc p_backupdb dbname sysname='', -要备份的数据库名称,不指定则备份当前数据库 bkpath nvarchar(260='', -备份文件的存放目录,不指定则使用SQL默认的备份目录 bkfname nvarchar(260='', -备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间 bktype nvarchar(10='DB', -备份类型:'DB'备份数据库,'DF' 差异备份,'LOG'

7、; 日志备份 appendfile bit=1 -追加/覆盖备份文件 as declare sql varchar(8000 if isnull(dbname,''='' set dbname=db_name( if isnull(bkpath,''='' set bkpath=dbo.f_getdbpath(null if isnull(bkfname,''='' set bkfname='DBNAME_DATE_TIME.BAK' set bkfname=replace(repl

8、ace(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='&

9、#39;'+bkpath+bkfname +''' with '+case bktype when 'DF' then 'DIFFERENTIAL,' else '' end +cas e appendfile when 1 then 'NOINIT' else 'INIT' end print sql exec(sql go /*3.-恢复数据库 */ /*-调用示例 -完整恢复数据库 exec p_RestoreDb bkfile='c:db_20031015_d

10、b.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

11、='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_Rest

12、oreDb GO create proc p_RestoreDb bkfile nvarchar(1000, -定义要恢复的备份文件名 dbname sysname='', -定义恢复后的数据库名,默认为备份的文件名 dbpath nvarchar(260='', -恢复后的数据库存放目录,不指定则为SQL的默认数据目录 retype nvarchar(10='DB', -恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG&

13、#39; 日志恢复 filenumber int=1, -恢复的文件号 overexist bit=1, -是否覆盖已经存在的数据库,仅retype为 killuser bit=1 -是否关闭用户使用进程,仅overexist=1时有效 as declare sql varchar(8000 -得到恢复后的数据库名 if isnull(dbname,''='' select sql=reverse(bkfile ,sql=case when charindex('.',sql=0 then sql else substring(sql,chari

14、ndex('.',sql+1,1000 end ,sql=case when charindex('',sql=0 then sql else left(sql,charindex('',sql-1 end ,dbname=reverse(sql -得到恢复后的数据库存放目录 if isnull(dbpath,''='' set dbpath=dbo.f_getdbpath('' -生成数据库恢复语句 set sql='restore '+case retype when '

15、LOG' then 'log ' else 'database ' end+dbname +' from disk='''+bkfile+'''' +' with file='+cast(filenumber as varchar +case when overexist=1 and retype in('DB','DBNOR' then ',replace' else '' end +case retype w

16、hen 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end print sql -添加移动逻辑文件的处理 if retype='DB' or retype='DBNOR' begin -从备份文件中获取逻辑文件名 declare lfn nvarchar(128,tp char(1,i int -创建临时表,保存获取的信息 create table #tb(ln nvarchar(128,pn nvarchar(260,tp char(1,fgn nvarchar(128

17、,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 ''

18、;'+lfn+''' to '''+dbpath+dbname+cast(i as varchar +case tp when 'D' then '.mdf''' else '.ldf''' end ,i=i+1 fetch next from #f into lfn,tp end close #f deallocate #f end -关闭用户进程处理 if overexist=1 and killuser=1 begin declare spid varc

19、har(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

20、 /*4.-创建作业 */ /*-调用示例 -每月执行的作业 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='se

21、lect * from syscolumns' -每日执行的作业,每天隔4小时重复的作业 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

22、_createjob GO create proc p_createjob jobname varchar(100, -作业名称 sql varchar(8000, -要执行的命令 dbname sysname='', -默认为当前的数据库名 freqtype varchar(6='day', -时间周期,month 月,week 周,day 日 fsinterval int=1, -相对于每日的重复次数 time int=170000 -开始执行时间,对于重复执行的作业,将从0点到23:59分 as if isnull(dbname,''=&#

23、39;' 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 i

24、nt,ffactor int select ftype=case freqtype when 'day' then 4 when 'week' then 8 when 'month' then 16 end ,fstype=case fsinterval when 1 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_jobschedu

25、le 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 /*-应用案例-备份方案: 完整备份(每个星期天一次)+差异备份(每天备份一次)+日志备份(每2小时备份一

26、次) 调用上面的存储过程来实现 -*/ declare sql varchar(8000 -完整备份(每个星期天一次) set sql='exec p_backupdb dbname=''要备份的数据库名''' exec p_createjob jobname='每周备份',sql,freqtype='week' -差异备份(每天备份一次) set sql='exec p_backupdb dbname=''要备份的数据库名'',bktype='DF''

27、; exec p_createjob jobname=' 每天差异备份',sql,freqtype='day' -日志备份(每2小时备份一次) set sql='exec p_backupdb dbname=''要备份的数据库名'',bktype='LOG'' exec p_createjob jobname='每2小时日志备份',sql,freqtype='day',fsinterval=2 /*-应用案例2 生产数据核心库:PRODUCE 备份方案如下: 1.设置

28、三个作业,分别对PRODUCE库进行每日备份,每周备份,每月备份 2.新建三个新库,分别命名为:每日备份,每周备份,每月备份 3.建立三个作业,分别把三个备份库还原到以上的三个新库。 目的:当用户在produce库中有任何的数据丢失时,均可以从上面的三个备份库中导入相应的TABLE数据。 -*/ declare sql varchar(8000 -1.建立每月备份和生成月备份数据库的作业,每月每1天下午16:40分进行: set sql=' declare path nvarchar(260,fname nvarchar(100 set fname=''PRODUCE_

29、''+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,db

30、name=''PRODUCE_周'',retype=''DBNOR'' -为日数据库恢复准备基础数据库 exec p_RestoreDb bkfile=path,dbname=''PRODUCE_日'',retype=''DBNOR'' ' exec p_createjob jobname='每月备份',sql,freqtype='month',time=164000 -2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行: set sql=

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论