Attach All SQL Datafiles in a Directory

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.

10 thoughts on “Attach All SQL Datafiles in a Directory

Add yours

  1. Love the script. Thanks for sharing. Found some typos…
    1)
    –cursor through the .ndf files
    DECLARE ndfFileCur CURSOR STATIC FORWARD_ONLY FOR
    2)
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @AttachCMD = @AttachCMD + ‘,(FILENAME = ”’ + @DataPath + ‘\’ + RE

    Like

  2. In SQL Server Management Studio Object Explorer, connect to an instance of SQL Server Database Engine, and then select to expand that instance view in SSMS.
    Right-click Databases and select Attach.
    In the Attach Databases dialog box, to specify the database to be attached, select Add

    Like

  3. Great script, thank you for posting this. One thing I noticed, is that if you have multiple LDF files, you are missing the comma on the second and subsequent files. I’d suggest you remove the comma at the end of the + @DatafileName line and add it to the start of the code in the LDF query, like this:

    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’

    Like

    1. A very good point 🙂. I’ll be honest, multiple log files is something that I hardly ever take into consideration as I do rarely come across a need for it. But if you do then it is something to think about.

      Like

    1. Log shipping is a great method if you’re migrating. The situation I founf myself in was a DR situation. The VMs and onsite backups were all lost. Off site backups existed but that would have meant time to retrieve them and around 24 hour dataloss. Luckily the underlying data LUNs were still good so by far the easiest option was to spin up a new VM, attach the LUNs and then attach the database in SQL.

      Like

    2. If you are migrating from one server to another and you have time to setup Logshipping, then that is a fantastic way to go, but in the situation mentioned above, it sounded to me like it was a DR situation, and they just had the MDF/LDF/NDF files, so nothing to logship from. This would dynamically create and execute the statements to attach those files to a server….

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: