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?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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. |
|
|
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?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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. |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-08 : 09:10:16
|
I think he's elwoos IT support, and elwoos is on holiday ...Kristen |
|
|
|