Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

Scenario:-

This may happen many times with us when we come across a situation where the SQL database has been restored with completion percentage 100% but still in the message display pane in management studio we do not see information which we see when restore finished or database looks still in restoring mode i.e. not yet come online and we wonder why it is taking long time to finish even after percentage shows 100%.


Explanation:

Click Here to check database backup/restore percentage completion in SQL server.

Let’s first check out what restore is doing internally. Restore session performs below three tasks while you execute the restore command.

  • Data Copy Phase
  • Redo phase
  • Undo phase

The data copy phase involves copying all the data, log, and index pages from the backup of a database to the database files. After the completion of this phase SQL Server reports restore completes 100 percent.

In the Redo phase, all the committed transactions present in the transaction log when the database was backed up are rolled forward. It means all the committed transaction changes applied to the database. Still the database has un-committed transaction so database will be in unusable state.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was backed up are rolled back. If the database is being restored with NORECOVERY, the Undo phase is skipped.

From the above explanation you can understand why the restore command is taking longer time after 100 percent completes.


Never reveal what you have thought upon doing, but by wise council keep it secret being determined to carry it into execution

Chanakya