Create a Differential Database Backup (SQL Server)

Applies to: SQL Server

Create a differential database backup in SQL Server using SQL Server Management Studio or Transact-SQL.

Sections in this topic

• Before you begin:

  Limitations and Restrictions

  Prerequisites

  Recommendations

  Security

• To create a differential database backup, using:

  SQL Server Management Studio

  Transact-SQL

Before you begin

Limitations and restrictions

• The BACKUP statement is not allowed in an explicit or implicit transaction.

Prerequisites

• Creating a differential database backup requires a previous full database backup. If your database has never been backed up, run a full database backup before creating any differential backups. For more information, see Create a Full Database Backup (SQL Server).

Recommendations

• As the differential backups increase in size, restoring a differential backup will significantly increase the time required to restore a database. We recommend that you take a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.

Security

Check your permissions first!

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role, the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file will interfere with a backup operation. SQL Server needs to be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Permissions problems on the backup device's physical file will not be obvious until the physical resource is accessed when you attempt the backup or restore.

SQL Server Management Studio

Create a differential database backup

1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.

3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4. In the Database list box, verify the database name. You can optionally select a different database from the list.

   You can perform a differential backup for any recovery model (full, bulk-logged, or simple).

5. In the Backup type list box, select Differential.

   Important

   When you select Differential , verify that the Copy Only Backup check box is cleared.

6. For Backup component, click Database.

7. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

8. Optionally, in the Description text box, enter a description of the backup set.

9. Specify when the backup set will expire:

   • To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; 0 days means the backup set will never expire.

     The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

   • To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

10. Choose the type of backup destination by clicking Disk or Tape. To select the path of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

    To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

11. To view or select the advanced options, click Options in the Select a page pane.

12. Select an Overwrite Media option, by clicking one of the following:

    • Back up to the existing media set

      For this option, click either Append to the existing backup set or Overwrite all existing backup sets. Optionally, check the Check media set name and backup set expiration check box and, optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see if the actual name matches the name you enter here.

      If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

    • Back up to a new media set, and erase all existing backup sets

      For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

13. In the Reliability section, optionally, check:

    • Verify backup when finished.

    • Perform checksum before writing to media, and, optionally, Continue on checksum error. For information about checksums, see Possible Media Errors During Backup and Restore (SQL Server).

14. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

    Note

    The options in the Transaction log section are inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).

15. SQL Server 2008 (10.0.x) Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    To view the current backup compression default

    • View or Configure the backup compression default Server Configuration Option

    Note

    Alternatively, you can use the Maintenance Plan Wizard to create differential database backups.

Transact-SQL

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

Example (Transact-SQL)

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  

See Also

Differential Backups (SQL Server)
Create a Full Database Backup (SQL Server)
Back Up Files and Filegroups (SQL Server)
Restore a Differential Database Backup (SQL Server)
Restore a Transaction Log Backup (SQL Server)
Maintenance Plans
Full File Backups (SQL Server)