Types of database backup in SQL Server 2008

There are three types of database backup in SQL Server. These are following.

• Full Database Backup
• Differential Backup
• Transaction Log Backup

Full Database Backup: - As the name clearly indicates that we are talking about full database backup.

The backup file will include data file (.mdf) and the log file (.ldf). When we restore the full backup of database, it creates data file (.mdf) and log file (.ldf).

Where we need full backup: - If database size is small and lesser no of transactions are happening on the database, In that case we take full database backup.

Let’s take an example of taking full database backup and restore it.

I am going to take Full database backup of “ETL” database, this will bring up a new screen as shown in the below image , we will follow below mentioned steps to take the full backup and can correlate each step with above mentioned image.

Right click on “ETL” databaseàTasksàclick on Backup menu,

In the below image, there is a combo box that is containing the different types of backups available in SQL Server, we will select full option as we are going to take full database backup.

Now, we will click on “Remove” button as shown in the above image that will remove the existing backup path (indicated by arrow). Post removal of backup path, we need to specify the backup path where we want to take the database backup. For that reason, we will click on “Add” button that will bring up a new screen as shown in the below image.

We will click on the button (encircled with blue color in the above image), that will bring up another new screen as shown in the below image.

We will specify the location where we want to take the database backup, in my case location is “E:\database backup\” and will provide the name of the database backup(in my case backup file name is “fullbackup.bak”).

Then click on “OK” then further “OK” then further “OK” , I have already explained how to take backup of a database in my earlier post, hence not explaining in detail. So in that way we will take the full database backup. Now we will restore this database backup. As I have already shown how to restore a database in my earlier post, you can refer my previous post to restore the full backup.

In the below image, we can see that I have specified the same path(encircled with blue color) where we took full backup of the database, I have given the name to this database is “FullRestore”. One the database is restored, we will check the files those has been created after restoring the database.

I will run below system stored procedure to check the files those were restored after database restoration.

Sp_helpfile

Differential Backup: - In differential backup, SQL Server only backup those extents (8 contiguous data pages) which have been modified after the last full database backup was taken. Differential backup is quite smaller than a full backup as it only backup those extents which have been modified after the last full backup.

Where we need Differential Backup: - Differential backups are taken in those scenarios where transaction frequency is quite high.

Example: - If I have taken Full backupà differential Backup 1à Differential Backup2à Differential Backup3, now I want to create the latest database with all of the transactions. I will just restore the full backup + differential backup3 (As it contains all the changes those have been done after the last full backup was taken).

Transaction log Backup:- In transaction log backup, SQL Server backup all the changes those have been made after the full or differential backup was taken. Once we take the transaction log backup, it freed up the space in the transaction log and that space can be reused. If we do not take transaction log backup, transaction log will keep up growing.

Where we need Transaction log Backup:- Transaction log backup is taken in those scenarios where transaction frequency is extreme(thousands of transactions per day) and the database size is very big.

Example:- If we have huge database, then we cannot go for full database backup each time, in that scenario, we can have transaction log backup on regular interval of time. If I have full backupàdifferential backupàtransaction log backup, we can create latest database with all the transactions by restoring backup in the below sequence

Full backup àdifferential backupàtransaction log backup=> Latest database

In my next article we will take differential backup and transaction log and restore it by taking an example. Till then take care and have a good day.