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

利用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 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_getdbpathGO create function f_getdbpath(dbname sysname)returns nvarchar(260)asbegindeclare re nvarchar(260)if dbname is null or db_id(dbname) is nullselect re=rtrim(reverse(filename) from master.sysdatabases where name='master'elseselect re=rtrim(reverse(filename) from master.sysdatabases where name=dbname if dbname is nullset re=reverse(substring(re,charindex('/',re)+5,260)+'BACKUP'elseset re=reverse(substring(re,charindex('/',re),260)return(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(N'dbo.p_backupdb') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure dbo.p_backupdbGO create proc p_backupdbdbname sysname='', -要备份的数据库名称,不指定则备份当前数据库bkpath nvarchar(260)='', -备份文件的存放目录,不指定则使用SQL默认的备份目录bkfname nvarchar(260)='', -备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间bktype nvarchar(10)='DB', -备份类型:'DB'备份数据库,'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/.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' endprint sqlexec(sql)go   /*3.-恢复数据库 */ /*-调用示例-完整恢复数据库exec p_RestoreDb bkfile='c:/db_db.bak',dbname='db' -差异备份恢复exec p_RestoreDb bkfile='c:/db_db.bak',dbname='db',retype='DBNOR'exec p_backupdb bkfile='c:/db_df.bak',dbname='db',retype='DF' -日志备份恢复exec p_RestoreDb bkfile='c:/db_db.bak',dbname='db',retype='DBNOR'exec p_backupdb bkfile='c:/db_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_RestoreDbGO create proc p_RestoreDbbkfile nvarchar(1000), -定义要恢复的备份文件名dbname sysname='', -定义恢复后的数据库名,默认为备份的文件名dbpath nvarchar(260)='', -恢复后的数据库存放目录,不指定则为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 charindex('.',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 '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 when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' endprint 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),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=0while 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 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 syscolumns',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 where id = object_id(N'dbo.p_createjob') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure dbo.p_createjobGO create proc p_createjobjobname varchar(100), -作业名称sql varchar(8000), -要执行的命令dbname sysname='', -默认为当前的数据库名freqtype varchar(6)='day', -时间周期,month 月,week 周,day 日fsinterval int=1, -相对于每日的重复次数time int= -开始执行时间,对于重复执行的作业,将从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 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 fsinterval<>1 set time=0set 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 /*-应用案例-备份方案:完整备份（每个星期天一次）+差异备份（每天备份一次）+日志备份（每2小时备份一次） 调用上面的存储过程来实现-*/ 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''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.设置三个作业,分别对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_''+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=''DBNOR'' -为日数据库恢复准备基础数据库exec p_RestoreDb bkfile=path,dbname=''PRODUCE_日'',retype=''DBNOR'''exec 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.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 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.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=