Database backup in SQL SERVER - Technology with Vivek Johari

Introduction

This article explain the simple way to take the database backup in SQL SERVER which can be easily understandable by the database programmer having little knowledge of DBA. For taking the database backup, we need to follow the given step.Step 1:- Open the SQL Sever Management Studio. For this we have to follow the following path:-

START –> All Programs –> Microsoft SQL Server 2008R2 –> Click on SQL Sever Management Studio (SSMS) –> Open the SQL Server Management Studio using Login information (Username & password or Window authentication )

Please note:-

Above path can be changed depending on the Window version.

Step2:- Once SQL Sever Management Studio is opened, opened the Object Explorer. By default it will be already opened. If it is not opened, then follow the below steps to open it.

Click on the View(Top menu) –> Click on Object Explorer.

Step3:- Under the Databases node, select the database for backup and right click on it. Then choose Tasks and then Back Up.. as shown in the below figure. Here we choose the Company_DB as database.

Choose Database for backup

Step4:- On the click on Back Up… link, it will create a Back Up Database window as shown below:-

Back Up Database Window

On this window, we have the options to choose the backup type. There are 3 backup types in SQL Server.

1) Full Backup

2) Differential Back Up

3) Transaction Log

I will explain all 3 database types in another article. Full Backup type is the default Backup type and it is usually chosen for taking the database backup.

Also, if we want to choose another destination for storing the backup file, we can either remove the default backup destination first and then add the new destination for the database files or we can add the other destination file without removing the default destination by clicking on the Add button. On the click on Add button, it will open a new window from which we can choose our new destination. The backup file will have the extension of .bak. In this article, I will keep the default values.

If we click on the Options link from the left hand side menu, it will open the following screen.

Backup database Option Screen

In this screen, we have the option of appending the new backup with the existing backup sets or overwriting the existing backup set. There are many other options available like backup like verify backup when finished and Perform checksum before writing to the Media for checking the reliability of the database backup. Let keeps the default setting. I will explains the Checksum in detail in another article.

When we click on the OK button, it will made the database backup either on the default destination which is given below:-

C:Program FilesMicrosoft SQL Server MSSQL10_50.MSSQLSERVER MSSQL Backup

on the destination, which we have added by Clicking on the ADD button(see Back Up Database Window ).