在进行服务器迁移时候,可能会遇到需要迁移几十个sqlserver数据库到新服务器,如果一个一个进行建立还原备份或者一个一个进行附加,需要耗费大量的时间和精力。这里给出一个批量附加的操作:

首先开启sqlserver的xp_cmdshell:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

登陆sql企业管理器,新建查询,建立存储过程:

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:\SQL2005\DE2\'
*/
  
--檢查文件路徑是否正確
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

批量附加数据库,这里假设需要附加的数据库都放在了E:\mssql-data目录下。

use master
Go
Exec sp_AttchDataBase 
@Path = 'E:\mssql-data', -- nvarchar(1024)
@DataFiles = NULL, -- nvarchar(max)
@SplitStr = NULL -- nvarchar(50)
/*
成功附加數據庫: [my db]
成功附加數據庫: [TestB]
*/

测试截图:

1.png

来源和参考:http://bbs.csdn.net/topics/370057803