Export (0) Print
Expand All

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

SQL Server 2000

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

Show:
© 2014 Microsoft