How to: Restore a Differential Database Backup (Transact-SQL)
This topic explains how to restore a differential database backup.
To restore a differential database backup
Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the full database backup that comes before the differential database backup. For more information, see How to: Restore a Full Backup.
Execute the RESTORE DATABASE statement to restore the differential database backup, specifying:
The name of the database to which the differential database backup is applied.
The backup device where the differential database backup is restored from.
The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored. Otherwise, specify the RECOVERY clause.
With the full or bulk-logged recovery model, restoring a differential database backup restores the database to the point at which the differential database backup was completed. To recover to the point of failure, you must apply all transaction log backups created after the last differential database backup was created. For more information, see How to: Apply a Transaction Log Backup (Transact-SQL).
A. Restoring a database and differential database backup
This example restores a database and differential database backup of the MyAdvWorks database.
-- Assume the database is lost, and restore full database, -- specifying the original full database backup and NORECOVERY, -- which allows subsequent restore operations to proceed. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY GO -- Now restore the differential database backup, the second backup on -- the MyAdvWorks_1 backup device. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH FILE = 2, RECOVERY GO
B. Restoring a database, differential database, and transaction log backup
This example restores a database, differential database, and transaction log backup of the MyAdvWorks database.
-- Assume the database is lost at this point. Now restore the full -- database. Specify the original full database backup and NORECOVERY. -- NORECOVERY allows subsequent restore operations to proceed. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY GO -- Now restore the differential database backup, the second backup on -- the MyAdvWorks_1 backup device. RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH FILE = 2, NORECOVERY GO -- Now restore each transaction log backup created after -- the differential database backup. RESTORE LOG MyAdvWorks FROM MyAdvWorks_log1 WITH NORECOVERY GO RESTORE LOG MyAdvWorks FROM MyAdvWorks_log2 WITH RECOVERY GO
