Database Backup in SQL Server 2005 : Part II

In the earlier post of this series, we discuss how to create a Full database backup using SQL Server Management Studio. In this part, we will discuss about differential database & transaction-log backup. We will continue our demonstration with “SKG_TEST” database. So let’s get started with differential database backup.

Creating Differential Database Backup using SSMS

In the previous post, we have discussed that the differential data backup helps us to minimize the number of transaction log backup required to restore a database in case of a disaster. In this demo, we will create a differential backup of “SKG_TEST” database.

  • Open SSMS and connect to the default instance of the SQL Server and expand the Database folder. Right click on the database “SKG_TEST”, select Task –> Backup, as mentioned in the picture below.


  • On the Database Backup page, select the backup type as Differential and click on the Add button to add the new differential data backup file.


  • Select the differential backup file and click on OK to continue. Verify the differential backup file path from the screen mentioned below.


  • Click on Ok to start the differential backup. Once the backup process is complete, you will get the following screen.



Creating Transaction Log Backup using SSMS

As discussed earlier, transaction log backup helps us to restore the database till the time of failure. If the database is in Full / Bulk-logged recovery model, transaction log backup of the database is possible. However, Simple recovery model does not support transaction log backup. For this demo, we will use “SKG_TEST” database which is using Full Recovery model.

  • Connect to the Local SQL instance using SSMS and browse to the Database folder in SSMS. Right click the “SKG_TEST” database and go to Task –> Backup.
  • In the Back up Database Page, select Backup Type as Transaction Log and select the location to store the transaction log backup file. Normally, transaction log backup files have an extension .trn however this is not mandatory.


  • Once you choose the transaction log backup file location, specify the backup file name and click on OK. Verify the transaction log file location from the screen mentioned below.


  • Click on OK to start the transaction log backup process. Once the transaction log backup is completed, you will get the following screen.


Thus, transaction-log backup of “SKG_TEST” database is completed successfully.

What if the database is using Simple Recovery Model

To complete this demo, we are using “SKG_TEST3” database which is using Simple recovery model. In this demo, we will not create a database backup, however, we will verify the backup option available to us.


From the above screen-shot, we find that the “SKG_TEST3” database is using Simple Recovery model. Let’s create a backup of the database. To create a backup, we will follow the instructions mentioned above (Right click on the database, Tasks –> Backup).


From the above screen-shot, it is clear that for “SKG_TEST3” database, we can have only two types of backup. They are:

i. Full Backup    &      ii. Differential Backup

Thus, transaction log backup is not possible, when a database is using Simple Recovery model.

In the next article, we will discuss about backup of databases with multiple file groups.


About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s