sql server批量附加数据库

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下，
    [email protected] Is Null ?附加@Path文件?下的所有???文件.
  
    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))+
                ’(FileName=N’’’[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)放到@Path下，
    [email protected] Is Null ?附加@Path文件?下的所有???文件.
  
    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))+
                ’(FileName=N’’’[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]