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.
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 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 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.
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 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.
- SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
- FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
- STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
- COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
- ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
- ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
- ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
- DROP IF EXISTS T-SQL Enhancement in SQL Server 2016
- TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
- T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
- TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
- PARTIAL Backup in SQL Server Step by Step Tutorial with Examples
- FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
- FILE Backup in SQL Server Step by Step Tutorial with Examples
- MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
- COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples