SQL Server Database Differential backup

Differential backup (differential backup) definition

A data backup that is based on the most recent full backup of a full database or part of a database or set of data files or filegroups (differential baselines), and contains only data that has changed since the differential base was determined.

Database differential backup using the SSMS database management tool

1, select Database-"Right click-" Select Task-"Select Backup.

2, in the backup Database pop-up box-"Select the backup type of diff-" and then delete the system-generated target file-"and then click Add.

3. In the Select backup target pop-up box-click Select Backup Path.

4, in the location database file pop-up box, first select the file path that needs to be backed up, then the data backup file name, click OK.

5. In the Backup Database popup box, click OK.

6. See if the backup was successful.

Database differential restore using the SSMS database management tool

Differential backup and restore NOTE: A differential backup is a copy of a change that occurred after the last full backup. So the restore is required for the last full backup and the most recent differential backup. A differential backup is a two-step operation, the first step is to restore the database, and the second step restores the difference section.

To restore a database step

1, select the database-"Right click-" Select Task-"Select Restore-" Select the database.

2, in the Restore Database pop-up box-"First click Device-" and then click the Restore file path.

3. In the Select Backup Device pop-up box-click on Add File.

4, in the location backup file pop-up box-"Select Folder-" and then select the file to restore-"click OK."

5, enter the Restore mode.

6, overwrite the existing database does not tick-"Select the database recovery mode is set to Norecorvery."

7. View the status of the database.

To restore a diff file step

1, select the database to restore-"Right click-" Select Task-"Select Restore-" Select the file and filegroup.

2, in the Restore files and filegroups popup-"Select Device-" Select the file restore path.

3, select the backup Device pop-up box-"select Add."

4. In the Locate Backup file popup box-"Select the diff file to restore-" click OK.

5. In the Restore files and Filegroups pop-up box-"Select File-" click Options.

6. Select Recovery Status-click OK.

7, restore the results.

Differential backup using t-SQL scripts

Syntax: Backup database name to disk= file path with differential;

Example: Backup database testss to disk= ' D:\SqlTest\backup\t3.bak ' with differential;

Differential restore using a T-SQL script

Syntax: The first step is to specify the database recovery model: Restore database name from disk= database file with NORECOVERY;

Second step restore differential backup: Restore Database name from disk= diff file with recovery;
Example: First step to restore the BACKUP database: Restore DB testss from disk= ' D:\SqlTest\backup\testss.bak ' with NORECOVERY;

Second step restore differential backup: Restore Database testss from disk= ' D:\SqlTest\backup\t3.bak ' with recovery;

(Note that if the database exists, it can be deleted or overwritten with the first database)

Summarize

Pros: 1 . Creating a differential backup can be very fast compared to creating a full backup. A differential backup records only the data that has changed since the full backup on which the differential backup is based. this helps with frequent data backups and reduces the risk of data loss. However, before you can restore a differential backup, you must first restore its baseline. therefore, restoring from a differential backup will necessarily require more steps and time than restoring from a full backup because it requires two backup files.

2. A differential database backup is especially useful if a subset of the database is modified more frequently than the rest of the database. in these cases, using a differential database backup, you can perform frequent backups and do not incur the overhead of a full database backup.

3. under the full recovery model, use differential backups to reduce the number of log backups that must be restored.

Cons: Differential backups are based on the difference from the last full backup, so for a busy database, a few differential backups have been made over a period of time, no matter how many times Checkpoint,sql server still puts these differential pages based on the full backup into the differential backup file, which, by reason, A page after a checkpoint, if no further changes occur, it is not necessary to put inside the differential backup file (if the difference is based on the last difference), because the difference bitmap can not be based on differences, this will cause, sometimes the differential backup file is larger than the full backup file.

SQL Server Database Differential backup