NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: 如何利用SQL语言实现数据库的备份/恢复的功能

如何利用SQL语言实现数据库的备份/恢复的功能

利用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_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


/*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_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=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(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

 

/*3.--恢复数据库--*/

/*--调用示例
--完整恢复数据库
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(10)= 'DB ',   --恢复类型: 'DB '完事恢复数据库, 'DBNOR '   为差异恢复,日志恢复进行完整恢复, 'DF '   差异备份的恢复, 'LOG '   日志恢复
@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,charindex( '. ',@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   '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 '   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),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+1
    fetch   next   from   #f   into   @lfn,@tp
  end
  close   #f
  deallocate   #f
end

--关闭用户进程处理
if   @overexist=1   and   @killuser=1
begin
  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

/*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_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, ' ')= ' '   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   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_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

这篇文章对你多有用?

相关文章

article 如何实现数据的恢复和彻底删除
现在网络中有很多数据恢复软件,EasyRecovery就是...

(No rating)  9-22-2014    Views: 682   
article 利用MSSQL复制技术 实现数据同步
利用MSSQL复制技术 实现数据同步 (2006-08-15...

(No rating)  4-21-2010    Views: 1204   
article Rsync+sersync 实现数据实时同步
Rsync+sersync 实现数据实时同步

(No rating)  3-6-2021    Views: 43   

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited