Differential Database Backups in SQL Server

In this fast moving world, data is the heart & soul of any enterprise. As the business is expanding many organizations around the world have adopted a 24x7 business model to support and run their businesses. In a 24x7 business model data changes happen around the clock, thereby increasing the demand for database backups. Taking full database backup is a good practice however this consumes lot of system resources thereby slowing responses to front end applications which are using the databases. Moreover a full database backup takes a lot of time to complete especially for very large databases. In order to reduce the database backup duration and also to safeguard the data, Database Administrators can take Differential Backups of their databases.

Different types of SQL Server Database Backups
There are different types of SQL Server Database Backups available depending upon the Recovery Model of the Database chosen by DBA. To know more about recovery models in SQL Server you can refer to my previous article titled Database Recovery Models in SQL Server. The different types of database backups in SQL Server are Full, Differential, Transactional Log, Full File Backup etc. The method which the Database Administrator should choose to backup databases depends on Recovery Model and how fast the database backup needs to be completed.

What Does Differential Database Backups Do?
Differential database backups basically contain changes which were made to the database since the last full backup. A differential backup usually takes lesser time to complete as it contains only the changes (delta) which has happened to the database since the very last full backup. It is much faster when compared to full database backups. When you have very large databases then you should be using this backup strategy to reduce the work loss and it should be scheduled at lesser time intervals when compared to full backups. In the event of a disaster, a differential database backup alone will not help you to recover the database. You need to have transactional database backups available to achieve the point in time recovery. In this article we will see how to take differential database backups using SQL Server Management Studio and how to take differential backups using TSQL code.

How to take Differential Database Backups using SQL Server Management Studio
Let us see the steps which you need to perform in order to take the differential backup of AdventureWorks database. However the initial backup of the database should always be a Full Backup. In the Back up Database Dialog box you have the option to choose Full, Differential, Transaction log or File and File Group backups. 

1. Once you are connected to the instance of SQL Server Database Engine, in the object explorer click on the server name node and expand it.

2. Expand the Databases Node.

3. In this article we will be see the steps which you need to follow to take the differential backup of AdventureWorks database. Using Differential Backup strategy you can even take the database backup of system databases expect for TempDB and Resource databases.

4. To take differential backup of AdventureWorks database you need to right click the AdventureWorks database and point to Tasks and then click on Back Up…. as shown in the below snippet. 

 

5. Once you click on Back up… option in the popup window it will open up Back Up Database dialog box.

6. In the database drop down list, verify that database name selected is AdventureWorks. Here you can optionally change the name to a different database if you would like to perform the differential backup of another database. Using differential backup strategy you can perform differential backup for databases in any of the Recovery Models (Full, Bulk-Logged or Simple). To know more about Recovery Models in SQL Server you can refer to my previous article titled Database Recovery Models in SQL Server.

7. Next step will be to select Differential as the value for Backup type in the drop down list. Make sure that “Copy Only Backup” option is not checked. 

8. By default, Backup component Database option will be selected for AdventureWorks database.

9. Next you can provide values for Name & Description fields as appropriate.


 
10. As you could see in the above snippet that there is an option to provide the Backup set expire days. You could see that the value provided for Backup set will expire is 0. This is because we never want the backups to expire. However, you can provide any value between 0 and 99999 days for backup set expire.

11. Next step will be to choose differential database backup destination by selecting either of the two options Disk or Tape. Click on Add… button to open up Select Backup Destination dialog box and specify the location where you want the differential backups of AdventureWorks to be saved. Click OK to save the changes as shown in the snippet.



 

12. In order to view or select the advanced options, click Options in the select a page pane.