SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

What is a Differential Backup SQL Server?

Differential Backup SQL Server will only record the data which has changes since the last successful full database backup.

Differential Backup SQL Server | Differential Database Backup in SQL Server

Differential backup (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 Backup in SQL Server or (Differential Database Backup in SQL Server) are very useful especially when subset of database is modified most often than the rest of the database.

This is Part 9 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

Internally when you run a differential backup in SQL Server 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.

In FULL or BULK-LOGGED recovery model you can use DIFFERENTIAL backups along with TRANSACTION LOG backup

When your database is in FULL or BULK-LOGGED recovery model you can use DIFFERENTIAL backups along with TRANSACTON LOG backup as every differential backup will have extends which have changed there by eliminating the number of transaction 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 transaction 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.

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

BACKUP DATABASE MyTechMantra
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 */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.DIF'
GO

Create a Differential 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.

Previous Page.. Begin Tutorial.. Next Page..

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement