批量恢复项目文件程序

----------------------------------------------------------从项目文件恢复项目------------------------------------------------------

declare @strFilePath nvarchar(2000)
create table  #dt_files(filePath nvarchar(1000),depth int,fileid int)
insert into #dt_files execute master..xp_dirtree 'd:\项目备份\1',1,1
update #dt_files set filePath='d:\项目备份\1\'+filePath
declare cur_1 cursor for 
select filePath from #dt_files order by filePath
open cur_1
fetch next from cur_1 into @strFilePath
while @@fetch_status=0
begin
declare @logicalname_db nvarchar(200)
declare @logicalname_log nvarchar(200)
declare @newName nvarchar(200)
create table #MadteaTest (LogicalName varchar(100),physicalName varchar(1000),type varchar(5),fileGroupName varchar(20),size bigint,maxsize varchar(20))  
insert #MadteaTest exec ('RESTORE FILELISTONLY  FROM DISK = '''+@strFilePath+''' with password=''Madtea''') 
select @logicalname_db=logicalname from #MadteaTest where type='D'
select @logicalname_log=logicalname from #MadteaTest where type='L'
if(charindex('_',@logicalname_db)>0)
set @newName=substring(@logicalname_db,1,charindex('_',@logicalname_db)-1) 
else 
set @newName=@logicalname_db
if exists(select name from master.dbo.sysdatabases where name=@newName)
begin
print @newName
print @strFilePath
end 
else
begin
declare @strSql nvarchar(4000)
set @strSql=N'restore database '+@newName+' from disk='''+@strFilePath+''' with password=''Madtea'',move '''+@logicalname_db+''' to ''D:\Madtea\Data\'+@newName+'.mdf'',move '''+@logicalname_log+''' to ''D:\Madtea\Data\'+@newName+'.ldf'''
print @strSql
exec(@strSql)
end
drop table #MadteaTest
fetch next from cur_1 into @strFilePath
end
close cur_1
deallocate cur_1
drop table #dt_files


-------------------------------------------------------从项目文件恢复项目-----------------------------------------------------------------