Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Attach Multiple [250 at a time] Databases.

Author  Topic 

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-19 : 04:16:06
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?
Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 07:52:35
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

tosuncan
Starting Member

1 Post

Posted - 2013-03-05 : 07:38:06
Hi,

http://www.datakent.com/FreeApps/SQL_Server_attach_multiple_databases.zip

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

Torgnesskar
Starting Member

1 Post

Posted - 2013-10-10 : 22:57:52
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

scanme
Starting Member

1 Post

Posted - 2015-03-06 : 09:36:53
quote:
Originally posted by tosuncan

Hi,

http://www.datakent.com/FreeApps/SQL_Server_attach_multiple_databases.zip

This program simplifies the process of adding multiple data base.




hi tosuncan
i see this program and very good
but my database files is not *.mdf file ,
can u say me , how attach multiple database with other database ?

if u update this program for attach other database files very good

Accounting Man
Go to Top of Page
   

- Advertisement -