Backup and Restore using sql server?

This article will explain you how to restore the database and how to take an backup of our database file , different types of backup and how it will be used.

Introduction:

Backup is nothing but copy of original data.It is used when the your database is crashed by some problem the backup is used at the time.

They are seven types of Back Up's are there:

1)Full Backup

2)Differential Backup.

3)Transaction Log Backup.

4)Tail-Log(Point-in-time).

5)Mirror.

6)Copy-only.

7)File&File Group.

Full Backup:

Full Backup is used to take an entire database and extension is .bak 

Differential Backup:

Differential Backup is used to take the backup of the modify data or changed data after taking the full backup.

Extension .bak

Transaction Log Backup:

Transaction Log Backup is used to take the Current transaction backup 

Extension .trn

Tail-Log Backup:

Trail-Log Backup is used to take the backup of after crashing the database.

Extension .trn

Mirror Backup:

Mirror Backup is used to take the backup more than one disk.

Extension .bak

Copy-Only Backup:

Copy-only backup is used to take the backup with out disturbing the Mdf & ndf files.

Extension .bak

File &File Group Backup:

File&File Group Backup is used to take the individual Backup or group of files then we can use file or file group.

Extension .bak

Syntax for Full BackUp:

backup database<database name>to disk='Location\Full.bak' with stats=5

If it is in Differential log Backup then we indicate with differential stats

Syntax for Transaction log Backup

backup log database name from disk='location\tlog.trn with stats 2

Syntax for Tail-log Backup

same as transaction log slite change in point-in-time file name.

Syntax for Mirror Backup

backup database<database name> to disk='Location\Full.bak' mirror to disk='location\full.bak'.

Syntax for Copy-Only Backup:

backup database to disk='location with copy-only'.

File &File group:

File backup is nothing but take the backup of mdf files&ndf files.

To take the backup for individual files and file groups.

GUI Based Full Backup:

Open object Explorer in the toolbar,Now we can got to database folder and right click on tasks , Now select the backup 

Now click on backup and select backup type is Full change the location and click on Add button(change the previous location)

 Backup destination window should appear, select the location using browser button 

Now select the location and file name must be with the extension of .bak file which as shown below and click ok button.

Now select the options page select Overwrite all existing backup sets radio button and chech the checkbox verify 

backup when finished then click on ok.

After we click ok me get a message box then again click on ok

I hope this step by step process will help you to backup the database.

How to Restore the Database?

Syntax of Restore db:

Restore Database<database name>from Disk='Location\full.bak with No Recovery stats=10

GUI based:

 Open object explorer in the tool bar,Now we can go to database and right click on database and select restore database

Restore Window database should appear and type the database name on 'to database' and select device radio button and click browse button.

Select Backup device click add button with the extension of .bak

Select the checkbox 

Go to options page check the overwrite the existing database on restore option in Recovery state select Restore with Recovery and click on ok.

After we click ok we get message box which in our case says "Database database name restored successfully

This step by step tutorial will help you for restore the database.

By using this article we can easily restore and backup the database.

i hope you understand.