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

To restore a differential database backup

Important The system administrator restoring the differential database backup must be the only person currently using the database to be restored.

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