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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Need some help with reattaching db's

Author  Topic 

maxmutant
Starting Member

4 Posts

Posted - 2004-07-07 : 13:32:04
Hey everyone,

here's the situation.

I have a couple of servers.

I need to migrate all databases from one to the other.

I was able to do a bulk detach.

I can then copy the files over.

I was wondering if anyone has a script example of how to loop and issue a reattach statement for all databases.

Thanks in advance for your consideration.

M

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 13:34:19
urr? How's the script going to automagically know what databases to reattach or where they are?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

maxmutant
Starting Member

4 Posts

Posted - 2004-07-07 : 13:38:26
I was going to stick all of the databases into the mssql\data directory.

That way everything thats in there can be read and reattached.

Assume the master, model, msdb and temp get excluded in the script.


Part of the issue is that the server got blown up and this is a complete rebuild.




Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 13:45:06
Did you use some kind of standard when you named the database files? I call all my .mdf files database_Data.mdf so I can parse out the db name? If you have done that, you can just use xp_cmdshell to do a 'DIR /B \\computer\share\mssql\backup\' and loop through that. Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

maxmutant
Starting Member

4 Posts

Posted - 2004-07-07 : 13:49:26
Derrick,

I was thinking along those lines.

The issue becomes how to handle the log files.

I have over 60 databases, so its tedious to reattach by hand.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 22:45:02
How did you end up doing this? I'm curious. If you named the data AND log files according to a naming standard it would be pretty easy. It would be nice to have a script to stash though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 03:07:26
Perhaps it would be better to have a script that generates the ATTACH script when it does the DETACH?

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 08:41:50
Well, I think I alluded to that earlier. If you read the thread it's a little too late for that though. :)



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 08:51:55
Get away with you, you Mean Old DBA ...

What was in my mind was I presume these DBs were detached - the only alternative being that they were backed up (e.g. by a tape NOT using a SQL Agent, but just a file-by-file approach) whilst the DB was running (in which case am I right in thinking that they are as good as trash?) or with SQL stopped (which seems unlikely on a server running 60 DBs.)

So if it detaching them was a deliberate act, for example detaching all databases in order to back them up, I'd generate a script to detach them all, and another to re-attach them, coz as soon as they are backed up I've gotta attach them again!

But if they are now being attached because "the server got blown up", and they got copied either off a tape backup (as above) or "rescued" from the original disk, aren't they just going to be junk once attached?

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 09:03:17
They will be if he didn't attach them and just copied them over. Sounds fun, doesn't it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 09:10:16
I think he's elwoos IT support, and elwoos is on holiday ...

Kristen
Go to Top of Page
   

- Advertisement -