批量分离和附加数据库
来源:互联网 发布:js判断全角半角括号 编辑:程序博客网 时间:2024/05/01 13:04
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:\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
--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'
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:\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
--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'
- 批量分离和附加数据库
- 批量附加、分离数据库
- 附加和分离数据库
- 分离和附加数据库
- 附加和分离数据库
- SQL 分离和附加数据库
- mssql 2000 下批量 附加/分离 数据库(sql语句)
- 通过SQL语句附加和分离数据库
- Mysql数据库的分离和附加
- Mysql数据库的分离和附加
- [SQL Server]自动化附加和分离数据库
- 数据库分离,附加
- 附加分离数据库命令
- 分离及附加数据库
- 批处理附加、分离数据库
- 分离删除附加数据库
- 数据库的附加分离
- sqlserver批量附加 批量分离数据
- C++: Under the Hood
- 局域网自动备份删除
- Travian的第一次成功攻击
- 挖Linux中的古老缩略语
- 安装 VC++ 重启
- 批量分离和附加数据库
- 红眼病流行了
- VS2005中使用webBrowser控件实现自动登录Gmail邮件的实例
- 写作软件需求时应记在心上的原则
- 青春与阳光——李宇春日记
- 《宇春日记2》正在进行时
- FckEdiror XML Request Error 500
- 用火狐浏览器的好处
- conn.asp