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.
Love the script. Thanks for sharing. Found some typos…
–cursor through the .ndf files
DECLARE ndfFileCur CURSOR STATIC FORWARD_ONLY FOR
WHILE @@FETCH_STATUS = 0
SET @AttachCMD = @AttachCMD + ‘,(FILENAME = ”’ + @DataPath + ‘\’ + RE
Thanks for pointing those out, I’ll sort them out 🙂
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
SSMS works fine if you’ve only got a few databases to attach. If you’ve got a few hundred as I did, SSMS is going to take you a long time.
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))))) + ”’)’
WHERE filename LIKE ‘%.ldf’
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.
Did you consider log shipping? I used that method to migrate 140 databases
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.
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….
Great script, has helped me out but how would I modify it to drop all databases linked to the MDF files in a folder…
When I say drop I mean detach.