Database Backup in SQL Server 2005 : Part I

Database backup in SQL Server 2005 depends on the recovery model of the database. Based on this, three types of backup is possible for a database. They are:

  • Full Database Backup
  • Differential Database Backup
  • Transaction Log Backup

Let’s look into each of these along with some examples. We will start with the Full data backup.

Full Database Backup

As I mentioned earlier, the database backup depends on the recovery model of the database. However, Full data backup is supported by all recovery models (Simple, Full and Bulk-Logged). When we create a full backup of a database, all the data stored in the database along with log is captured. Also we should understand that to initiate a differential backup or a transaction log backup, first, we need to take a full backup of the database.

Differential Database Backup

Differential database backup can be initiated on a database irrespective of its recovery models. When we create a differential backup, all the data which got changed from the last full backup is captured within it. The main purpose of using a differential backup is to reduce the number of transaction log backup required to restore a database in case of a disaster.

Transaction Log Backup

Transaction Log backup can be taken when a database is using Full or Bulk-logged recovery model. Also, the t-log backup can be initiated after the full backup of the database. The log backup is starts with the LSN (Log Sequence Number) at which the previous log backup is completed. Using the transaction log backup, we can recover the data till the time of failure.

In the next section, we will see how to create these types of backup.

Creating a Full Database Backup using SSMS

  • Connect to the SQL Server 2005 instance using SSMS and expands the Databases folder.


  • For this demo, we are going to create a Full backup of the database “SKG_TEST” using SQL Server Management Studio. Later, I will discuss how to create a full backup of the database using T-SQL.
  • Right-click on the database “SKG_TEST”, go to Tasks and Select Backup.


  • Once we click on the Backup option, we will get the following Back up Database screen. Have a look on the Recovery model, Backup Type, and Backup component of the database in the screen-shot.


  • We are going to create a full backup on the disk. Select Disk in ‘Back up to’ option and click on Remove, to remove the existing backup file. Click on Add, and browse to the folder where you need to store your new full database backup file. In the below screen-shot, the Browse button is shown with a red line below it.


  • Select the Backup folder and enter a backup file name for the same. For this demo, we choose the backup location as C:\Backup\Test. Click on OK to continue.


  • Click on OK button to get the following screen. On the Back Up Database screen, click on OK to start the full backup of the database “SKG_TEST”.


  • Once the backup is completed, we get the following screen. Click on the OK button to complete the Full Backup Process.



In our next article, we will create a differential backup and a Transaction-Log backup.


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