How to restore a differential database backup (Transact-SQL)
To restore a differential database backup
- 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.
- 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.
- The name of the database to which the differential database backup will be applied.
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