Complete Database Restores (Full Recovery Model)
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
In a complete database restore, the goal is to restore the whole database. The whole database is offline for the duration of the restore. Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.
Under the full recovery model, after you restore your data backup or backups, you must restore all subsequent transaction log backups and then recover the database. You can restore a database to a specific recovery point within one of these log backups. The recovery point can be a specific date and time, a marked transaction, or a log sequence number (LSN).
When restoring a database, particularly under the full recovery model or bulk-logged recovery model, you should use a single restore sequence. A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore.
In this Topic:
Typically, recovering a database to the point of failure involves the following basic steps:
Back up the active transaction log (known as the tail of the log). This creates a tail-log backup. If the active transaction log is unavailable, all transactions in that part of the log are lost.
Under the bulk-logged recovery model, backing up any log that contains bulk-logged operations requires access to all data files in the database. If the data files cannot be accessed, the transaction log cannot be backed up. In that case, you have to manually redo all changes that were made since the most recent log backup.
For more information, see Tail-Log Backups (SQL Server).
Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).
Restoring the most recent differential backup reduces the number of log backups that must be restored.
Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.
Recover the database (RESTORE DATABASE database_name WITH RECOVERY). Alternatively, this step can be combined with restoring the last log backup.
The following illustration shows this restore sequence. After a failure occurs (1), a tail-log backup is created (2). Next, the database is restored to the point of the failure. This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.
The basic RESTORETransact-SQL syntax for the restore sequence in the preceding illustration is as follows:
RESTORE DATABASE database FROM full database backup WITH NORECOVERY;
RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;
RESTORE LOG database FROM log_backup WITH NORECOVERY;
Repeat this restore-log step for each additional log backup.
RESTORE DATABASE database WITH RECOVERY;
The following Transact-SQL example shows the essential options in a restore sequence that restores the database to the point of failure. The example creates a tail-log backup of the database. Next, the example restores a full database backup and log backup and then restores the tail-log backup. The example recovers the database in a separate, final step.
USE master; --Create tail-log backup. BACKUP LOG AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' WITH NORECOVERY; GO --Restore the full database backup (from backup set 1). RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' WITH FILE=1, NORECOVERY; --Restore the regular log backup (from backup set 2). RESTORE LOG AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' WITH FILE=2, NORECOVERY; --Restore the tail-log backup (from backup set 3). RESTORE LOG AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' WITH FILE=3, NORECOVERY; GO --recover the database: RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; GO
Under the full recovery model, a complete database restore can usually be recovered to a point of time, a marked transaction, or an LSN within a log backup. However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible.
The following example assumes a mission-critical database system for which a full database backup is created daily at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. To restore the database to the state is was in at 5:19 A.M. Wednesday, do the following:
Restore the full database backup that was created Tuesday at midnight.
Restore the differential database backup that was created at 5:00 A.M. on Wednesday.
Apply the transaction log backup that was created at 5:10 A.M. on Wednesday.
Apply the transaction log backup that was created 5:20 A.M. on Wednesday, specifying that the recovery process applies only to transactions that occurred before 5:19 A.M.
Alternatively, if the database needs to be restored to its state at 3:04 A.M. Thursday, but the differential database backup that was created at 3:00 A.M. Thursday is unavailable, do the following:
Restore the database backup that was created Wednesday at midnight.
Restore the differential database backup that was created at 2:00 A.M. on Thursday.
Apply all the transaction log backups created from 2:10 A.M. to 3:00 A.M. on Thursday.
Apply the transaction log backup that was created at 3:10 A.M. on Thursday, stopping the recovery process at 3:04 A.M.
To restore a full database backup
To restore a differential database backup
To restore a transaction log backup
To restore a backup by using SQL Server Management Objects (SMO)
To restore a database to a point within a log backup
Apply Transaction Log Backups (SQL Server)
Full Database Backups (SQL Server)
Differential Backups (SQL Server)
Backup Overview (SQL Server)
Restore and Recovery Overview (SQL Server)