• Article

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

  1. 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.

  2. 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.

  3. 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).

Example

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