批量恢复项目文件程序


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

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


-------------------------------------------------------从项目文件恢复项目-----------------------------------------------------------------
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值