Sql Server 2000 Databases

[clubsceneuk2]

Hi everyone, please help

We have SQL Server 2000 running on 6 different servers and i want to add a lot of additional databases to 1.
The problem is tis data was originally archived and has been restored to the server but it is in MDF file format. Now adding them one at a time is not a problem until you realise there are over 500 to add so i want to know the following....

Can you attach multipul databases all in one go and secondly once this is done can i import all of the data from these data bases in to 1 main data base in order for me to access the data.
Im trying to save myself the job of having to add each one individually.

Please help.

Craig

 

[mrdenny]

You could use xp_dirtree to get a list of mdf files in a folder, then use a cursor to run the sp_attach_db system stored procedure passing in the name of the database, and the filenames.

Then you can use sp_MSforeachdb to run a SQL Script to move the data from one database into a master database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog