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

时间:2021-10-01 08:21:05
您好,邹老师,我在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''')
改成这样就可以了。

#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''')
改成这样就可以了。