What are the phases of sql server database restore process?

There are two types of recoveries:

1) Restart Recovery

·        Every time an instance is restarted/ started the consistency of all the databases including master, model, msdb and tempdb is checked.

·        This process is an internal operation and initiated just to keep the entire instance clean and with integrity.

·        Taking database offline/online involves restart recovery for that database.

2) Restore Recovery

·        As per backup strategy whenever a restore is started and recovery is done per backup sequence.

·        This entire process of recovery initiated manually is called as Restore recovery.

Following the data copy phase involving copying of all the data, log, and index-pages from the backup media of a database to the database files, four consecutive phases that take place during SQL Server recovery are

1.       Discovery – is to find the logical structure of the Transaction log file.

2.       Analysis – is to find the best LSN starting from which rolling forward can be done during redo phase.

3.       Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files.

4.       Undo – is the phase where in, rolling back of the active transactions for consistency, takes place.

Discovery Phase:

The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

This is the reason why it is preferred to have optimal number of VLFs in a log file.

For further information on influence of VLFs in Transaction Log throughput please refer point #8 in Kimberly Trip’s blog http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

 

Analysis Phase:

Preparation of ‘Dirty Page Table’ (DPT) and ‘Active Transaction Table’ (ATT) are the prime motives of Analysis phase. These two tables are put to use by SQL Server during subsequent redo and undo phases respectively.

To create DPT, SQL Server requires to make a note all the pages and their LSNs that might have been dirty (à not yet hardened) at the time of crash, from the transaction log (.ldf), so that during redo phase all such pages will be rolled forward and at the end of redo phase the database would be in such a state as if it was just before crash.

As all the pages prior to last checkpoint would have been already hardened and the pages after the last checkpoint are the ones that are dirty but yet to get hardened. Hence analysis phase starts (in the sense SQL Server starts reading using the .ldf) from the last checkpoint LSN till end of transaction log.

Scanning through the transaction log from the latest checkpoint till end of transaction log prepares the list of all pages that are dirty and obviously not hardened as they are after checkpoint. This list is the DPT. The minimum of all the LSNs available from DPT will be the minimum recovery LSN. Similarly using transaction log file, active transaction table is generated.

Redo Phase:

“Rolling forward all the changes that took place after the checkpoint and just before the crash so that at the end of redo phase the db would be in a state as if it was just before the crash”, is the intent of redo phase.

Hence making use of minimum recovery LSN obtained from DPT, starting from the minimum recovery LSN and till the LSN at end of transaction log, SQL server rolls forward (hardens) all the changes that are present in all the dirty ( not yet hardened) pages and brings the db to the desired state.

Undo Phase:

Ensuring that the data integrity is not hampered so that db can be opened for access is the aim of Undo phase. For this, all the changes made by all the transactions that were active at the time of crash are to be rolled back.

Hence, SQL server, using ATT , starting from LSN at the end of transaction log will rollback all the changes caused by all the active transactions till the LSN of beginning of oldest transaction(among the active transactions present in ATT), which is available from transaction log and opens the database for user access.

https://dba.stackexchange.com/questions/172003/very-slow-sql-server-database-recovery

 

RECOVERY vs NORECOVERY vs STANDBY

 

RECOVERY

RECOVERY is The default option. This indicates that the restore is complete and that once RESTORE is complete it is fully available. No further backups can be applied to the database as the db engine cannot guarantee consistency between what has changed in the fully recovered database and if the further backup files can be restored successfully.

use master

go

restore database adventureworks2014 with recovery

NORECOVERY

The database is in a state of RESTORING even after this RESTORE is complete as there are further RESTORE statements expected (eg t-log or differential). I’m just going to specify NORECOVERY here rather than restore from a full backup then a log backup as I do this in a further demo below.

use master

go

restore database adventureworks2014 with norecovery

 

STANDBY

Similar to NORECOVERY except that the database will accept read only connections. To do this any uncommitted transactions in the backup will be rolled back and stored in a transaction undo file (tuf.) Whilst users are running queries against the database no further restores can continue until all queries are complete (though this is not the case with log shipping.) When the next restore occurs, those uncommitted transactions in the tuf file will be rolled forward and the next log is restored.

use master

go

restore database adventureworks2014 with standby