Script to attach tất cả file SQL Server MDF cùng folder

Cần attach tất cả data mdf vào sql server

Khi mình chuyển server sql thì attach cả vài ngàn file vào máy chủ là cả cực hình

Solution

The solution involves creating a stored procedure (dbo.usp_MultiAttachSingleMDFFiles) that has one parameter for the folder path that contains the MDF files and then determines the contents of the MDF file using DBCC CHECKPRIMARYFILE.

The procedure loops over all the MDF files and for each file it will get the logical database name from the file, attach it to the SQL Server instance and rebuild the database log file in the process.

The procedure completes the following steps:

• A list of all the MDF file names are read from the folder and inserted into a table variable called @mdfFileNames. Note that in order to use this I am using xp_cmdshell which would need to be enabled. You can use sp_configure system procedure to enable it.
• The procedure checks to make sure that the MDF file is not already attached to the instance of SQL Server.
• The procedure loops over all the MDF file names using a cursor. In order to get the database name from the file name the procedure executes DBCC CHECKPRIMARYFILE for each file. This undocumented statement, when used with a value of 2 for the second parameter, gets the logical database name, collation and internal version number from the file.
• The process checks to make sure the version of the database is not newer than the version of the SQL Server instance.
• After getting the database name, the procedure executes the system procedure sp_attach_single_file_db with the database name and the MDF file name as parameters to attach the database to the SQL Server instance and rebuild a new log file for the database.
• The process is checked for errors by using a try and catch error handling block.  If there is an error, the process will stop and print the MDF file that caused it to stop.

T-SQL Code to Attach Databases

USE master
GO
 
CREATE PROCEDURE [dbo].[usp_MultiAttachSingleMDFFiles] ( @mdfTempDir nvarchar(500) )
AS
BEGIN  
   DECLARE @dirstmt       nvarchar(1000)
   DECLARE @currFile      nvarchar(160)
   DECLARE @db_name       nvarchar(256)
   DECLARE @phys_name     nvarchar(520)
   DECLARE @dbccstmt      nvarchar(1000)
   DECLARE @db2attch_ver  INT
   DECLARE @curr_srv_ver  INT  
   DECLARE @mdfFileNames  TABLE (mdfFile nvarchar(260))
   DECLARE @mdfFileATTR   TABLE (attrName sql_variant, attrValue sql_variant)
   
   DECLARE cf CURSOR FOR SELECT mdfFile FROM @mdfFileNames

   SET NOCOUNT ON

   -- get all mdf file names only , in bare format.
   SET @dirstmt = 'dir /b "' + @mdfTempDir + '"\*.mdf'

   INSERT into @mdfFileNames 
   EXEC xp_cmdshell @dirstmt
   
   DELETE from @mdfFileNames where mdfFile IS NULL or mdfFile = 'File Not Found'

   -- if file is already attached skip it
   DELETE FROM @mdfFileNames 
   WHERE mdfFile IN (SELECT mdfFile FROM @mdfFileNames a INNER JOIN sys.master_files b ON lower(@mdfTempDir + '\' + a.mdfFile) = lower(b.physical_name) )

   -- if no files exist then exit process
   IF not exists (SELECT TOP 1 * FROM @mdfFileNames)
   BEGIN
      PRINT 'No files found to process'
      RETURN
   END

   -- get the current server database version
   SELECT  @curr_srv_ver = CONVERT (int,DATABASEPROPERTYEX('master', 'version'))

   BEGIN TRY

      OPEN cf

      FETCH NEXT FROM cf INTO @currFile

      WHILE @@FETCH_STATUS = 0 
      BEGIN 
 
         SET @phys_name =  @mdfTempDir + '\' + @currFile
         SET @dbccstmt = 'DBCC checkprimaryfile (' + '"' + @phys_name + '"' + ',2)'

         INSERT INTO @mdfFileATTR 
         EXEC (@dbccstmt)
         
         SELECT @db_name = convert (nvarchar(256), attrValue)
         FROM @mdfFileATTR
         WHERE attrName = 'Database name'
      
         -- get the candidate to be attached db version
         SELECT @db2attch_ver = convert (int, attrValue)
         FROM @mdfFileATTR
         WHERE attrName = 'Database version'

         -- if the current server database version is less that the attached db version 
         -- OR
         -- if the database already exists then skip the attach 
         -- print an appropriate message message
         IF (@db2attch_ver > @curr_srv_ver)
            OR
            (exists (SELECT 1 
                     FROM sys.databases d 
                     WHERE RTRIM(LTRIM(lower(d.name))) = RTRIM(LTRIM(lower(@db_name)))))
         BEGIN 
            PRINT ''
            PRINT ' Attach for database ' + @db_name + ' was not performed! '
            PRINT ' Possible reasons : ' 
            PRINT '1. ' +  @db_name + ' DB version is higher that the currnet server version.'
            PRINT '2. ' +  @db_name + ' DB already exists on server.'
            PRINT ''
         END 
         ELSE 
         BEGIN 
            EXEC sp_attach_single_file_db @dbname= @db_name , @physname = @phys_name
            PRINT ''
            PRINT 'Database "' + @db_name + '" attached to server OK using file ' + @currFile + '".'
            PRINT ''
            DELETE FROM @mdfFileATTR
         END 

         FETCH NEXT FROM cf INTO @currFile

      END 
    
      CLOSE cf
      DEALLOCATE cf
   END TRY
   BEGIN CATCH
      PRINT 'Error while attaching FILE ' + @phys_name + ',...Exiting procedure'
      CLOSE cf
      DEALLOCATE cf
   END CATCH 

   SET NOCOUNT OFF
END 
GO

Muốn dùng

• Create a directory on your server ( C:\ATT, for example ) and copy the unattached MDF files.  Note don’t include a “\” at the end of the folder name for the parameter value.
• From SSMS, open a new query window and run the following.

USE master
GO

EXEC dbo.usp_MultiAttachSingleMDFFiles 'C:\ATT' 
GO 

After executing the procedure, refresh the Databases node in SSMS and check that the databases are attached.

Next Steps

• You can use this procedure to attach many unattached single MDF files to your SQL Server instance while rebuilding new LDF files for each database.
• Note that the ‘xp_cmdshell’ configuration option needs to be enabled for this procedure to work.
• The procedure was created and tested on SQL Server 2012, 2014 and 2016.

sưu tầm in tờ net