• First of all, I strongly recommend against killing SPIDs for anything ever. It's a documented problem on CONNECT that the KILL process can leave spids that consume large amounts of resources trying to do a "zero rollback" that will never finish because there's actually nothing to rollback. To date, the only fix for such "zero rollback" spids is to bounce the SQL Server Service. I have witnessed/been victim to this problem myself.

    Instead of killing spids, write a script to set the database to single user mode with [font="Arial Black"]ROLLBACK IMMEDIATE[/font], immediately change it back to multi-user mode (in case you lose the connection, you don't want something else grabbing the only connection available), and immediately start the restore all within the same script. The attached Webservices and the like won't be fast enough to do a reconnect before the restore starts if you do those 3 steps in a single T-SQL script (I do this all the time on my Dev and Staging boxes when doing restores/refreshes). Don't forget, your SSMS session must not be attached to the database while running the script.

    I also don't take the database offline unless there are other compelling reasons to do so. I let the restore do that.

    Second, the question is about ONLINE restores. That would imply NOT killing any connections. I believe the real answer here is that you can't do an "online" restore if the only filegroup available is the PRIMARY filegroup. In fact, according to what's in Books Online ("restores, online" in 2005 and "Piecemeal" restores in 2005 and up), I don't believe you can do an "online" restore of the PRIMARY FG even if multiple other FGs exist because the PRIMARY FG must be up and fully functional at all times for the database to be "online" to begin with.

    All that being said, I strongly recommend you spend some time on "restores" in Books Online before you try anything else. To do otherwise is like a blind man smoking a cigarette in a gunpowder factory. 😉 And, just to be sure, "Books Online" is the help system you can get to by pressing the {f1} key while in SSMS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)