批量分离和附加数据库

usemaster
go
declare@detachnvarchar(4000)
set@detach=''
select@detach=@detach+char(13)+'execsp_detach_db'+quotename(Name)+',''true'''frommaster..sysdatabaseswhereNamelike'Lonigo%'
anddbid>4
exec(@detach)
--EXECsp_detach_db'+@name+','+'true'

go
usemaster
go
/**//***********************************************************************************************************************
說明：@file顯示格式為'E:\data',路徑有空格時加上雙引號如：'E:\"Data bak"'
禁止數據庫名為'.'圓點。如：HR.hr.mdf/HR.hr.ldf在文件名存在兩個圓點

功能：附加數據庫

***********************************************************************************************************************/
createprocDataAttach(@filenvarchar(100),@file1nvarchar(100)=@file)
as

declare@mdf1nvarchar(200),@mdf2nvarchar(200),@ldf1nvarchar(200),@ldf2nvarchar(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'

ifobject_id('tempdb..#ta')isnotnull
droptable#ta
createtable#ta(IDintidentity(1,1),FNamenvarchar(256),DataNamenvarchar(256))
insert#ta(FName)execmaster..xp_cmdshell@mdf1
insert#ta(FName)execmaster..xp_cmdshell@ldf1

ifobject_id('tempdb..#tb')isnotnull
droptable#tb
createtable#tb(IDintidentity(1,1),FNamenvarchar(256))
insert#tb(FName)execmaster..xp_cmdshell@mdf2
insert#tb(FName)execmaster..xp_cmdshell@ldf2

--EXECUTEmaster.dbo.xp_fileexistN'E:LonigoBrandAgencyLonigoBrandAgency_Data.MDF'

updatea
setDataName=b.FName
from#taajoin#tbbonreverse(left(reverse(a.FName),charindex('',reverse(a.FName))-1))=b.FName

update#ta
setDataName=casewhencharindex('_',DataName)>0then
casewhenreverse(left(reverse(DataName),charindex('_',reverse(DataName))-1))in('Data.MDF','log.LDF')
thenreverse(stuff(reverse(DataName),1,charindex('_',reverse(DataName)),''))
elsereverse(stuff(reverse(DataName),1,charindex('.',reverse(DataName)),''))end
elseleft(DataName,charindex('.',DataName)-1)end



if(selectcount(1)from#taawhereexists(select1frommaster..sysdatabaseswhereName=a.DataName))>0
begin
declare@errornvarchar(1000)
select@error=isnull(@error+',','')+DataNamefrom#taawhereexists(select1frommaster..sysdatabaseswhereName=a.DataName)groupbyDataName
print'已存在數據庫'+@error
end

if1>0
begin
declare@snvarchar(4000),@s1nvarchar(4000)
set@s=''
select@s=@s+char(13)+
'EXECsp_attach_db@dbname=N'+quotename(DataName,'''')+',@filename1=N'''+
max(casewhenFNamelike'%.MDF'thenFNameelse''end)+''',@filename2=N'''+
max(casewhenFNamelike'%.LDF'thenFNameelse''end)+'''',@s1=isnull(@s1,'')+DataName+char(13)
from#tata
whereFNameisnotnull
and(selectcount(1)from#tatcwhereDataName=ta.DataName)=2--記錄為2
andnotexists(select1frommaster..sysdatabaseswhereName=ta.DataName)
groupbyDataName
--print@s
exec(@s)
end

if@@error<>0
print'附加失敗'+char(13)+@s1
else
print'附加成功'+char(13)+@s1

go


select*from#ta

--execDataAttach'E:\LonigoData'