SQL Server Performance

Online database restore

Discussion in 'SQL Server 2008 General DBA Questions' started by Trev256b, May 24, 2011.

  1. Trev256b Member

    Is online restore not completed if some data is continually locked by a user?
  2. Jahanzaib Member

    User cant interrupt restore process because when restore process is going on File groups automatically offline or you can say database is in offline mode and in restore state after restore ,recover process start it and then database come to normal mode for transaction ,Recover process will start after restore if you have mention Recovery clause in the restore statement,you can define No Recovery clause in restore statement also
  3. Trev256b Member

    Sorry that doesn't answer the question. What happens if user has exclusive lock on data forever?
  4. Luis Martin Moderator

    Is that a production server issue question or interview question?
  5. Jahanzaib Member

    What does it mean by Data forever? ,if you mean like there is no end of the query or transaction then that must be a dead lock victim of that process
  6. satya Moderator

    Syed has already explained about the restore process, however for ONLINE RESTORE technique BOL states that :
    "Restoring data while the database is online is called an online restore. A database is considered to be online whenever the primary filegroup is online, even if one or more of its secondary filegroups are offline. Under any recovery model, you can restore a file that is offline while the database is online. Under the full recovery model, you can also restore pages while the database is online. "
    So unless the relevant filegroup is restored the user process cannot acquire so called locking...
  7. Trev256b Member

    if a user has a process open on the database that hold an exclusive lock on a table (with an update) - does that mean you can still do an online restore, or will it never complete the online restore until the lock is released??
  8. satya Moderator

    The restore will fail as it is failed to obtain exclusive lock of file to perform restore.
  9. Hemantgiri New Member

    What Satya said is very true, restore won't happened if database is in use. The other scenario could be, a database which spread across multiple files and file groups, now a table - table1 resides in FileGroupAA and this table is not being accessed by any user, then you can perform file/filegroup restore on this (to the best of my knowledge) .

    Satya please advise!!

Share This Page