Attaching database with data files only - URGENT

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    I am recovering some suspect databases, and I ran into an interesting problem. I basically can't recover the suspect databases by using sp_resetstatus (it doesn't help). I am trying to detach the databases, then attach them using sp_attach_single_file_db. This works for databases that only have an .mdf and a .ldf (the log file is created when the command is run). My problem is, how do you do this procedure if you have multi-file databases (a .mdf file, one or more .ndf files, and an .ldf file)? There should be something like "attach data files only" in addition to sp_attach_single_file_db.

    The problems we are experiencing occured byfore last night's backups, so I will have to roll to Monday Night's backups, losing all of yesterdays work unless I can attach these databases without log files. Any help would be greatly appreciated.

  • DCPeterson

    SSCoach

    Points: 15145

    You can still use sp_attach_db to attach multiple data files without specifying a log file.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    But not if there are multiple log files. But it seems as in this case there is only a single log file, so then sp_attach_db should do the trick.

  • DCPeterson

    SSCoach

    Points: 15145

    I wasn't aware of that limitation, but then again, who would create multiple log files?  Although I'm sure it's been done, it isn't something that normally factors into my equations...

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • sa24

    SSCrazy Eights

    Points: 8027

    No hard feelings for Oracle guys. But I have seen this when people start managing SQL Server like Oracle. They need to understand that Life is easy with SQL Server.

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    Absolutely, I have yet to see a reason to use multiple log files in a SQL Server database. Unfortunately I have had to work with databases that had them, so I have encountered this limitation.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply