Back Up and Restore a database using sql server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 5965 red flag
Rating: 4 out of 5  
 1 vote(s)

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.


It 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.
7)File&File Group.

Full Backup:

It is used to take an entire database and extension is .bak

Differential Backup:

It is used to take the backup of the modify data or changed data after taking the full backup.
Extension .bak

Transaction Log Backup:

It is used to take the Current transaction backup 
Extension .trn

Tail-Log Backup:

 It is used to take the backup of after crashing the database.
Extension .trn

Mirror Backup:

It is used to take the backup more than one disk.
Extension .bak

Copy-Only Backup:

It is used to take the backup with out disturbing the Mdf & ndf files.
Extension .bak

File &File Group Backup:

It 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 o

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

I hope this step by step tutorial will help you for restore the database.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Aswini Aluri
Member Level: Starter
Member Status: Member
Member Since: 11/21/2012 12:44:34 PM
Country: India
Mark as Answer if it helps you Thanks&Regards Aswini Aluri
Aswini Aluri. Software developer

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)