SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Attach Multiple [250 at a time] Databases.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 05/19/2011 :  04:16:06  Show Profile  Reply with Quote
Hi folks,
I am currently adding 250 databases to a SQL Server (MS SQL Server 2008) using SQL Server Management Studio. It took all day yesterday, and I am sure there has to be another way to do it. The filenames are quite complex and it is not easy tracking which ones I have added. Is there a wee loop or something I can use to add all .mdf and .ldf files in a folder to the server?

Most Valuable Yak

15701 Posts

Posted - 05/19/2011 :  07:52:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Assuming the files are named DB.mdf and the log files are DB_log.ldf:
exec master..xp_cmdshell 'for %a in (C:\Path\*.mdf) do @echo exec sp_attach_db ''%~na'', ''%a'', ''%~dpn_log.ldf'' '
That will generate the necessary SQL to attach all the files in C:\Path. Change as necessary, copy and paste the results in a new window.

I blogged about the DOS for command a while back if you're interested: http://weblogs.sqlteam.com/robv/category/128.aspx
Go to Top of Page

Starting Member

1 Posts

Posted - 03/05/2013 :  07:38:06  Show Profile  Reply with Quote


This program simplifies the process of adding multiple data base.
Go to Top of Page

Starting Member

1 Posts

Posted - 10/10/2013 :  22:57:52  Show Profile  Reply with Quote
When I try this script I get this result. Using SQL 2012 and I can not attach becouse of the logfiles path is not correct.

My result
exec sp_attach_db 'AdvokatFossvollVLData', 'e:\mssql\Data\AdvokatFossvollVLData.MDF in ','%~_log.ldf'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000