Shulou(Shulou.com)05/31 Report--

This article mainly introduces how MS SQL Server batch attached database, has a certain reference value, interested friends can refer to, I hope you read this article after a great harvest, the following let Xiaobian take you to understand.

/** ********************************************************** * Title: MS SQL Server Bulk Attach Database * Instructions: Use this script according to the following comments * Time: 2015/7/13 11:16:41 ************************************************************/ USE MASTER GO IF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL DROP PROCEDURE [sp_AttchDataBase] GO /* Attach Database (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, based on V1.0, handles file path irregularities, e.g. @DataFiles='E:\"my data DB"\"Hello RT"' @Path file path @DataFiles List of file names @SplitStr File separator in file name list 1. Database files to attach (*.mdf and *.ldf) must be placed under @Path, 2. When @DataFiles Is Null all database files in the @Path folder are appended. e.g: Exec sp_AttchDataBase 'D:\db2' */ --Check if file path is correct DECLARE @Dir NVARCHAR(1024), @i INT, @x XML IF RIGHT(@Path, 1) '\' SET @Path = @Path + '\' IF CHARINDEX('\\', @Path) > 0 BEGIN --RAISERROR 50001 N'File path cannot contain'\\',@Path setting error. ' RETURN(1) END SET @Dir = 'Dir ' + @Path EXEC @i = xp_cmdshell @Dir, no_output IF @i 0 BEGIN --RAISERROR 50001 N'Invalid file path,@Path set incorrectly. ' RETURN(1) END SET @Path = REPLACE(@Path,'"','') /* Handling file path irregularities */ 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'' + REPLACE(@DataFiles, @SplitStr, N'') + N'' 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'Successfully attached database: ' + @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 /** ********************************************************** * calling method ************************************************************/ --use master --Go --Exec sp_AttchDataBase -- @Path ='E:\100. Other\Tests', -- nvarchar(1024) -- @DataFiles = NULL, -- nvarchar(max) -- @SplitStr = NULL -- nvarchar(50) Thank you for reading this article carefully. I hope that the article "MS SQL Server How to Attach Database in Batch" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support it a lot. Pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.