Wednesday, August 3, 2011

SQL Server interview questions: - How to back-up SQL Server Database and Restore Database?

There are basically two ways (methods) in order to back-up SQL Server Database
and Restore database.

The following are the two methods to do the same.

1. By simple Detach and Attach Methods.

2. By creating a .bak file of the Database.

Let’s see a simple example on both the above methods for creating back-up and
restoring database.

Method1: -By simple Detach and Attach.

Let us assume that we have a Customer Database on which we have to perform the
Back-up and Restore action.

In order to perform simple Detach and Attach method just right click on the
database name > go to Tasks > Select Detach. A new window will pop-up from the
check box of drop box just select the drop box and clicks ok.





As soon as you click on ok you find that now the Customer database is detach
from the Databases.



Now, just browse to the location of “Customer.mdf” database and copy the both
“Customer.mdf” and “Customer_log.ldf” and save both the file to your specified
location.



Now, let’s see how we can restore the database back to the Databases.

In order to restore “Customer” database right click on Databases > Select Attach
a new will pop-up like below diagram from that click on Add then again a new
window will open of locate database file from that select “Customer.mdf” and
click ok again you will be revert back to the Attach database window from that
click ok.









Now, you will see that “Customer” database is again attached to the Databases.



Method2: -By creating a .bak file of the Database.

In order to create a .bak file right click on “Customer” database > go to Tasks
> Select back up.


As soon as you select back up a new window like below diagram.


Now click on ok, a message will be displayed like below diagram.



Now, just browse to the destination, you will see a backup file (.bakformat) file is created.





Now, let’s see how to restore back the database.


As soon as you click on Restore Database a new window will appear like below
diagram from that select from device and click [----] then again a new window
will appear of specify back-up from that click on add.





As soon as you click on add then again a new window will appear like below
diagram of Locate back-up file from that select the Database on which you have
created the back-up and then click ok.



Now just click on ok.



Similarly, when you click on ok you will revert back to the restore database
window from that select the database name and check the restore box as marked
and later click on ok.



If everything goes right you will see the message as completed like below
diagram.


See the following SQL Server interview questions video on difference between unique key and primary key



By a click you can avail more tutorial on SQL Server interview questions

Regards,

Visit for more author’s blog on SQL Server interview questions



No comments: