use master
go
declare @detach nvarchar(4000)
set @detach=''
select @detach=@detach+char(13)+'exec sp_detach_db '+quotename(Name)+',''true''' from master..sysdatabases where Name like 'Lonigo%'
and dbid>4
exec( @detach)
--EXEC sp_detach_db '+ @name+', '+ 'true'
go
use master
go
/**//***********************************************************************************************************************
說明：@file顯示格式為' E:date'
禁止數據庫名為'.'圓點。如：HR.hr.mdf/HR.hr.ldf 在文件名存在兩個圓點
功能：附加數據庫
***********************************************************************************************************************/
create proc DataAttach (@file nvarchar(100),@file1 nvarchar(100)=@file)
as
declare @mdf1 nvarchar(200),@mdf2 nvarchar(200),@ldf1 nvarchar(200),@ldf2 nvarchar(200)
select @mdf1='dir /b/o/s '+@file+'*.mdf',@ldf1='dir /b/o/s '+@file1+'*.ldf',
@mdf2='dir /b/o '+@file+'*.mdf',@ldf2='dir /b/o '+@file1+'*.ldf'
if object_id('tempdb..#ta') is not null
drop table #ta
create table #ta(ID int identity(1,1),FName nvarchar(256),DataName nvarchar(256))
insert #ta(FName) exec master..xp_cmdshell @mdf1
insert #ta(FName) exec master..xp_cmdshell @ldf1
if object_id('tempdb..#tb') is not null
drop table #tb
create table #tb(ID int identity(1,1),FName nvarchar(256))
insert #tb(FName) exec master..xp_cmdshell @mdf2
insert #tb(FName) exec master..xp_cmdshell @ldf2
--EXECUTE master.dbo.xp_fileexist N'E:LonigoBrandAgencyLonigoBrandAgency_Data.MDF'
update a
set DataName=b.FName
from #ta a join #tb b on reverse(left(reverse(a.FName),charindex('',reverse(a.FName))-1))=b.FName
update #ta
set DataName=case when charindex('_',DataName)>0 then
case when reverse(left(reverse(DataName),charindex('_',reverse(DataName))-1)) in('Data.MDF','log.LDF')
then reverse(stuff(reverse(DataName),1,charindex('_',reverse(DataName)),''))
else reverse(stuff(reverse(DataName),1,charindex('.',reverse(DataName)),'')) end
else left(DataName,charindex('.',DataName)-1) end
if (select count(1) from #ta a where exists(select 1 from master..sysdatabases where Name=a.DataName))>0
begin
declare @error nvarchar(1000)
select @error=isnull(@error+',','')+DataName from #ta a where exists(select 1 from master..sysdatabases where Name=a.DataName) group by DataName
print '已存在數據庫 '+@error
end
if 1>0
begin
declare @s nvarchar(4000),@s1 nvarchar(4000)
set @s=''
select @s=@s+char(13)+
'EXEC sp_attach_db @dbname = N'+quotename(DataName,'''')+', @filename1 = N'''+
max(case when FName like '%.MDF' then FName else '' end)+''', @filename2 = N'''+
max(case when FName like '%.LDF' then FName else '' end)+'''',@s1=isnull(@s1,'')+DataName+char(13)
from #ta ta
where FName is not null
and (select count(1) from #ta tc where DataName=ta.DataName)=2--記錄為2
and not exists(select 1 from master..sysdatabases where Name=ta.DataName)
group by DataName
--print @s
exec (@s)
end
if @@error<>0
print '附加失敗'+char(13)+@s1
else
print '附加成功'+char(13)+@s1
go
select * from #ta
--exec DataAttach 'E:LonigoData'
发表于 @ 2007年08月25日 11:49:00|评论(loading...)|编辑