在进行服务器迁移时候，可能会遇到需要迁移几十个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] */
测试截图：
来源和参考：http://bbs.csdn.net/topics/370057803