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
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.
4)Tail-Log(Point-in-time).
5)Mirror.
6)Copy-only.
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.