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)[email protected], [email protected] Is Null [email protected]?下的所有???文件. e.g: Exec sp_AttchDataBase ’D:SQL2005DE2’ */ --?查文件路?是否正? Declare @Dir nvarchar(1024), @i int, @x xml If Right(@Path,1)<>’’ Set @[email protected]+’’ If Charindex(’\’,@Path)>0 Begin Raiserror 50001 N’文件路?中不能包含有"\",@Path?置??.’ Return(1) End Set @Dir=’Dir [email protected] 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 [email protected]+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’’’ [email protected]+ @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 [email protected]+’ On’+Char(13)+Char(10))+ ’([email protected]+Right(Rtrim(filename),Charindex(’’,Reverse(Rtrim(filename)))-1)+’’’)’ From @SmoPrimayChildren Exec(@sql+’ For Attach’) Print N’成功附加???: [email protected] 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)[email protected], [email protected] Is Null [email protected]?下的所有???文件. e.g: Exec sp_AttchDataBase ’D:SQL2005DE2’ */ --?查文件路?是否正? Declare @Dir nvarchar(1024), @i int, @x xml If Right(@Path,1)<>’’ Set @[email protected]+’’ If Charindex(’\’,@Path)>0 Begin Raiserror 50001 N’文件路?中不能包含有"\",@Path?置??.’ Return(1) End Set @Dir=’Dir [email protected] 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 [email protected]+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’’’ [email protected]+ @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 [email protected]+’ On’+Char(13)+Char(10))+ ’([email protected]+Right(Rtrim(filename),Charindex(’’,Reverse(Rtrim(filename)))-1)+’’’)’ From @SmoPrimayChildren Exec(@sql+’ For Attach’) Print N’成功附加???: [email protected] 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]