Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to attach multiple SQL Server MDF files


By:   |   Read Comments (2)   |   Related Tips: More > Scripts

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I need a quick way to attach numerous SQL Server databases to a new instance where I just had the MDF files from a SQL Server Express instance.  All of the databases had a single MDF file for each database, so I wrote the following script to attach the SQL Server databases.

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
   

Example Use

  • 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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, September 01, 2017 - 8:24:16 AM - Jim Schellenberg Back To Top

 Occasionally we have databases with multiple data files. Do you have any thoughts on how to go about attaching these databases? 

 


Friday, August 25, 2017 - 10:20:11 PM - Agarwal Sushil Narsidas Back To Top

 I many times need this to be done whenever my pc is formatted, but always attaching files gives error 5, I need to change for both msg and ldf file security settings and set them with the user name and give full writes. 

Can u suggest how to do that in your script so that it will surely succsed.

 


Learn more about SQL Server tools