邹建:备份还原数据库的通用存储过程

注意，下面备份还原都是用存储过程实现！
 
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
 
/**/ /*--备份数据库的通用存储过程
 
--邹建 2003.10--*/
 
/**/ /*--调用示例
 
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/DATE/_db.bak'
存储过程实现备份和还原数据库：
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
 
/*--备份数据库的通用存储过程
 
--邹建 2003.10--*/
 
/**/ /*--调用示例
 
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/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'
--*/
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 ,    -- 追加/覆盖备份文件
@password   nvarchar ( 20 ) = ''   -- 为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
  declare   @sql   varchar ( 8000 )
  if   isnull ( @dbname , '' ) = ''   set   @dbname = db_name ()
  if   isnull ( @bkpath , '' ) = ''
  begin
   select   @bkpath = rtrim ( reverse (filename))  from  master..sysfiles  where  name = ' master '
   select   @bkpath = substring ( @bkpath , charindex ( ' / ' , @bkpath ) + 1 , 4000 )
   , @bkpath = reverse ( substring ( @bkpath , charindex ( ' / ' , @bkpath ), 4000 )) + ' BACKUP/ '
  end
  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
   + case   isnull ( @password , '' )  when   ''   then   ''   else   ' ,PASSWORD= ''' + @password + ''''   end
  exec ( @sql )
go
  
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
 
/**/ /*--恢复数据库的通用存储过程
 
--邹建 2003.10--*/
 
/**/ /*--调用示例
--完整恢复数据库
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_RestoreDb @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_RestoreDb @bkfile='c:/db_20031015_log.bak',@dbname='db',@retype='LOG'
 
--*/
 
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为'DB'/'DBNOR'是有效
@killuser   bit = 1 ,       -- 是否关闭用户使用进程,仅@overexist=1时有效
@password   nvarchar ( 20 ) = ''   -- 备份文件的密码(仅sql2000支持),如果备份时设置了密码,必须提供此密码
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 , '' ) = ''
begin
  select   @dbpath = rtrim ( reverse (filename))  from  master..sysfiles  where  name = ' master '
  select   @dbpath = reverse ( substring ( @dbpath , charindex ( ' / ' , @dbpath ), 4000 ))
end
 
-- 生成数据库恢复语句
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
  + case   isnull ( @password , '' )  when   ''   then   ''   else   ' ,PASSWORD= ''' + @password + ''''   end
 
-- 添加移动逻辑文件的处理
if   @retype = ' DB '   or   @retype = ' DBNOR '
begin
  -- 从备份文件中获取逻辑文件名
  declare   @lfn   nvarchar ( 128 ), @tp   char ( 1 ), @i   int , @s   varchar ( 1000 )
 
  -- 创建临时表,保存获取的信息
  create   table  #tb(ln  nvarchar ( 128 ),pn  nvarchar ( 260 ),tp  char ( 1 ),fgn  nvarchar ( 128 ),sz numeric( 20 , 0 ),Msz numeric( 20 , 0 ))
  -- 从备份文件中获取信息
  set   @s = ' restore filelistonly from disk= ''' + @bkfile + ''''
   ++ case   isnull ( @password , '' )  when   ''   then   ''   else   '  with PASSWORD= ''' + @password + ''''   end
  insert   into  #tb  exec ( @s )
  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  hCForEach  cursor   for
  select  s = ' kill  ' + cast (spid  as   varchar )  from  master..sysprocesses 
  where  dbid = db_id ( @dbname )
  exec  sp_msforeach_worker  ' ? '
end
 
-- 恢复数据库
exec ( @sql )
 
Go
邹建说：
说白了,就是备份数据库和还原数据库的SQL语句的应用:
 
-- 备份
backup   database  数据库  to   disk = ' c:/你的备份文件名 '
 
-- 还原
restore   database  数据库  from   disk = ' c:/你的备份文件名 '