It is time for T-SQL Tuesday #85 and this month the party is being hosted by Kenneth Fisher from SQL Studies.This month’s topic is Backup and Restore and I will be discussing the differences between database Restore and Recovery.
The restoration process of a SQL Server database passes through two phases: Restore and Recovery. The Restore phase performs a Data Copy of the database, while the Recovery process is comprised of performing both Redo and Undo actions to roll forward or roll back transactions from the transaction log.
Data Copy – The longest phase of database restoration is when the data files need to be recovered from the backups. SQL Server will recreate the data and log files and then rewrite the data from the full database backup. Data pages are retrieved from the backup in order and written to the data files. If you are also performing a restoration from a differential backup, SQL Server overwrites the extents restored from the full backup with the extents from the differential backup.
Redo – If the database is in either full or bulk-logged recovery model, committed transactions that are found in the transaction log will be rolled forward during the recovery phase up to the recovery point objective. If the database is in simple recovery model then committed transactions can only be recovered from either the full database backup or the differential backup.
Undo – When you are recovering your database to the chosen recovery point objective, which is normally the time of your database failure, the transaction log will likely include details of transactions that had not yet been committed. These uncommitted transactions will be rolled back by SQL Server during the undo phase.
In the example, the database performed a checkpoint at noon and a backup had been taken at that time. The restore process will capture all the transactions up until the point the database had been backed up. After the database has been restored, the recovery process will roll forward transactions 2 and 4 because they had been committed to the transaction log before the point of failure. Since transactions 3 and 5 did not commit before the time of system failure, the undo process will roll back the transactions to keep the data in a consistent state.
To see this in code, below is an example of restoring the JDmusic database from the music.bak file. A Full Backup, Differential Backup, and a Log Backup had previously been completed and backed up to the music.bak file. Within that file, each backup that was made will be assigned a file number in order of when they were added to the file. In this case the Full backup is file 1, the Differential backup is file 2, and the Log backup is file 3. When restoring the Full or Differential backup there is not much difference other than selecting the correct file number. Since neither of these backups are the final item we are restoring, we use the NoRecovery statement so that only the Data Copy phase of the restore is completed. When we get to the final log that we want to restore, we use the Recovery statement to tell SQL Server that the backup can now do the Redo and Undo phases of the restore process.
