SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

RobertStewart
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?
Thanks!

robvolk
Most Valuable Yak

USA
15729 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

tosuncan
Starting Member

Turkey
1 Posts

Posted - 03/05/2013 :  07:38:06  Show Profile  Reply with Quote
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

Norway
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

scanme
Starting Member

1 Posts

Posted - 03/06/2015 :  09:36:53  Show Profile  Reply with Quote
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
  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.05 seconds. Powered By: Snitz Forums 2000