批量分离和附加数据库

use master
go
declare @detach nvarchar ( 4000 )
set @detach = ''
select @detach = @detach + char ( 13 ) + ' execsp_detach_db ' + quotename (Name) + ' , '' true ''' from master..sysdatabases where Name like ' Lonigo% '
and dbid > 4
exec ( @detach )
-- EXECsp_detach_db'+@name+','+'true'

go
use master
go
/**/ /***********************************************************************************************************************
說明：@file顯示格式為'E:\data',路徑有空格時加上雙引號如：'E:\"Data bak"'
禁止數據庫名為'.'圓點。如：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

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

update a
set DataName = b.FName
from #taa join #tbb 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 #taa where exists ( select 1 from master..sysdatabases where Name = a.DataName)) > 0
begin
declare @error nvarchar ( 1000 )
select @error = isnull ( @error + ' , ' , '' ) + DataName from #taa 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 ) +
' EXECsp_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 #tata
where FName is not null
and ( select count ( 1 ) from #tatc 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

-- execDataAttach'E:\LonigoData'