Share via

  • Article

How to: Create a Differential Database Backup (Transact-SQL)

This topic explains how to create a differential database backup.

Important

You cannot create a differential database backup unless the database has been backed up first.

To create a differential database backup

  1. Execute the BACKUP DATABASE statement to create the differential database backup, specifying:

    • The name of the database to back up.

    • The backup device where the full database backup is written.

    • The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last full database backup was created are backed up.

    The required syntax is:

    BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL

  2. Optionally, specify:

    • The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

    • The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.

    • The FORMAT clause when you are using media for the first time to initialize the backup media and rewrite any existing media header.

      The INIT clause is not required if the FORMAT clause is specified.

      Important

      Use extreme caution when you are using the FORMAT or INIT clauses of the BACKUP statement as this destroys any backups previously stored on the backup media.

Example

This example creates a full and a differential database backup for the MyAdvWorks database.

-- Create a full database backup first.
BACKUP DATABASE MyAdvWorks 
   TO MyAdvWorks_1 
   WITH INIT
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
   TO MyAdvWorks_1
   WITH DIFFERENTIAL
GO