您好，邹老师，我在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
期待您的指点，不胜感激。
插入错误: 列名或所提供值的数目与表定义不匹配。
消息 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
期待您的指点，不胜感激。
这个存储过程执行是成功的，但调用这个来恢复就不可以了。
exec p_RestoreDb @bkpath='c:\'
原因：
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''')
改成这样就可以了。