Skip navigation

Why Can’t My Database Restore Be an Online Operation?

The question you're really asking is "Why can't I access my database while it's being restored?" and the answer is, depending on what kind of restore you’re doing, you might be able to access parts of the database. In general, the files, file groups, or pages that are being restored are automatically offline because of all the recovery operations that are occurring during a restore.

The restore process typically starts with SQL Server copying the data, log, and index pages from the backup media to the database files. Then comes the “redo” phase—applying logged transactions to the data to the point of recovery in a process called “roll forward.” These logged transactions are changes that didn’t get written to the physical database before the system crash. SQL Server copies data and structural changes to the transaction log first, then writes these changes to the physical database. The “roll forward” part of the recovery process ensures that the changes that were recorded only in the transaction log are now applied to the database.

At this stage, a typical database will have uncommitted transactions and will be unusable. SQL Server 2005 Standard Edition now goes into its last “undo” phase, which “rolls back” any uncommitted transactions. After this phase, the database is fully restored and ready for use. Enterprise Edition works a little differently than Standard Edition—after the “redo” operation you can access the database; you don’t have to wait for the “undo” process to complete.

You can’t access the files, file groups, or pages that are being restored because if you tried to read the data during the restore/recovery (roll forward–roll back) process you’d be reading dirty data. If you tried to edit data that was in the process of being restored, you’d compound the problem of missing transactions (redo) and uncommitted transactions (undo).

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.