Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    12

    Unanswered: Re-Attaching databases

    Haven't posted in a while but got great advice the last time I did - http://www.dbforums.com/t573220.html
    I hope someone can give me some advice this time.

    Due to a flawed Microsoft hotfix the o/s on which my SQL Server was trashed completely causing me to lose some databases completely. The o/s and SQL Server have now been reinstalled (same version as before).

    I have about 716 databases and logfiles which I need to re-attach to the server. I don't have the old master db or a db of all the database names. I was thinking I could write an asp script using FSO to get all the database & log file names & paths and attach them using sp_attach_db.

    If anyone knows an easier way to do it or has advice to give, I would be really greatful.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Do not keep so many dbs on one server... Think about to merge some dbs.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If they are all in one directory, you could do a directory into a file, import that file into a table, then iterate through the table as needed.

    If they are in multiple directories, you need to construct a "tree walker" that will hunt them all down, and deal with them appropriately.

    I'm going to second the advice of the snail, you probably don't want to attach all 716 at once. I'd do them a few at a time, and somehow conglomerate them into a much smaller number of databases!

    -PatP

  4. #4
    Join Date
    Nov 2002
    Posts
    12
    There are arguments also for keeping them in separate databases.

    Came across this beautiful piece of code - http://www.planetsourcecode.com/vb/s...d=630&lngWId=5

    I'll back them up before trying it.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, in the world of server consolidation efforts you have no choice. And it's not a matter of how many databases are on the server, it's how you handle recovery options. If MASTER and MSDB backups were available, it wouldn't matter how many you got. In the case of data centers it's really not a very large number.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Posts
    12
    Success!

    I successfully re-attached the databases using the above script. I had to modify it slightly so that it attached all the ones beginning with "a", then "b", etc.

    Thanks for your help and advice.

    Now to complain to Microsoft.
    Last edited by Podge; 05-05-04 at 15:15. Reason: forgot to thank contributers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •