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 the code to restore the JDmusic database from the music.bak file. Within that file, there has been a Full Backup, a Differential Backup, and a Log Backup. To restore a specific backup, specify the file number of where the backup is located within the backup file. (Full Backup is file 1 within the music.bak file.) Notice there is not a difference between restoring from a Full or Differential Backup other then needing to specify the correct file number of the backup that is being restored. For the first two sections of code the NORECOVERY statement is being used so the Recovery process is not started. The complete Restore process need to completely copy all the data to disk before the Recovery process begins. The final section of code that Restores the log will use the RECOVERY statement to begin the Recovery process. If multiple logs are being restored, only use the RECOVERY keyword with the last log that is being restored.