在进行服务器迁移时候,可能会遇到需要迁移几十个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