V1.0版本
Use master
Go
if object_ID(’[sp_AttchDataBase]’) is not null
Drop Procedure [sp_AttchDataBase]
Go
/*附加???(V1.0) Andy 2009-3-3*/
Create Procedure sp_AttchDataBase
(
@Path nvarchar(1024),
@DataFiles nvarchar(max)=null,
@SplitStr nvarchar(50)=’,’
)
As
Set Nocount On
/*
@Path 文件路?
@DataFiles 文件名列表
@SplitStr 文件名列表中的文件分隔符
1.必?把要附加的???文件(*.mdf和*.ldf)放到@Path下，
2.?@DataFiles Is Null ?附加@Path文件?下的所有???文件.
e.g:
Exec sp_AttchDataBase ’D:SQL2005DE2’
*/
--?查文件路?是否正?
Declare
@Dir nvarchar(1024),
@i int,
@x xml
If Right(@Path,1)<>’’
Set @Path=@Path+’’
If Charindex(’\’,@Path)>0
Begin
Raiserror 50001 N’文件路?中不能包含有"\",@Path?置??.’
Return(1)
End
Set @Dir=’Dir ’+@Path
Exec @i=xp_cmdshell @Dir,no_output
If @i<>0
Begin
Raiserror 50001 N’?效的文件路?,@Path?置??.’
Return(1)
End
Declare @Files Table(Name nvarchar(512))
Declare @filetmpfin Table(Name nvarchar(255) Not Null,depth int Null, IsFile bit Null)
Declare @SmoPrimayChildren Table(status int,fileid int,name sysname,filename nvarchar(512))
Declare @smoPrimaryFileProp Table(property sql_variant Null, value sql_variant Null)
Set @DataFiles=Replace(Replace(Replace(@DataFiles,Char(13)+Char(10),’’),Char(13),’’),Char(10),’’)
Set @x=N’<Root><File>’+Replace(@DataFiles,@SplitStr,N’</File><File>’)+N’</File></Root>’
Insert Into @Files
Select t.v.value(’.[1]’,’nvarchar(512)’) As Name
From @x.nodes(’Root/File’) t(v)
Where t.v.value(’.[1]’,’nvarchar(512)’)>’’
Insert Into @filetmpfin Exec master.dbo.xp_dirtree @Path,1,1
Declare @File nvarchar(255),
@sql nvarchar(4000),
@DataBase sysname
Declare cur_File Cursor For
Select Name
From @filetmpfin As a
Where IsFile=1 And
Name Like ’%.mdf’ And
(Exists(Select 1 From @Files Where name=a.Name) Or @DataFiles Is Null) And
Not Exists(Select 1 From Master.sys.master_files Where physical_name=@Path+a.Name)
Open cur_File
Begin Try
Fetch Next From cur_File Into @File
While @@Fetch_Status=0
Begin
Set @sql = ’dbcc checkprimaryfile (N’’’+ @Path+ @File + ’’’ , 2) With No_Infomsgs’
Insert Into @smoPrimaryFileProp Exec (@sql)
Set @sql=’dbcc checkprimaryfile (N’’’ +@Path+ @File + ’’’ , 3) With No_Infomsgs’
Insert Into @SmoPrimayChildren Exec (@sql)
Select @DataBase=Quotename(Convert(nvarchar(255),value)),
@sql=null
From @smoPrimaryFileProp Where Convert(nvarchar(255),property)=’Database name’
Select @sql=Isnull(@sql+’,’+Char(13)+Char(10), ’Create DataBase ’+@DataBase+’ On’+Char(13)+Char(10))+
’(FileName=N’’’+@Path+Right(Rtrim(filename),Charindex(’’,Reverse(Rtrim(filename)))-1)+’’’)’
From @SmoPrimayChildren
Exec(@sql+’ For Attach’)
Print N’成功附加???: ’+@DataBase
Delete From @SmoPrimayChildren
Delete From @smoPrimaryFileProp
Fetch Next From cur_File Into @File
End
End Try
Begin Catch
Declare @Error nvarchar(2047)
Set @Error=ERROR_MESSAGE()
Raiserror 50001 @Error
End Catch
Close cur_File
Deallocate cur_File
Go
调用执行以下命令：
use master
Go
Exec sp_AttchDataBase
@Path = ’D:SQL2005DE2’, -- nvarchar(1024)
@DataFiles = NULL, -- nvarchar(max)
@SplitStr = NULL -- nvarchar(50)
/*
成功附加???: [my db]
成功附加???: [TestB]
*/
V2.0版本
Use master
Go
if object_ID(’[sp_AttchDataBase]’) is not null
Drop Procedure [sp_AttchDataBase]
Go
/*附加???(V2.0) Andy 2011-7-8 */
Create Procedure sp_AttchDataBase
(
@Path nvarchar(1024),
@DataFiles nvarchar(max)=null,
@SplitStr nvarchar(50)=’,’
)
As
Set Nocount On
/*
V2.0 版本，在V1.0基?上，?理文件路?不??原?，e.g. @DataFiles=’E:"my data DB""Hello RT"’
@Path 文件路?
@DataFiles 文件名列表
@SplitStr 文件名列表中的文件分隔符
1.必?把要附加的???文件(*.mdf和*.ldf)放到@Path下，
2.?@DataFiles Is Null ?附加@Path文件?下的所有???文件.
e.g:
Exec sp_AttchDataBase ’D:SQL2005DE2’
*/
--?查文件路?是否正?
Declare
@Dir nvarchar(1024),
@i int,
@x xml
If Right(@Path,1)<>’’
Set @Path=@Path+’’
If Charindex(’\’,@Path)>0
Begin
Raiserror 50001 N’文件路?中不能包含有"\",@Path?置??.’
Return(1)
End
Set @Dir=’Dir ’+@Path
Exec @i=xp_cmdshell @Dir,no_output
If @i<>0
Begin
Raiserror 50001 N’?效的文件路?,@Path?置??.’
Return(1)
End
Set @Path=replace(@Path,’"’,’’) /*?理文件路?不??原?*/
Declare @Files Table(Name nvarchar(512))
Declare @filetmpfin Table(Name nvarchar(255) Not Null,depth int Null, IsFile bit Null)
Declare @SmoPrimayChildren Table(status int,fileid int,name sysname,filename nvarchar(512))
Declare @smoPrimaryFileProp Table(property sql_variant Null, value sql_variant Null)
Set @DataFiles=Replace(Replace(Replace(@DataFiles,Char(13)+Char(10),’’),Char(13),’’),Char(10),’’)
Set @x=N’<Root><File>’+Replace(@DataFiles,@SplitStr,N’</File><File>’)+N’</File></Root>’
Insert Into @Files
Select t.v.value(’.[1]’,’nvarchar(512)’) As Name
From @x.nodes(’Root/File’) t(v)
Where t.v.value(’.[1]’,’nvarchar(512)’)>’’
Insert Into @filetmpfin Exec master.dbo.xp_dirtree @Path,1,1
Declare @File nvarchar(255),
@sql nvarchar(4000),
@DataBase sysname
Declare cur_File Cursor For
Select Name
From @filetmpfin As a
Where IsFile=1 And
Name Like ’%.mdf’ And
(Exists(Select 1 From @Files Where name=a.Name) Or @DataFiles Is Null) And
Not Exists(Select 1 From Master.sys.master_files Where physical_name=@Path+a.Name)
Open cur_File
Begin Try
Fetch Next From cur_File Into @File
While @@Fetch_Status=0
Begin
Set @sql = ’dbcc checkprimaryfile (N’’’+ @Path+ @File + ’’’ , 2) With No_Infomsgs’
Insert Into @smoPrimaryFileProp Exec (@sql)
Set @sql=’dbcc checkprimaryfile (N’’’ +@Path+ @File + ’’’ , 3) With No_Infomsgs’
Insert Into @SmoPrimayChildren Exec (@sql)
Select @DataBase=Quotename(Convert(nvarchar(255),value)),
@sql=null
From @smoPrimaryFileProp Where Convert(nvarchar(255),property)=’Database name’
Select @sql=Isnull(@sql+’,’+Char(13)+Char(10), ’Create DataBase ’+@DataBase+’ On’+Char(13)+Char(10))+
’(FileName=N’’’+@Path+Right(Rtrim(filename),Charindex(’’,Reverse(Rtrim(filename)))-1)+’’’)’
From @SmoPrimayChildren
Exec(@sql+’ For Attach’)
Print N’成功附加???: ’+@DataBase
Delete From @SmoPrimayChildren
Delete From @smoPrimaryFileProp
Fetch Next From cur_File Into @File
End
End Try
Begin Catch
Declare @Error nvarchar(2047)
Set @Error=ERROR_MESSAGE()
Raiserror 50001 @Error
End Catch
Close cur_File
Deallocate cur_File
Go
调用执行：
use master
Go
Exec sp_AttchDataBase
@Path = ’E:"my data DB""Hello RT"’, -- nvarchar(1024)
@DataFiles = NULL, -- nvarchar(max)
@SplitStr = NULL -- nvarchar(50)
--成功附加???: [TestB]
