sqlserver数据库的备份与恢复sql实现.docx

《sqlserver数据库的备份与恢复sql实现.docx》由会员分享，可在线阅读，更多相关《sqlserver数据库的备份与恢复sql实现.docx（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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数据目录=dbo.f_getdbpath(),默认SQL

2、 SERVER备份目录=dbo.f_getdbpath(null)-*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_getdbpath) and xtype in (NFN, NIF, NTF)drop function dbo.f_getdbpathGOcreate function f_getdbpath(dbname sysname)returns nvarchar(260)asbegindeclare re nvarchar(260)if dbname is null or db_id(dbna

3、me) is nullselect re=rtrim(reverse(filename) from master.sysdatabases where name=masterelseselect re=rtrim(reverse(filename) from master.sysdatabases where name=dbnameif dbname is nullset re=reverse(substring(re,charindex(/,re)+5,260)+BACKUPelseset re=reverse(substring(re,charindex(/,re),260)return(

4、re)endgo/*2.-备份数据库*/*-调用示例-备份当前数据库exec p_backupdb bkpath=c:/,bkfname=db_/DATE/_db.bak-差异备份当前数据库exec p_backupdb bkpath=c:/,bkfname=db_/DATE/_df.bak,bktype=DF-备份当前数据库日志exec p_backupdb bkpath=c:/,bkfname=db_/DATE/_log.bak,bktype=LOG-*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_

5、backupdb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_backupdbGOcreate proc p_backupdbdbname sysname=, -要备份的数据库名称,不指定则备份当前数据库bkpath nvarchar(260)=, -备份文件的存放目录,不指定则使用SQL默认的备份目录bkfname nvarchar(260)=, -备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间bktype nvarchar(10)=DB, -备份类型:DB备

6、份数据库,DF 差异备份,LOG 日志备份appendfile bit=1 -追加/覆盖备份文件asdeclare 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/.BAKset bkfname=replace(replace(replace(bkfname,/DBNAME/,dbname),/DATE/,conver

7、t(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 endprint sqlexec(sql)go/*3.-恢

8、复数据库*/*-调用示例-完整恢复数据库exec p_RestoreDb bkfile=c:/db_db.bak,dbname=db-差异备份恢复exec p_RestoreDb bkfile=c:/db_db.bak,dbname=db,retype=DBNORexec p_backupdb bkfile=c:/db_df.bak,dbname=db,retype=DF-日志备份恢复exec p_RestoreDb bkfile=c:/db_db.bak,dbname=db,retype=DBNORexec p_backupdb bkfile=c:/db_log.bak,dbname=db,

9、retype=LOG-*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_RestoreDb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_RestoreDbGOcreate proc p_RestoreDbbkfile nvarchar(1000), -定义要恢复的备份文件名dbname sysname=, -定义恢复后的数据库名,默认为备份的文件名dbpath nvarchar(260)=, -恢复后的数据库存放目录,不指定

10、则为SQL的默认数据目录retype nvarchar(10)=DB, -恢复类型:DB完事恢复数据库,DBNOR 为差异恢复,日志恢复进行完整恢复,DF 差异备份的恢复,LOG 日志恢复filenumber int=1, -恢复的文件号overexist bit=1, -是否覆盖已经存在的数据库,仅retype为killuser bit=1 -是否关闭用户使用进程,仅overexist=1时有效asdeclare sql varchar(8000)-得到恢复后的数据库名if isnull(dbname,)=select sql=reverse(bkfile),sql=case when ch

11、arindex(.,sql)=0 then sqlelse substring(sql,charindex(.,sql)+1,1000) end,sql=case when charindex(/,sql)=0 then sqlelse left(sql,charindex(/,sql)-1) end,dbname=reverse(sql)-得到恢复后的数据库存放目录if isnull(dbpath,)= set dbpath=dbo.f_getdbpath()-生成数据库恢复语句set sql=restore +case retype when LOG then log else datab

12、ase 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 when DBNOR then ,NORECOVERY else ,RECOVERY endprint sql-添加移动逻辑文件的处理if retype=DB or retype=DBNORbegin-从备份文件中获取逻辑文件名declare lfn nvarchar(128),tp ch

13、ar(1),i int-创建临时表,保存获取的信息create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),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 #tbopen #ffetch next from #f into lfn,tpset i=0wh

14、ile fetch_status=0beginselect 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,tpendclose #fdeallocate #fend-关闭用户进程处理if overexist=1 and killuser=1begindeclare spid varchar(20)declare #spid cursor forselect spid=cast(spid

15、 as varchar(20) from master.sysprocesses where dbid=db_id(dbname)open #spidfetch next from #spid into spidwhile fetch_status=0beginexec(kill +spid)fetch next from #spid into spidendclose #spiddeallocate #spidend-恢复数据库exec(sql)go/*4.-创建作业*/*-调用示例-每月执行的作业exec p_createjob jobname=mm,sql=select * from s

16、yscolumns,freqtype=month-每周执行的作业exec p_createjob jobname=ww,sql=select * from syscolumns,freqtype=week-每日执行的作业exec p_createjob jobname=a,sql=select * from syscolumns-每日执行的作业,每天隔4小时重复的作业exec p_createjob jobname=b,sql=select * from syscolumns,fsinterval=4-*/if exists (select * from dbo.sysobjects wher

17、e id = object_id(Ndbo.p_createjob) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_createjobGOcreate proc p_createjobjobname varchar(100), -作业名称sql varchar(8000), -要执行的命令dbname sysname=, -默认为当前的数据库名freqtype varchar(6)=day, -时间周期,month 月,week 周,day 日fsinterval int=1, -相对于每日的重复次数time int

18、= -开始执行时间,对于重复执行的作业,将从0点到23:59分asif 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

19、 int,fstype int,ffactor intselect ftype=case freqtype when day then 4when week then 8when month then 16 end,fstype=case fsinterval when 1 then 0 else 8 endif fsinterval1 set time=0set ffactor=case freqtype when day then 0 else 1 endEXEC msdb.sp_add_jobschedule job_name=jobname,name = 时间安排,freq_type=

20、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小时备份一次）调用上面的存储过程来实现-*/declare sql varchar(8000)-完整备份（每个星期天一次）set sq

21、l=exec p_backupdb dbname=要备份的数据库名exec p_createjob jobname=每周备份,sql,freqtype=week-差异备份（每天备份一次）set sql=exec p_backupdb dbname=要备份的数据库名,bktype=DFexec p_createjob jobname=每天差异备份,sql,freqtype=day-日志备份（每2小时备份一次）set sql=exec p_backupdb dbname=要备份的数据库名,bktype=LOGexec p_createjob jobname=每2小时日志备份,sql,freqtyp

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

23、nvarchar(100)set fname=PRODUCE_+convert(varchar(10),getdate(),112)+_m.bakset 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=DBNOR-为日数据库恢复准备基础数据库

24、exec p_RestoreDb bkfile=path,dbname=PRODUCE_日,retype=DBNORexec p_createjob jobname=每月备份,sql,freqtype=month,time=-2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行:set sql=declare path nvarchar(260),fname nvarchar(100)set fname=PRODUCE_+convert(varchar(10),getdate(),112)+_w.bakset path=dbo.f_getdbpath(null)+fname

25、-差异备份exec p_backupdb dbname=PRODUCE,bkfname=fname,bktype=DF-差异恢复周数据库exec p_backupdb bkfile=path,dbname=PRODUCE_周,retype=DFexec p_createjob jobname=每周差异备份,sql,freqtype=week,time=-3.建立每日日志备份和生成日备份数据库的作业,每周日下午17:15分进行:set sql=declare path nvarchar(260),fname nvarchar(100)set fname=PRODUCE_+convert(varchar(10),getdate(),112)+_l.bakset path=dbo.f_getdbpath(null)+fname-日志备份exec p_backupdb dbname=PRODUCE,bkfname=fname,bktype=LOG-日志恢复日数据库exec p_backupdb bkfile=path,dbname=PRODUCE_日,retype=LOGexec p_createjob jobname=每周差异备份,sql,freqtype=day,time=