By: Eli Leiba | Updated: 2017-08-25 | Comments (3) | Scripts
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.
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
- 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.
- 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 Updated: 2017-08-25
About the author
View all my tips