邹老师在吗，新手提问。关于您以前写的批量恢复指定目录下的数据库文件的问题

您好，邹老师，我在SQL2005中用了您写的“恢复指定目录下的所有数据库”脚本进行数据库的批量恢复，报了如下错误：

消息 213，级别 16，状态 7，第 1 行
插入错误: 列名或所提供值的数目与表定义不匹配。
消息 3013，级别 16，状态 1，第 1 行
RESTORE FILELIST 正在异常终止。
消息 16915，级别 16，状态 1，过程 p_RestoreDb，第 80 行
名为 'hCForEach' 的游标已存在。
消息 16950，级别 16，状态 2，过程 sp_MSforeach_worker，第 27 行
目前没有为变量 '@local_cursor' 分配游标。
消息 16950，级别 16，状态 2，过程 sp_MSforeach_worker，第 32 行
目前没有为变量 '@local_cursor' 分配游标。
消息 16950，级别 16，状态 2，过程 sp_MSforeach_worker，第 153 行
目前没有为变量 '@local_cursor' 分配游标。
消息 16916，级别 16，状态 1，过程 sp_MSforeach_worker，第 155 行
名为 'hCForEachDatabase' 的游标不存在。

不过似乎不影响结果，数据库一样是被恢复了。只是不明白如何修改这些错误。

原代码如下：

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  
   
  /*--恢复指定目录下的所有数据库  
   
  恢复的数据库名为备份文件名(不含扩展名)  
  备份文件的扩展名固定为.bak  
   
  --邹建   2003.10(引用请保留此信息)--*/  
   
  /*--调用示例  
  --恢复指定目录下的所有数据库  
  exec   p_RestoreDb   @bkpath='c:\'  
   
  --恢复指定目录下的指定数据库  
  exec   p_RestoreDb   @bkpath='c:\',@bkfile='客户资料,xzkh_new'  
  --*/  
  create   proc   p_RestoreDb  
  @bkpath   nvarchar(1000)='',   --定义备份文件的存放目录,默认为SQL的备份目录  
  @bkfile   nvarchar(4000)='', --定义要恢复的备份文件名,不含扩展名  
  @dbpath   nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录  
  @overexist   bit=1,             --是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效  
  @killuser   bit=1       --是否关闭用户使用进程,仅@overexist=1时有效  
  as  
  declare   @sql   varchar(8000),@dbname   sysname  
   
  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  
  else   if   right(@bkpath,1)<>'\'   set   @bkpath=@bkpath+'\'  
   
  --得到恢复后的数据库存放目录  
  if   isnull(@dbpath,'')=''  
  begin  
  select   @dbpath=rtrim(reverse(filename))   from   master..sysfiles   where   name='master'  
  select   @dbpath=reverse(substring(@dbpath,charindex('\',@dbpath),4000))  
  end  
  else   if   right(@dbpath,1)<>'\'   set   @dbpath=@dbpath+'\'  
   
  --得到指定目录下的所有备份文件  
  create   table   #t(fname   varchar(260),depth   int,isf   bit)  
  insert   into   #t   exec   master..xp_dirtree   @bkpath,1,1  
   
  if   isnull(@bkfile,'')=''  
  declare   tb   cursor   local   for   select   fn=left(fname,patindex('%.bak',fname)-1)   from   #t  
  where   isf=1   and   fname   like   '%.bak'     --取.bak文件  
  else  
  begin  
  set   @bkfile=','+replace(@bkfile,',','.bak,')+'.bak,'  
  declare   tb   cursor   local   for   select   fn=left(fname,patindex('%.bak',fname)-1)   from   #t  
  where   isf=1   and   fname   like   '%.bak'   and   @bkfile   like   '%,'+fname+',%'  
  end  
   
  --恢复数据库处理  
  open   tb  
  fetch   next   from   tb   into   @dbname  
  while   @@fetch_status=0  
  begin  
  --生成数据库恢复语句  
  set   @sql='restore   database   ['+@dbname  
  +']   from   disk='''+@bkpath+@dbname+'.bak'''  
  +'   with   RECOVERY'  
  +case   when   @overexist=1   then   ',replace'   else   ''   end  
   
  --添加移动逻辑文件的处理  
  --从备份文件中获取逻辑文件名  
  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='''+@bkpath+@dbname+'.bak''')  
  declare   #f   cursor   local   for   select   ln,tp   from   #tb   order   by   tp  
  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  
  drop   table   #tb  
   
  --关闭用户进程处理  
  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)  
  fetch   next   from   tb   into   @dbname  
  end  
  close   tb  
  deallocate   tb  
  go

期待您的指点，不胜感激。

11 个解决方案

#1

关注，

#2

帮顶.

#3

我这边是成功的

#4

2000下好像可以，但2005就不行，郁闷：（
这个存储过程执行是成功的，但调用这个来恢复就不可以了。
exec   p_RestoreDb   @bkpath='c:\'  

#5

顶一下，期待高手能协助解决问题。

#6

学习中，还看不懂。

#7

UP

#8

已经搞定了，谢谢大家。

#9

邹老师好像很久没来了哦。。。

#10

楼主，能否解释怎么解决的？谢谢，我的也是SQL SERVER 2005,也是报这样的错。。。

#11

楼主不厚道啊，我也遇到这个问题了
原因：
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='''+@bkpath+@dbname+'.bak''')

这两句，sql server 2000 查找出来的列数是6，但是sql server 2008 查出来的不止6列，所以在下面这句插入出问题了
解决办法：
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0),
FId bigint,CLsn numeric(25,0),DLsn numeric(25,0),UnId uniqueidentifier,ROLsn numeric(25,0),RWLsn numeric(25,0),
bsib bigint,sbz int,FGId int,lgguid uniqueidentifier,dblsn numeric(25,0),dbguid uniqueidentifier,iro bit,
isp bit,tdet varbinary(32)) 
--从备份文件中获取信息  
insert   into   #tb   exec('restore   filelistonly   from   disk='''+@bkpath+@dbname+'.bak''')
改成这样就可以了。