How to restore a differential database backup (Transact-SQL)

How to restore a differential database backup (Transact-SQL)

To restore a differential database backup

1. Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the database backup preceding the differential database backup. For more information, see How to restore a database 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 will be applied.
     
     
   • The backup device where the differential database backup will be 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.

Examples

A. Restoring a database and differential database backup

This example restores a database and differential database backup of the MyNwind database.

-- Assume the database is lost at this point. Now restore the full 
-- database. Specify the original full backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY
GO
-- Now restore the differential database backup, the second backup on 
-- the MyNwind_1 backup device.
RESTORE DATABASE MyNwind
   FROM MyNwind_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 MyNwind database.

-- Assume the database is lost at this point. Now restore the full 
-- database. Specify the original full backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY
GO
-- Now restore the differential database backup, the second backup on 
-- the MyNwind_1 backup device.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH FILE = 2,
      NORECOVERY
GO
-- Now restore each transaction log backup created after
-- the differential database backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH RECOVERY
GO

See Also

Differential Database Backups

RESTORE

Reducing Recovery Times