DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: Transactional Log Backups
Page 9 / 16
DIFFERENTIAL database backup will only record the data which has changes since the last successful full database backup. This backed up data is known as differential base. When compared to full database backups the differential database backup are quick to complete and will also be smaller in size as this has only the changes thereby reducing the work loss exposure. Differential database backups are very useful especially when subset of database is modified most often than the rest of the database.
Internally when you run a differential database backup it will backup all extents which has changes since the time last full backup was run. SQL Server stored data in 8KB page and an extent comprises of 8 of such 8 KB pages equaling to 64KB of data. Whenever any data changes within a 8KB Page a flag is turned on to let SQL Server know that if at all a differential backup is initiated then it should include the data from this extent within the backup. Once a FULL backup is completed the flags are turned off. Therefore, every time you take a differential backup it will backup all the extends which has changed after the last successful FULL backup.
When your database is in SIMPLE recovery model and when you plan to restore a database then you first need to restore the latest FULL backup with NORECOVERY, followed by restoring the latest DIFFERENTIAL backup with RECOVERY. When a database is in SIMPLE recovery model you cannot achieve POINT IN TIME recovery of the database.
When your database is in FULL or BULK-LOGGED recovery model you can use DIFFERENTIAL backups along with TRANSACTONAL LOG backup as every differential backup will have extends which have changed there by eliminating the number of transactional logs that needs to be restored to recover the database. To restore a database then you first need to restore the latest FULL backup with NORECOVERY, followed by restoring the latest DIFFERENTIAL backup with NORECOVERY, followed by restoring all the transactional log backup files which were created since the last differential backup; thereby reducing the time to recover the database.
Best Practices for Scheduling Differential Database Backups in SQL Server
For very large databases, a full database backup will always require significant amount of disk space and time to complete the backup. Hence, you can save time and disk space by having a full backup to start at the beginning of a week followed by a series of differential database backups at regular intervals throughout the week.
Note: Each successive differential database backup will be larger than its predecessor, hence it will require more backup time, disk space, and restore time. Therefore, Microsoft recommend that you take a new FULL database backup at an appropriate intervals to provide a new differential base to reduce the database recovery time and to reduce the data loss.
Recommendations when you use differential database backups:
- After a successful full database backup, schedule differential database backups periodically. For example, you might take a differential database backup every two/four hours or, for highly active systems, or even more frequently.
- Take full database backups at intervals that makes sure that your differential backups do not become too large. For example, you might back up the full database one time a week every Sunday followed by differential database backups every two/four hours depending upon how active is your system.
Create a DIFFFERENTIAL Database Backups Using TSQL commands
TO DISK = 'C:\DBBackups\MyTechMantra.DIF'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25
/* Verify the Database Backup File Once the Backup has completed successfully */
FROM DISK = 'C:\DBBackups\MyTechMantra.DIF'
Create a DIFFFERENTIAL Database Backup Using SQL Server Management Studio
- Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree
- Expand Databases node and then right click the database which you want to take a differential backup
- Select Tasks, and then click Back Up… option as shown to open up Back Up Database dialog box
- Select Backup Type as Differential and Select Destination as Disk
- Click on Add button to add a backup file and specify the backup file name and click OK to save the changes
- Finally to take Differential database backup click OK
Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.
- TRANSACTIONAL LOG Backups in SQL Server Step by Step Tutorial with Examples
- COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
- Mirrored Backup in SQL Server Step by Step Tutorial with Examples
- FILE Backup in SQL Server Step by Step Tutorial with Examples
- FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: