Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so you’re probably going to be left with the slow, arduous task of doing them one by one.
I recently had to deal with a DR situation (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so I knocked together this script to do the job for me.
Firstly it uses xp_cmdshell to fetch back a list of mdf files in a specified directory which don’t already belong to an existing database.
I then use the undocumented DBCC CHECKPRIMARYFILE to retrieve the database name from the file and the transaction log and any .ndf files that make up the database.
With that information, the script builds up the CREATE DATABASE statements, runs through them and quickly attaches all databases in the specified directory.
Make sure that you change @DataPath and @LogPath to point to the correct place.
Please note that the script assumes that any .ndf files are located in the same location as the .mdf file. It also assumes that you’re using the standard extensions (.mdf -primary datafile, .ndf – secondary datafiles, .ldf – transaction log). If these assumptions are wrong for you, you’ll need to alter the script to suit.
DECLARE @DataPath VARCHAR(MAX) = 'Data file path'
DECLARE @LogPath VARCHAR(MAX) = 'Log file path'
IF (OBJECT_ID('tempdb.dbo.#DataFiles') IS NOT NULL)
DROP TABLE #DataFiles
CREATE TABLE #DataFiles
(mdf VARCHAR(400))
IF (OBJECT_ID('tempdb.dbo.#DBFiles') IS NOT NULL)
DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(status INT,
fileid INT,
name VARCHAR(256),
filename VARCHAR(256))
IF (OBJECT_ID('tempdb.dbo.#DBProperties') IS NOT NULL)
DROP TABLE #DBProperties
CREATE TABLE #DBProperties
(property VARCHAR(256),
value SQL_VARIANT)
DECLARE @Cmd VARCHAR(4000)
DECLARE @DataFileNames TABLE (mdfFile nvarchar(260))
DECLARE @DataFileName VARCHAR(256)
DECLARE @ndfFileName VARCHAR(256)
DECLARE @AttachCMD VARCHAR(2000)
--get list of .mdf files from data directory
SET @Cmd = 'dir /b "' + @DataPath + '"*.mdf'
INSERT into #DataFiles
EXEC xp_cmdshell @Cmd
--cursor containing only .mdf files are aren't associatated with an existing database
UPDATE #DataFiles
SET mdf = @DataPath + '' + mdf
DECLARE DataFilesCur CURSOR STATIC FORWARD_ONLY FOR
SELECT mdf
FROM #DataFiles
WHERE mdf != 'null'
AND mdf NOT IN (SELECT physical_name FROM sys.master_files)
OPEN DataFilesCur
FETCH NEXT FROM DataFilesCur INTO @DataFileName
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #DBFiles
TRUNCATE TABLE #DBProperties
--DBCC CHECKPRIMARYFILES to return all files associated with the database
INSERT INTO #DBFiles(status, fileid, name, filename)
EXEC ('DBCC CHECKPRIMARYFILE(''' + @DataFileName + ''', 3) WITH NO_INFOMSGS')
--DBCC CHECKPRIMARYFILES to get database name
INSERT INTO #DBProperties (property, value)
EXEC ('DBCC CHECKPRIMARYFILE(''' + @DataFileName + ''', 2) WITH NO_INFOMSGS')
--Begin constructing file attachment command
SELECT @AttachCMD = 'CREATE DATABASE ' + QUOTENAME(CAST(value AS SYSNAME))
FROM #DBProperties
WHERE property = 'Database name'
SET @AttachCMD = @AttachCMD + ' ON (FILENAME = ''' + @DataFileName + '''),'
SELECT @AttachCMD = @AttachCMD + '(FILENAME = ''' + @LogPath + '' + REVERSE(SUBSTRING(REVERSE(RTRIM(filename)),0,CHARINDEX('',REVERSE(RTRIM(filename))))) + ''')'
FROM #DBFiles
WHERE filename LIKE '%.ldf'
--cursor through the .ndf files
DECLARE CURSOR ndfFileCur STATIC FORWARD_ONLY FOR
SELECT filename
FROM #DBFiles
WHERE filename LIKE '%.ndf'
OPEN ndfFileCur
FETCH NEXT FROM ndfFileCur INTO @ndfFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AttchCMD = @AttachCMD + ',(FILENAME = ''' + @DataPath + '' + REVERSE(SUBSTRING(REVERSE(RTRIM(@ndfFileName)),0,CHARINDEX('',REVERSE(RTRIM(@ndfFileName))))) + ''')'
FETCH NEXT FROM ndfFileCur INTO @ndfFileName
END
CLOSE ndfFileCur
DEALLOCATE ndfFileCur
SET @AttachCMD = @AttachCMD + ' FOR ATTACH'
--PRINT @AttachCMD
EXEC (@AttachCMD)
FETCH NEXT FROM DataFilesCur INTO @DataFileName
END
CLOSE DataFilesCur
DEALLOCATE DataFilesCur
Thanks for reading and I hope you’ve found it userful.